Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Ravi Kiran Va
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 .
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
     
    Ravi Kiran Va
    Ranch Hand
    Posts: 2234
    Eclipse IDE Firefox Browser Redhat
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 2234
    Eclipse IDE Firefox Browser Redhat
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    sorry for troubling you , i realized that i dont have enough priviliges to execute Create Statement . Thanks .
     
    Ravi Kiran Va
    Ranch Hand
    Posts: 2234
    Eclipse IDE Firefox Browser Redhat
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 423
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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:


     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic