2.14. DUAL

Literal expressions can be used within the select clause of a select statement. For example, if we have a table, PROFESSORS, with a column called SALARY, we can select all salaries plus what those salaries would be with a 10% increase.

SELECT SALARY, SALARY*1.1 FROM professors;

We can use SQL and built-in functions to do some math. To get the square root of 2, we can execute the following select statement.

SELECT SQRT(2) FROM professors;

This select will return the square root of 2 for every row in the table. If the table has just one row, then we will get the square root of 2 once. Why not create a dummy table to use with these expressions? This already exists. It is called DUAL. It is not declared in your schema, but it is in the database and every account has access to use it through a grant and a synonym. A description of the table is the following.

					SQL> desc dual
 Name                          Null?    Type
					------------------------------ -------- -----------
					DUMMY                                   VARCHAR2(1)
				

The DUAL table is used mostly for ad hoc queries. The DUAL table with SQL*Plus provides a mechanism to experiment with built-in functions. We want to investigate the Oracle built-in function, INSTR, to see if we can pull the last forward slash from a pathname. For this exercise, hard code a test pathname. The SQL*Plus session uses “select from dual,” which returns “8.” This is the position of the last forward slash in the hard coded pathname.

					SQL>
					SQL> SELECT instr('aaa/bbb/ccc','/', -1,1) FROM dual;

INSTR('AAA/BBB/CCC','/',-1,1)
					-----------------------------
					8
				

Next, use this function with a SUBSTR function to extract just the file name.

					SQL> SELECT
  2  substr('aaa/bbb/ccc',instr('aaa/bbb/ccc','/',-1,1)+1)
  3  FROM dual;

SUB
					---
					ccc
				

Now we can write our application code. Assume we have a PL/SQL variable with the name FULL_PATHNAME. We can assign just the filename portion to a variable with the following:

variable := substr(full_pathname,
                instr(full_pathname, '/', -1, 1) + 1);