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?
RND2006Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
My understanding is that you cannot.

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

DDL, however, requires EXECUTE IMMEDIATE.
0
Steve WalesSenior Database AdministratorCommented:
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.
0
TaygerCommented:
Hello

Im a bit confused. Can you let us know why you wont/cant use "EXECUTE" or "EXECUTE IMMEDIATE"? Even using dynamic SQL you need a "EXECUTE" command starting the dynamic SQL.
So saying this I dont know any other way, except Java Stored Procudre, creating a object inside a PL/SQL procedure.
Greetings
Tayger
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Steve WalesSenior Database AdministratorCommented:
I don't know "why", other than that's what Oracle says :)

Oracle's documentation says that you can't issue DML inside a PL/SQL block without EXECUTE IMMEDIATE.  Can't tell you why they did it that way, but they did, so we users just have to live with it...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RND2006Author Commented:
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.
0
TaygerCommented:
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
0
RND2006Author Commented:
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.
0
TaygerCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.