DBMS_SQL package
This package is used to execute a sql which gets created on the fly.
OPEN_CURSOR -- opens the dynamic cursor and
returns a number
PARSE -- it parses the dynamic sql, checks
the syntax and reports the errors if there are any
DEFINE_COLUMN -- define the columns
as in your select statement
EXECUTE -- Execute the SQL
FETCH_ROWS -- Fetch the row
COLUMN_VALUE -- Read the column Value
CLOSE_CURSOR -- Close the cursor.
Example of DBMS_SQL
In the following example we are generating all the table names and the number of rows in each table.
DECLAREcursor c1 is select table_name from user_tables;
var_table_name user_tables.table_name%type;
cur_cur integer;
var_sqlstmt varchar(300);
Kount number(15);
retval integer;
type knt_rec is record ( kount number(15) );
a knt_rec;
BEGIN
Open c1;
Loop
Fetch c1 into var_table_name;
If c1%notfound then
exit;
End If;
var_sqlstmt := 'select count(*) Kount from ' || var_table_name ;
--dbms_output.put_line(var_sqlstmt);
cur_cur := dbms_sql.open_cursor;
dbms_sql.parse(cur_cur,var_sqlstmt,1);
dbms_sql.define_column(cur_cur,1, a.kount );
retval := dbms_sql.execute(cur_cur);
If dbms_sql.fetch_rows( cur_cur ) > 0 then
dbms_sql.column_value( cur_cur, 1, kount);
End If;
dbms_sql.close_cursor(cur_cur);
dbms_output.put_line ( var_table_name || ' has ' || kount || ' rows ' );
End Loop;
Close c1;
END;