Oracle Magazine - July/August 2012

one column—called the DUMMY column— that contains the value X.) You can use DUAL to try out functions that work on string literals and, as you’ll see in subsequent articles in this series, on number literals and even on today’s date.

The following demonstrates the single-row, single-column output of a SELEC T statement executed against the DUAL table:

SQL> select

2 from dual;

D - X

1 row selected.

To display the current date, you can query the DUAL table as follows:

SQL> select sysdate 2 from dual;

SYSDATE —————————— 18-APR- 12

1 row selected.

And finally, the following example shows how you can practice any function in the SELECT clause of a SQL statement, using the DUAL table:

SQL> select rpad('Melanie', 10, '*') Melanie, lpad('Caffrey', 10, '.') Caffrey

2 from dual;

MELANIE CAFFREY ——————————— ——————————— Melanie*** ...Caffrey

1 row selected.

Note that functions work even though there is no usable data in DUAL. In the preceding examples, the SYSDATE function displays the current date and time of the operating system hosting the database, and the RPAD and LPAD functions add padding to my name.

Code Listing 7: Query that demonstrates the concatenation operator, ||

SQL> select first_name||' '||last_name employee_name 2 from employee

3 order by employee_name;

EMPLOYEE_NAME —————————————————————————————————————————————————————————————————————————————— Betsy James Donald Newton Emily Eckhardt Frances Newton Matthew Michaels Roger Friedli mark leblanc michael peterson

8 rows selected.

Code Listing 8: Query that demonstrates nested CONCAT calls

SQL> select CONCAT(first_name, CONCAT(' ', last_name)) employee_name 2 from employee

3 order by employee_name;

EMPLOYEE_NAME —————————————————————————————————————————————————————————————————————————————— Betsy James Donald Newton Emily Eckhardt Frances Newton Matthew Michaels Roger Friedli mark leblanc michael peterson