Re: dynamic SQL- create statement in stored procedure: insuf. priv.

>My problem is the following message that I get executing the procedure test:
>
>SQLWKS> show errors;
>Keine Fehler bei PROCEDURE TEST
>SQLWKS>
>SQLWKS> execute o2o_test;
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
>ORA-06512: at "SYS.DBMS_SQL", line 32
>ORA-06512: at "BAANNT.O2O_TEST", line 19
>ORA-06512: at line 2
>
>
>the source code is ...
>
>create or replace procedure test Is
>
>v_date char(9);
>v_name char(30);
>v_cursor number;
>v_statement varchar(2000);
>v_numrows integer;
>
>begin
>
>v_date := '_'||to_char(sysdate,'YYYYMMDD');
>v_name := 'MISC'||v_date;
>v_cursor := DBMS_SQL.OPEN_CURSOR;
>v_statement := 'create table ' || v_name|| ' as select * from TABLE1
>where...';
>
>DBMS_SQL.PARSE(v_Cursor, v_statement, DBMS_SQL.NATIVE);
>v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);
>DBMS_SQL.CLOSE_CURSOR(v_Cursor);
>commit;
>
>end;
>/
>
>Any idea which privileges I have to set? I am using oracle 8 (NT) where the
>destination platform is Oracle 7.3.4 (NT).
>The user has the folowing rights:
>- connect, dba, exp_full_database, imp_full_database, resource
>
>Thanks for any help (if you want to answer fast, please use (additional) my
>email: tischendorf_at_arkusa.de)
>
>Manfred Tischendorf
>
>

The problem is probably due to the user either lacking the create table system privilege, or select privileges on the table being selected from in the 'create table..' dynamic sql. Stored packages/procedures etc. rely on system privileges being specifically granted to a user, ignoring any privileges granted via roles.
Hope this helps,
  Rob Received on Mon May 29 2000 - 00:00:00 CDT