Generate DML/DDL/QUERY from SQL:

Lets consider an example to generate an SQL from select query to online datafile of a database.

The actual query is

select name from v$datafile;

From the above one we need to get the datafile online sql script.

ALTER DATABASE DATAFILE '/oradb01/TEST/data/apps_ts_tx_data.306.705691421' online ;

The final script would be,

set heading off

set lines 2000

select 'ALTER DATABASE DATAFILE '''|| name || ''' online ;' from v$datafile;

Above command would give an output like

ALTER DATABASE DATAFILE '/oradb01/TEST/data/apps_ts_tx_data.306.705691421' online ;

ALTER DATABASE DATAFILE '/oradb01/TEST/data/apps_ts_tx_data.302.705691421' online ;

Two basic things to consider is

1) Whatever you need display as it is,should be given in ' ' quotes.

2) To get the value of a variable (in our case ‘NAME’ which gives Datafile file path and name) give it in Double Pipe. i.e, || ||

Taking our example, Lets break the SQL for better understanding.

SQL>select 'ALTER DATABASE DATAFILE ''' || name || ''' online ;' from v$datafile;

Here “ALTER DATABASE DATAFILE” is the text we want to display at first. Hence the query “select 'ALTER DATABASE DATAFILE ' “. Remember there is a space between datafile and the ending single quote. Next we want datafile name with full location, which the actual query gets.

To get the value of the Name Column. We need to place column name in || ||

select 'ALTER DATABASE DATAFILE ' || name ||

This would command would give an something like

ALTER DATABASE DATAFILE /oradb01/TEST/data/apps ……

However we want ' to be included for the datafile name value.

Hence it should be

select 'ALTER DATABASE DATAFILE ' '' || name || ''

To add a single quote we need to add additional single quote to print single quote Hence we need to give 2 single quotes. Now we just need to add online; word to the end of it.

Which is ' online ; ' . Note, you need to give space between the online and the starting single quote. 'online ; ' .Now adding it all would give

select 'ALTER DATABASE DATAFILE ' ''|| name || '' ' online ;' from v$datafile;

However the above need to be formatted as(remove the space between the quotes)

select 'ALTER DATABASE DATAFILE '''|| name || ''' online ;' from v$datafile;

Got the knack?