Solved

No EXECUTE IMMEDIATE but stll CREATE TABLE using stored procedure.

Posted on 2007-03-23
8
646 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

756 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