Create SQL from SQL

In this article I would like to demonstrate the power of generating SQL statements from a SQL another SQL statement. This kind of tool is invaluable for any Oracle DBA or Oracle developer. I say Oracle because the dynamic SQL statements which follow are all written for the Oracle database. However, the same principles apply to all other databases which use SQL to interact with them but the syntax will probably be different.

So, how do you generate SQL from SQL? OK, let’s start off with a simple example to show you how it all works:

SQL> SELECT 'hello world!' as text from dual;

Hello World!

We can see it’s simple enough to output text from a select statement so that you can print out anything you want, literally. Whenever you use the single quotation mark it is treated as a sign that you want to tell the SQL engine anything which follows is going to be printed out as it is, and not interpretted by the database itself.

Create SQL scripts from SQL Statements

There are many uses for this kind of functionality, particularly for an Oracle DBA doing administrative tasks. One such example where you want to generate a set of SQL commands from one SQL statements is when you are changing the file location for your datafiles. Imagine that you are changing the location of your files on disk. You could use something like this:

SELECT 'alter database rename file '''||name||''' to '''||replace(name,'D:\DB','D:\DB1')||'''' as cmd
FROM v$datafile
WHERE name LIKE 'D:\DB%';

In the above statement we are using the name columne of the v$datafile view to retrieve all the datafile names in the database where the name is like ‘R:\ORACLE\DB1\%’ and then the replace function displays D:\ORADATA\DB1 in its place in the output string.

To generate SQL commands in this way saves so much time. Imagine if you had over 500 data files and wanted to change all the names, you’d have to use a text editor, search and replace, etc. However, through the use of this method you can create the SQL statements from one SQL statement and you are done. No messing around with text editors. Also, you can run the command whenever you like and not have to worry about a file having been added while you were editing the commands in notepad!

In fact, you can even wrap this inside of a loop to execute an anonymous pl/sql block when you want to run it:

for i in (select 'alter database rename file '''||name||''' to '''||replace(name,'D:\DB','D:\DB1')||'''' as cmd
from v$datafile
where name like 'D:\DB%')
execute immediate i.cmd;
end loop;

Generate SQL from SQL Example

There are other DBA administrative uses for it, too. Here is an example of how you would generate a script for granting privileges to a user based on another user’s existing privileges:

SELECT 'grant '||privilege||' to user2;'
FROM dba_sys_privs
WHERE grantee = 'USER1';

And there you have it, how to generate SQL from SQL. There really are so many uses for this that you will find yourself using it everyday if you are an active Oracle DBA. It’s an amazing time saver, reliable and removes error prone manual text editor changes. It’s a win win!

Like it, share it...

Category: SQL

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *