Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

No EXECUTE IMMEDIATE but stll CREATE TABLE using stored procedure.

Posted on 2007-03-23
8
Medium Priority
?
660 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 23

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 23

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 600 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Accepted Solution

by:
Steve Wales earned 900 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

715 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