• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 .
 
Sheriff
Posts: 14691
16
Eclipse IDE VI Editor Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
     
    Ranch Hand
    Posts: 423
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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:


     
    Beware the other head of science - it bites! Nibble on this message:
    a bit of art, as a gift, that will fit in a stocking
    https://gardener-gift.com
    reply
      Bookmark Topic Watch Topic
    • New Topic