| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: dynamic DML statements in stored procedures
A copy of this was sent to Cristian Veronesi <c.veronesi_at_crpa.it>
(if that email address didn't require changing)
On Thu, 02 Jul 1998 14:51:10 +0200, you wrote:
>Greetings.
>I'm using Oracle Server 7.2.2.4 and I wish to create a stored procedure
>that runs a dynamic DML statement, as shown in the "Oracle 7 Server
>Application Developer's Guide".
>
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence dbms_sql won't be able to do it either.
You probably have the privelege to do what you are trying to do in dbms_sql via a role. Grant the privelege (CREATE TABLE) directly to the owner of the procedure and it'll work.
[snip]
>
>SQL> execute ptest
>begin ptest; end;
>
>*
>ERROR at line 1:
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
>ORA-06512: at "SYS.DBMS_SQL", line 25
>ORA-06512: at "INFO2000.PTEST", line 8
>ORA-06512: at line 1
>
>
[snip]
>Cristian Veronesi - computer programmer
>CRPA spa - Centro Ricerche Produzioni Animali / Research Centre for
>Animal Production
>Reggio Emilia, Italy
>e-mail: c.veronesi_at_crpa.it
>www: http://www.crpa.it/
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jul 02 1998 - 08:48:22 CDT
 |
 |