connect test/test
 
-- example procedure from PL/SQL Programming, Oracle Press
create or replace procedure recreatetemptable (
  p_description in varchar2) is
 
  v_cursor        number;
  v_createstring  varchar2(100);
  v_dropstring    varchar2(100);
  v_numrows       integer; 
begin
  v_cursor := dbms_sql.open_cursor;
 
  v_dropstring := 'drop table temp_table';
 
  begin
    dbms_sql.parse(v_cursor, v_dropstring, dbms_sql.v7);
    v_numrows := dbms_sql.execute(v_cursor);
  exception
    when others then
      if sqlcode != -942 then
        raise;
      end if;
  end;
 
  v_createstring := 'create table temp_table ' || p_description;
  dbms_sql.parse(v_cursor, v_createstring, dbms_sql.v7);
  v_numrows := dbms_sql.execute(v_cursor);
 
  dbms_sql.close_cursor(v_cursor);
exception
  when others then
    dbms_sql.close_cursor(v_cursor);
    raise;
end recreatetemptable;
/
 
execute recreatetemptable('(id number,name varchar2(30)) ');
 
<b>
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST.RECREATETEMPTABLE", line 31
ORA-06512: at line 1
</b>
 
 
 
connect system/manager
 
grant create table to test;
grant drop any table to test;
 
 
 
connect test/test
 
execute recreatetemptable('(id number,name varchar2(30)) ');
<b>
PL/SQL procedure successfully completed.
</b>