aspose file tools*
The moose likes Oracle/OAS and the fly likes What is the use of using EXECUTE IMMEDIATE in Oraccle PL/SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "What is the use of using EXECUTE IMMEDIATE in Oraccle PL/SQL" Watch "What is the use of using EXECUTE IMMEDIATE in Oraccle PL/SQL" New topic
Author

What is the use of using EXECUTE IMMEDIATE in Oraccle PL/SQL

Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Hi ,

Please tell me what is the use of using EXECUTE IMMEDIATE statement ??

As per the docs it says that :

The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement .

The EXECUTE IMMEDIATE statement parses the SQL string command and executes the statement.

An example of the EXECUTE IMMEDIATE in a block :



Here , i am having a query , what is the use of using EXECUTE IMMEDIATE , i mean if i dont use EXECUTE IMMEDIATE will my statement will not be created ??
For example :The below will also work where , i am not using any EXECUTE IMMEDIATE to process a statement , please explain , thanks in advance .




Please share your ideas , thanks in advance .


Save India From Corruption - Anna Hazare.
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14688
    
  16

In a PL/SQL program, you can invoke static SQL without any special notation. What is static SQL ? Static SQL is :
  • Data Manipulation Language (DML) Statements (except EXPLAIN PLAN) : INSERT, UPDATE, DELETE
  • Transaction Control Language (TCL) Statements : COMMIT, ROLLBACK...
  • SQL Functions
  • SQL Pseudocolumns : ROWID, ROWNUM...
  • SQL Operators


  • As you can see, CREATE statements are not static SQL. To invoke them, you have to use dynamic SQL, via EXECUTE IMMEDIATE.


    [My Blog]
    All roads lead to JavaRanch
    Ravi Kiran Va
    Ranch Hand

    Joined: Apr 18, 2009
    Posts: 2234

    Thanks Christophe Verré , i think without your explanation on this point , i could have never understood this point .

    Thanks once again.

    But even after using EXECUTE IMMEDIATE , i coulnt able to create a table in a procedure

    Ravi Kiran Va
    Ranch Hand

    Joined: Apr 18, 2009
    Posts: 2234

    sorry for troubling you , i realized that i dont have enough priviliges to execute Create Statement . Thanks .
    Ravi Kiran Va
    Ranch Hand

    Joined: Apr 18, 2009
    Posts: 2234

    I have one more question is :

    Does Select statement is also a Dynamic SQL :



    Because here in this case the EXECUTE UPDATE is being used in conjunction with Select statement.

    Please share your views . Thanks
    Ireneusz Kordal
    Ranch Hand

    Joined: Jun 21, 2008
    Posts: 423
    SELECT statement can also be dynamic,
    but you cannot use SELECT in a PL/SQL procedure in the same way as in a gui tool like SqlPlus, Toad or SQL-Developer.
    Interactive GUI tools accept ordinary SQL syntax like SELECT xx FROM yyy, they execute queries entered interactively by the user
    and display results of queries on the screen to the user.
    But PL/SQL is not a gui tool, it doesn't display results of the query on the screen and it doesn't accept simple SELECT syntax.
    You must use SELECT column_list INTO variable_list syntax to collect query results into PL/SQL variables (if your query returns only one row)
    or SELECT column_list BULK COLLECT INTO collection_variable_list to collect many values into collections,
    or open a cursor (cursors can be used also with dynamic SQL) and fetch row by row from returned resultset.

    Here is a documentation with many examples how to use both static and dynamic SQL in PL/SQL:
    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/static.htm#BABGEDAE
    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/dynamic.htm#CACDDACH

    I guess that you are looking for something like this:


     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: What is the use of using EXECUTE IMMEDIATE in Oraccle PL/SQL