Using DBMS_SQL for dynamic SQL
Dynamic SQL is the SQL created inside a PL/SQL program. The SQL is formed during the execution of the program. This permits to create procedures that are more general purpose. You can also do DDL statements using dynamic SQL in PL/SQL. Oracle provideds DBMS_SQL package to do dynamic SQL. This package is created when you run catproc.sql as part of the database creation. If this package is not available, login as SYS or INTERNAL and run dbmssql.sql found under $ORACLE_HOME/rdbms/admin.
I will discuss here methods of doing simple dynamic SQL using DBMS_SQL package. For more information on DBMS_SQL, please refer to the Oracle manual - "Application Developers Guide".
In normal SQL operation, the steps involved are PARSE, EXECUTE and FETCH. Here in dynamic SQL we need to explicitly perform the above steps by using procedures.
Let's take an example of creating a dynamic SQL without any paramters. We need to truncate all tables in the users schema which start with 'TRANS'. Here is the program
declare
cursor main is
select table_name
from user_tables
where table_name like 'TRANS%';
wcursor integer;
wreturn integer;
wsql varchar2 (512);
begin
for rec in main loop
/* Form the SQL statement to execute */
wsql := 'TRUNCATE TABLE ' || rec.table_name || ' REUSE STORAGE';
/* Parse the above statement in Oracle ver 7 behaviour */
dbms_sql.parse (wcursor, wsql, DBMS_SQL.V7);
/* Execute the SQL statement parsed, return value can be ignored */
wreturn := dbms_sql.execute (wcursor);
/* Close the cursor opened for the SQL */
dbms_sql.close_cursor (wcursor);
end loop;
end;
/
Since in the above program, there was no fetch involved, we did only the parse and execute. Now consider if we need to return value from the dynamic SQL. Here is an example. We need to find the count of rows in each table belonging to the user schema and insert into a count table. We get the table names from the user_tables view and form the SQL to find the count. Here is the program:
declare
cursor main is
select table_name
from user_tables;
wcursor integer;
wreturn integer;
wsql varchar2 (512);
wcount number;
begin
for rec in main loop
/* Form the SQL statement to execute */
wsql := 'SELECT COUNT(*) FROM ' || rec.table_name;
/* Parse the above statement in Oracle ver 7 behaviour */
dbms_sql.parse (wcursor, wsql, DBMS_SQL.V7);
/* Define column to be selected from the SQL, 1 indicates the first
position from the SELECT */
dbms_sql.define_column (wcursor, 1, wcount);
/* Execute and fetch the SQL statement parsed, return value can be
ignored. The two steps can also be separated. TRUE raises an exception
if the number of rows returned from the query is not one. */
wreturn := dbms_sql.execute_and_fetch (wcursor, true);
/* Return the value of the cursor into the variable by position */
dbms_sql.column_value (wcursor, 1, wcount);
/* This is to introduce another procedure in DBMS_SQL */
/* Check if the cursor is open */
if dbms_sql.is_open (wcursor) then
dbms_sql.close_cursor (wcursor);
end if;
insert into my_count_table (table_name, row_count)
values (rec.table_name, wcount);
end loop;
end;
/
For details on the procedures used here and other procedures, please refer to the Application Developers Guide, chapter "Using Dynamic SQL". You can find oracle documentation online at http://technet.oracle.com or http://metalink.oracle.com
 Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, Oracle8i OCP and Oracle9i OCA/OCP Certified DBA |
Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster. |