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.
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