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

From: Manfred Tischendorf <tischendorf_at_arkusa.de>
Date: 2000/05/24
Message-ID: <9gPW4.903$K4.645@telenews.teleline.es>#1/1

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

---

      /\
     / a\       Manfred Tischendorf
    / s  \
   / u    \     Tunnelstrasse 14
  / k      \    D 70469 Stuttgart-Feuerbach
/ r        \   Deutschland / Germany
\a         /
  \        /    Tel.:    +49 711 81781-56
   \      /     Fax:     +49 711 81781-51
    \____/      E-Mail:  tischendorf_at_arkusa.de
     \  /       WWW:     http://www.arkusa.de/
      \/
       -----------------------------

Received on Wed May 24 2000 - 00:00:00 CDT