Solved

No EXECUTE IMMEDIATE but stll CREATE TABLE using stored procedure.

Posted on 2007-03-23
8
641 Views
Last Modified: 2013-12-19
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?
0
Comment
Question by:RND2006
  • 3
  • 3
  • 2
8 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 18781906
My understanding is that you cannot.

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

DDL, however, requires EXECUTE IMMEDIATE.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 18781933
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
 
LVL 2

Assisted Solution

by:Tayger
Tayger earned 200 total points
ID: 18783909
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
 
LVL 22

Accepted Solution

by:
Steve Wales earned 300 total points
ID: 18793252
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:RND2006
ID: 18793330
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
 
LVL 2

Expert Comment

by:Tayger
ID: 18794332
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
 

Author Comment

by:RND2006
ID: 18794500
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
 
LVL 2

Expert Comment

by:Tayger
ID: 18795649
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now