DBMS_SQL - Dynamic SQL
This allows you to build sql statements dynamically at run time. The package is created by DBMSSQL.SQL (run from catproc).
To use DBMS_SQL the procedure running it must have the necessary privileges (granted explicitly) to be able to execute the SQL embedded within DBMS_SQL.
For the following example the owner of the procedure must have 'select any table' granted explicitly plus 'execute any procedure' or execute on dbms_sql.
DECLARE
CURSOR c1 is
select segment_name
from user_segment;
l_c1c1%ROWTYPE;
cidINTEGER;
dbms_sql_feedback INTEGER;
v_select_stmt VARCHAR2(200);
l_buf VARCHAR2(2000);
BEGIN
OPEN c1;
LOOP
FETCH c1 into l_c1;
cid := DBMS_SQL.OPEN_CURSOR;
v_select_stmt := 'select count(*) l_count from '||l_c1.segment_name;
DBMS_OUTPUT.PUT_LINE (v_select_stmt);
DBMS_SQL.PARSE (cid, v_select_stmt, DBMS_SQL.V7);
DBMS_DEFINE_COLUMN (cid, 1 , l_count);
dbms_sql_feedback :=DBMS_SQL.EXECUTE (cid);
dbms_sql_feedback := DBMS_SQL.FETCH_ROWS (cid) ;
DBMS_SQL.COLUMN_VALUE (cid, 1, l_count);
DBMS_SQL.CLOSE_CURSOR (cid) ;
l_buff := l_c1.segment_name||' '||l_count;
DBMS_OUTPUT.PUT_LINE (l_buff);
END LOOP;
CLOSE c1;
END;