EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials PL/SQL Tutorial PLSQL execute immediate
 

PLSQL execute immediate

Updated April 6, 2023

PLSQL execute immediate

 

 

Introduction to PLSQL execute immediate

PL/ SQL execute immediate statement is used for executing and running the dynamic statements in SQL or block of PL/ SQL code which is anonymous and not known in prior. We can make the use of PLSQL to execute immediate statements when we have to create or build the statements where we are not aware of all the where clauses, table names, restrictions, etc to be put up in prior or also when you cannot specify particular SQL statements or block directly or in advance. This statement prepares the program or statements at runtime and helps you in execution.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

In this article, we will study the general syntax of execute immediate clause, its components, and also learn about its implementation with the help of certain examples.

Syntax:

The syntax of execute immediate is as shown below –

Variable for storing statement = EXECUTE IMMEDIATE "string created dynamically"
INTO [name of record | variables which is already defined]
USING [OUT | IN | IN OUT] argument which are bind, [ [OUT | IN | IN OUT] argument which are bind] …
RETURNING/ RETURN INTO [argument which are bind (s)]

The terminologies used in the above syntax are described one by one below –

String created dynamically – This can be an expression, variable, or a string literal which will be PL/ SQL block or even a single SQL query statement. Make sure that the datatype of this should be VARCHAR2 or CHAR and not NVARCHAR2 or NCHAR.

Name of record – This can be a %ROWTYPE record or any user-defined record that holds the values of the selected row.

INTO – This is only used when we have a single row query in SQL and is used for specifying the record or variable into which the values will be retrieved. For each of the column or expression value which is retrieved from the query statement output, there should be the declaration of the corresponding variable with same or compatible datatype into which the values with be returned.

Variables that are already defined – These is the variables that are name assigned to the memory location which will store the value of the selected column.

The argument which is bind – The variables that hold the value which is passed to the dynamic PL/ SQL statement for using it informing the statement or the value which is supposed to be returned from the PL/ SQL statement after it is created dynamically.

RETURNING INTO – This clause is used only in the case of DML statements that contain a returning clause in them. Note that it cannot be used for the BULK COLLECT clause. This helps in the specification of the variables which are bind to the values of the returned column. Make sure that for each of the values being returned by the DML statement there must be a corresponding type compatible variable present inside the RETURNING INTO statement.

USING – The default mode is IN for all the parameters. It helps you to specify the list of bind arguments which can be input or output type.

Variable for storing statement – This is the variables that are name assigned to a memory location that will store the value of the selected column.

Points to be considered while using execute immediately:

  • We can specify all the arguments to be bind in the USING clause and b default all of them will be of IN mode parameters. We can place all the arguments of OUT mode in returning clause while using the DML statements having returning clause without mentioning the mode of a parameter as they are by default treated as OUT mode here.
  • When we are making the use of USING and RETURNING both in the same PL/ SQL statement then we can only mention IN mode parameters in USING and OUT mode parameters in RETURNING clause.
  • We can execute the same dynamic SQL statements again and again for different values of arguments to be bind. The EXECUTE IMMEDIATE clause prepares the dynamic query again each time. Hence, the overhead of repetitive execution of the same dynamic query with different bind arguments will still be there.
  • The variable or string argument used for the storing of dynamic query statements in EXECUTE IMMEDIATE cannot have the national character type datatype which includes NVARCHAR2 and NCHAR.
  • The string which is created dynamically can hold any SQL statement or PL/ SQL block of code except for the multi-row queries. There can be the occurrence of even the placeholders in the string for the arguments to bind. We cannot make the use of arguments to bind for passing the value of schema name to the dynamic query in PL/ SQL.

Example

Let us consider one example where we will create the dynamic query statements and use the EXECUTE IMMEDIATE to run them.

DECLARE
pl_sql_statement    VARCHAR2(200);
block_of_code       VARCHAR2(500);
customer_id      NUMBER(4) := 1520;
bill_amount      NUMBER(7,2);
store_id     NUMBER(2) := 50;
store_name   VARCHAR2(14) := 'Snehal's General Store';
city_name    VARCHAR2(13) := 'Mumbai';
customer_record     customer%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE scheme (id NUMBER, amt NUMBER)';
pl_sql_statement := 'INSERT INTO store VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE pl_sql_statement USING store_id, store_name, city_name;
pl_sql_statement := 'SELECT * FROM customer_details WHERE customer_id = :id';
EXECUTE IMMEDIATE pl_sql_statement INTO customer_record USING customer_id;
block_of_code := 'BEGIN customer_details_pkg.raise_bill_amount(:id, :amt); END;';
EXECUTE IMMEDIATE block_of_code USING 7788, 500;
pl_sql_statement := 'UPDATE customer_details SET billAmt = 2000 WHERE customer_id = :1
RETURNING billAmt INTO :2';
EXECUTE IMMEDIATE pl_sql_statement USING customer_id RETURNING INTO bill_amount;
EXECUTE IMMEDIATE 'DELETE FROM store WHERE storeno = :num'
USING store_id;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;

The output of the execution of the above code in PL/ SQL is as shown below –

PLSQL execute immediate output

Conclusion – PLSQL execute immediate

The EXECUTE IMMEDIATE statement is used in PL/ SQL while working with the dynamic query generation where the query statements to be executed are created at run time. These dynamic statements can be passed any bind arguments and also the values can be retrieved from them after executing it. The execute immediate command prepares the statements by collecting all the values received for bind arguments and parameters along with the query statements and variables that are specified.

 Recommended Articles

We hope that this EDUCBA information on “PLSQL execute immediate” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Loops in PL/SQL
  2. PL/SQL Data Types
  3. Triggers in PL/SQL
  4. PLSQL Interview Questions

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW