Oracle Scripting


Oracle scripting is very powerful to automate lots of SQL tasks. It can save you lots of time, boost your productivity and make your life easier!

If you are Oracle DBA or Oracle Developer, SQL scripting will be very useful tool for you. Oracle script will write your SQL statements for you using very easy to understand but powerful technique.

Here is a simple example. Let's say, you want to drop  all your tables in the current schema. You have lots of tables in the schema. So, you don't want to type individual drop table statements.

Here is a simple SQL script that will do that for you!

select 'drop  table ' || table_name || 

       ' cascade constraints;' as my_trunc_stmt

from user_tables;

The output of the above query will be as shown below.

MY_TRUNC_STMT
---------------------------------------------------------------
drop  table NEW_DEPT cascade constraints;
drop  table NEW_EMP cascade constraints;
drop  table NEW_T cascade constraints;

You can use some SQL*PLUS commands to disable heading, feedback etc and spool the output to another file and run that file to drop all the tables in the schema!

set feedback off

set heading off

spool c:\temp\a.sql

select 'drop  table ' || table_name || 

        ' cascade constraints;' as my_trunc_stmt

from user_tables;

spool off

set feedback on

set heading off

The output will go to the file c:\temp\a.sql.
Here is the content of the file.

SQL> get c:\temp\a.sql
  1  SQL>
  2  SQL> select 'drop  table ' || table_name || ' cascade constraints;' as my_trunc_stmt
  3    2  from user_tables;
  4  drop  table NEW_DEPT cascade constraints;
  5  drop  table NEW_EMP cascade constraints;
  6  drop  table NEW_T cascade constraints;
  7  3 rows selected.
  8* SQL> spool off

You can either copy/paste the content of the file, or you can run it to drop all the tables.

SQL> @c:\temp\a.sql

SQL> drop  table NEW_DEPT cascade constraints;

Table dropped.

SQL> drop  table NEW_EMP cascade constraints;

Table dropped.

SQL> drop  table NEW_T cascade constraints;

Table dropped.

With the power of scripting, the number of tables don't matter.

Hope you like this simple demo.

==================================================================
If you are interested in the book "Oracle SQL Scipting". here is the link.

http://www.amazon.com/Oracle-SQL-Scripting-ebook/dp/B00E2T1DVA/ref=sr_1_1?ie=UTF8&qid=1374436123&sr=8-1&keywords=oracle+sql+scripting