EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PLSQL execute immediate

Home » Data Science » Data Science Tutorials » Database Management Tutorial » PLSQL execute immediate

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.

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

Popular Course in this category
Sale
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,900 ratings)
Course Price

View Course

Related Courses
PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

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

This is a guide to PLSQL execute immediate. Here we discuss the general syntax of execute immediate clause, its components, and also learn about its implementation with the help of certain examples. You may also have a look at the following articles to learn more –

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Database Management Tutorial
  • PL/SQL
    • What is PL/SQL?
    • Careers in PL/SQL
    • PLSQL procedure
    • PL/SQL Exception
    • PL/SQL LIKE
    • PL/SQL Raise Exception
    • PLSQL rowtype
    • PLSQL? bind variables
    • PL/SQL Record
    • PL/SQL WITH
    • PL/SQL bulk collect
    • PL/SQL Block Structure
    • PL/SQL else if
    • PL/SQL nvl2
    • PL/SQL Package
    • PL/SQL exists
    • PL/SQL instr
    • PL/SQL listagg
    • PL/ SQL Formatter
    • PLSQLlength
    • PL/SQL Commands
    • PL/SQL Data Types
    • CASE statement in PL/SQL
    • PL/SQL IF Statement
    • Loops in PL/SQL
    • PL/SQL Add Column
    • For Loop in PLSQL
    • PL/SQL Cursor Loop
    • PLSQL Array
    • Cursors in PL/SQL
    • PL/SQL FOR Loop Cursor
    • PL/SQL Queries
    • PL/SQL SELECT INTO
    • PL/SQL TO_CHAR
    • PL/SQL UNION
    • PL/SQL NOT EQUAL
    • PL/SQL varray
    • PL/SQL Concatenate
    • PL/SQL UPDATE
    • PL/SQL TRIM
    • PL/SQL GROUP BY
    • PL/SQL GOTO
    • PL/SQL Date Functions
    • PL/ SQL having
    • PL/SQL to_DATE
    • PL/SQL NVL
    • PLSQL format date
    • PLSQL mod
    • PLSQL round
    • PL/SQL Boolean
    • PL/SQL exit
    • PL/SQL DECODE
    • PL/SQL ROWNUM
    • PLSQL?pivot
    • PLSQL string functions
    • PL/SQL Block
    • PL/SQL Function
    • PL/SQL Unwrapper
    • PL/SQL Table
    • PL/SQL ALTER TABLE
    • PLSQL execute immediate
    • Triggers in PL/SQL
    • PL/SQL Collections
    • PL/SQL stored procedure
    • PL/SQL Anonymous Block
    • PLSQL Interview Questions
  • DataBase Management
    • Text Data Mining
    • Roles of Database Management System in Industry
    • SQL Server Database Management Tools
    • Database administrator skills
    • Database Management Systems Advantages
    • Database Testing Interview Questions
    • Data Administrator
    • Database Administrator
    • Database Management Software
    • DataStage
    • Types of Database Models
    • Types of Database
    • Hierarchical Database Model
    • Relational Database
    • Relational Database Advantages
    • Operational Database
    • What is RDBMS?
    • What is DB2?
    • Data Masking Tools
    • Database Security
    • Data Replication
    • Bitmap Indexing
    • Second Normal Form
    • Third Normal Form
    • Fourth Normal Form
    • Data Definition Language
    • Data Manipulation Language
    • Data Control Language
    • Transaction Control Language
    • Conceptual Data Model
    • Entity-Relationship Model
    • Relational Database Model
    • Sequential File Organization
    • Checkpoint in DBMS
    • Teradata Create Table
    • Centralized Database
    • Data Storage in Database
    • Thomas write Rule
    • DBA Interview Questions
    • What is JDBC?
    • jdbc hive
    • Apriori Algorithm
    • JDBC Architecture
    • JDBC Interview Questions
    • Wildcard Characters
    • Distributed Database System
    • Multidimensional Database
  • TSQL Basic
    • TSQL
    • What is T-SQL
    • T-SQL Commands
    • T-SQL String Functions
    • TSQL Interview Questions
  • MariaDB
    • MariaDB Versions
    • MariaDB?list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB?boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL
  • SQLite
    • What is SQLite
    • SQLite Commands
    • SQLite Data Types
    • SQLite COUNT
    • SQLite Boolean
    • SQLite autoincrement
    • SQLite select
    • SQLite? Bulk Insert
    • SQLite? add column
    • SQLite? concat
    • SQLite BETWEEN
    • SQLite group by
    • SQLite CASE
    • SQLite group_concat
    • SQLite array
    • SQLite? enum
    • SQLite sum
    • SQLite create table
    • SQLite Alter Table
    • SQLite Create Database
    • SQLite Delete
    • SQLite connection string
    • SQLite Database
    • SQLite Describe Table
    • SQLite Show Tables
    • SQLite exit
    • SQLite create index
    • SQLite foreign key
    • SQLite Stored Procedures
    • SQLite Extension
  • DB2
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE
    • View Serializability in DBMS
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • Dataset Normalization
    • MariaDB Max Connections
    • jdbc connection
    • MariaDB GUI
  • DBMS
    • Introduction To DBMS
    • DBMS ER Diagram
    • What is DBMS?
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • Netezza Database
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS

Related Courses

SQL Certification Course

PL/SQL Certification Course

Oracle Certification Course

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - SQL Certification Course Learn More