Link to home
Start Free TrialLog in
Avatar of RND2006
RND2006

asked on

No EXECUTE IMMEDIATE but stll CREATE TABLE using stored procedure.

I would like to create a table using a stored procedure in Oracle 10g and withou using EXECUTE IMMEDIATE command in the stored procedure. How can I possibly do this?
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

My understanding is that you cannot.

DML can be written without EXECUTE IMMEDIATE (select, insert, update, delete).

DDL, however, requires EXECUTE IMMEDIATE.
Ah, here we go, found the following in the 10g documentation:

http://download-east.oracle.com/docs/cd/B14117_01/appdev.101/b10807/11_dynam.htm#CACDDACH

Note particularly under the "Why Use Dynamic SQL?" header:

You need dynamic SQL in the following situations:

    * You want to execute a SQL data definition statement (such as CREATE), a data control statement (such as GRANT), or a session control statement (such as ALTER SESSION). Unlike INSERT, UPDATE, and DELETE statements, these statements cannot be included directly in a PL/SQL program.
SOLUTION
Avatar of Tayger
Tayger

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RND2006
RND2006

ASKER

To answer Tayger's question:
Developer has all the permissions like DML and DDL operations but for some reason the DBA has restricted the access to use the EXECUTE IMMEDIATE command with in the stored procedure.
I believe it is because of the policies of the IT Company that does not allow the usage of the command EXECUTE IMMEDIATE because it could be misutilized or something of that nature.
That is why I want to know if there is any other possibility.
Anyways I think I should just accept the fact that there is no other option except using EXECUTE IMMEDIATE.
Thanks for the response.
There is another possibilty. I thought that this is the problem you are explaining, thats why I asked back.
In general you have two possibilties to use "execute immediatte" inside a package/procdure function. One of the two optiions need to be fullfilled:

a) Create the package/function/procedure with the option "AUTHID CURRENT USER"
or
b) The user(s) executing the func/proc needs the DIRECT "EXECUTE IMMEDIATE" grant and NOT via role (therefore the DBA must agree giving it).

This should help
Tayger
Avatar of RND2006

ASKER

Tayger,
This should be the actual solution I think but I have already accepted the solution before your reply was posted. Anyways, Thank you.
I have tried the option a) and the stored procedure has compiled without any errors. I was able to create the table also using the option a) "AUTHID CURRENT USER".
Thank you once again for the solution.
NP, I'm glad I could help. I thought it was pointing to that problem, thats why I asked back. I once ran into the same problem DDL comands are very powerful (inside func/prod), thats why you need to add one of these options.
Tayger