Re: DDL in Dynamic PL/SQL (roles are not enabled)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/10/22
Message-ID: <344e534e.22027283@newshost>#1/1

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.

On 22 Oct 1997 08:14:39 GMT, "Dick Willis" <rmw_at_synchrony.com> wrote:

>Trying to create a table that I don't know the name of a priori. I am
>therefore using Dynamic PL/SQL in my procedure to perform the CREATE TABLE
>function.
>
>Procedure compiles fine ...
>
>Pseudo code is:
>
> Get table name
> Build SQL string
> Open cursor with DBMS_SQL.OPEN_CURSOR
> Parse SQL statement for cursor DBMS_SQL.PARSE
> Execute on cursor DBMS_SQL.EXECUTE
>
>Problem is that when procedure is called, it fails at the .PARSE step with
>an 'insufficient privilege' error.
>
>Any clues? Am creating the function in an account with the DBA role
>assigned. Also calling it from the same account, so I thought I had the
>privileges nailed. Same code with UPDATE, SELECT, etc. works OK. Seems to
>be solely due to DDL SQL call.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation

NOTICE: In order to cut down on the amount of time I spend applying silly logic to email addresses like "In order to cut down on spam, remove THIS from my email" or "In order to cut down on spam, reverse my email address", etc, I will simply hit delete when that mail bounces back to me, which is what I do with spam myself.

I haven't figured out whats more time consuming for me, deleting spam or resending emails after re-reading the whole thing to figure out the algorithm to apply. Received on Wed Oct 22 1997 - 00:00:00 CDT