| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Dynamic DDL statements in Stored procedure...
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C02183.E29F4036
Content-Type: text/plain;
charset="x-user-defined"
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]
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);
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). ------_=_NextPart_001_01C02183.E29F4036 Content-Type: text/html; charset="x-user-defined"Received on Mon Sep 18 2000 - 10:19:49 CDT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=x-user-defined">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2448.0">
<TITLE>RE: Dynamic DDL statements in Stored procedure...</TITLE>
</HEAD>
<BODY>
<BR>
<P><FONT SIZE=2>grant create table to RAPID;</FONT>
</P>
<P><FONT SIZE=2>you should give this grant directly not from a roll to execute this from a stored procedure.</FONT>
</P>
<BR>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Amar Kumar Padhi [<A HREF="mailto:TS2017_at_emirates.com">mailto:TS2017_at_emirates.com</A>]</FONT>
<BR><FONT SIZE=2>Sent: Monday, September 18, 2000 6:30 AM</FONT>
<BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=2>Subject: Dynamic DDL statements in Stored procedure...</FONT>
</P>
<BR>
<P><FONT SIZE=2>Hi,</FONT>
<BR><FONT SIZE=2>can I dynamically create a table, using dbms_sql, from a stored procedure?</FONT>
<BR><FONT SIZE=2>I am getting the following error. If I run the routine directly at the</FONT>
<BR><FONT SIZE=2>sqlprompt, it is executed successfully, but fails as a stored procedure.</FONT>
</P>
<P><FONT SIZE=2>create or replace procedure am_test is</FONT>
<BR> <FONT SIZE=2>Z integer;</FONT>
<BR> <FONT SIZE=2>Y integer;</FONT>
<BR><FONT SIZE=2> x number;</FONT>
<BR><FONT SIZE=2> l_ddl varchar2(100);</FONT>
<BR><FONT SIZE=2>begin</FONT>
<BR> <FONT SIZE=2>l_ddl := 'create table am_test_table (x number)';</FONT>
<BR> <FONT SIZE=2>dbms_output.put_line(l_ddl);</FONT>
<BR> <FONT SIZE=2>Z := dbms_sql.open_cursor ;</FONT>
<BR> <FONT SIZE=2>dbms_sql.parse(Z, l_ddl, dbms_sql.native);</FONT>
<BR> <FONT SIZE=2>Y := dbms_sql.execute(Z);</FONT>
<BR> <FONT SIZE=2>dbms_sql.close_cursor(Z);</FONT>
<BR><FONT SIZE=2>End;</FONT>
</P>
<P><FONT SIZE=2>sql> exec am_test</FONT>
</P>
<P><FONT SIZE=2>ERROR at line 1:</FONT>
<BR><FONT SIZE=2>ORA-01031: insufficient privileges</FONT>
<BR><FONT SIZE=2>ORA-06512: at "SYS.DBMS_SYS_SQL", line 239</FONT>
<BR><FONT SIZE=2>ORA-06512: at "SYS.DBMS_SQL", line 32</FONT>
<BR><FONT SIZE=2>ORA-06512: at "RAPID.AM_TEST", line 11</FONT>
<BR><FONT SIZE=2>ORA-06512: at line1</FONT>
</P>
<P><FONT SIZE=2>Thanks,</FONT>
<BR><FONT SIZE=2>Amar</FONT>
<BR><FONT SIZE=2>00-971-50-7883254</FONT>
<BR><FONT SIZE=2>ts2017_at_emirates.com</FONT>
<BR><FONT SIZE=2>amar_padhi_at_hotmail.com</FONT>
<BR><FONT SIZE=2>amar_padhi_at_musclemail.com</FONT>
</P>
<BR>
<P><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Author: Amar Kumar Padhi</FONT>
<BR><FONT SIZE=2> INET: TS2017_at_emirates.com</FONT>
</P>
<P><FONT SIZE=2>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=2>San Diego, California -- Public Internet access / Mailing Lists</FONT>
<BR><FONT SIZE=2>--------------------------------------------------------------------</FONT>
<BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an E-Mail message</FONT>
<BR><FONT SIZE=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT>
<BR><FONT SIZE=2>the message BODY, include a line containing: UNSUB ORACLE-L</FONT>
<BR><FONT SIZE=2>(or the name of mailing list you want to be removed from). You may</FONT>
<BR><FONT SIZE=2>also send the HELP command for other information (like subscribing).</FONT>
 |
 |