Re: Dynamic DDL statements in Stored procedure...

From: <raman.yagna_at_db.com>
Date: Mon, 18 Sep 2000 18:59:20 +0800
Message-Id: <10623.117235@fatcity.com>

grant create any table directly to the user.

• Message History ----------------------------------------

From: TS2017_at_emirates.com on 18/09/2000 10:30 GMT

Please respond to ORACLE-L_at_fatcity.com

To: ORACLE-L_at_fatcity.com
cc:
Subject: Dynamic DDL statements in Stored procedure...

Hi,
can I dynamically create a table, using dbms_sql, from a stored procedure? I am getting the following error. If I run the routine directly at the sqlprompt, it is executed successfully, but fails as a stored procedure.

create or replace procedure am_test is

     Z    integer;
     Y    integer;
        x number;
        l_ddl   varchar2(100);
begin
     l_ddl := 'create table am_test_table (x number)';
     dbms_output.put_line(l_ddl);
     Z := dbms_sql.open_cursor ;
     dbms_sql.parse(Z, l_ddl, dbms_sql.native);
     Y := dbms_sql.execute(Z);
     dbms_sql.close_cursor(Z);

End;

sql> exec am_test

ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "RAPID.AM_TEST", line 11
ORA-06512: at line1

Thanks,
Amar
00-971-50-7883254
ts2017_at_emirates.com
amar_padhi_at_hotmail.com
amar_padhi_at_musclemail.com

--
Author: Amar Kumar Padhi
  INET: TS2017_at_emirates.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Sep 18 2000 - 05:59:20 CDT