Re: DDL in Dynamic PL/SQL

Dick. You must give the necessary grants (CREATE TABLE in your case) to the owner of the function DIRECTLY - not through roles.

Hope that helps.

In article <01bcdec2$456fae60$0600a8c0_at_rmw>,   "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.
>
> --
> Dick Willis, Senior Engineer
> Synchrony Industrial Controls, Inc.
> rmw_at_synchrony.com
> http://www.synchrony.com

--
====================================================
Oren Nakdimon
Golden Screens Ltd.
address: 17 Abba-Hillel st., Ramat-Gan 52522, ISRAEL
email:   oren_at_gsit.co.il
tel:     +972-3-7510836
fax:     +972-3-7518221
====================================================

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet