| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: stored procedures / ora-01031
A copy of this was sent to Arnold Schommer <aschommer_at_fs-edv.de>
(if that email address didn't require changing)
On Wed, 29 Jul 1998 13:13:28 +0200, you wrote:
>I have encountered the following strange situation: In order to rebuild
>an table chosen by the user i used dynamic SQL via some own package
>calling the DBMS_SQL-Package; the instruction was something like
> CREATE TABLE schema.save_example AS (SELECT * FROM schema.example)
>(please don't focus on the schema and table names)
>Doing it this way i got the error ora-01031 on DBMS_SQL.PARSE(). Copying
>the SQL-instruction to SQL-worksheet, it worked. The same instruction
>performed via FORMS_DDL() also succeeded.
>
>The Login was the same for Forms and SQL-worksheet, so what is the
>reason of this different behaviour ? Did i do something wrong ?
>
>I'm using Oracle Forms 4.5, the Database-Version is 7.3.2.2.0 (on NT).
>
>Thanks in advance
>
>Arnold
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 directly to the owner of the procedure and it'll work.
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 Wed Jul 29 1998 - 08:58:35 CDT
 |
 |