RE: Dynamic DDL statements in Stored procedure...

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Tue, 19 Sep 2000 10:58:49 +0400
Message-Id: <10623.117315@fatcity.com>

Thanks a lot Guys...

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

-----Original Message-----

From: Murugaian Adi [mailto:adi_at_MarketingAssociates.com] Sent: Monday, September 18, 2000 7:20 PM To: 'ORACLE-L_at_fatcity.com'
Cc: 'TS2017_at_emirates.com'
Subject: RE: Dynamic DDL statements in Stored procedure...

grant create table to RAPID;

you should give this grant directly not from a roll to execute this from a stored procedure.

-----Original Message-----

From: Amar Kumar Padhi [ mailto:TS2017_at_emirates.com <mailto:TS2017_at_emirates.com> ]
Sent: Monday, September 18, 2000 6:30 AM To: Multiple recipients of list ORACLE-L 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 Received on Tue Sep 19 2000 - 01:58:49 CDT