Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Oracle PL/SQL Execute Immediate – Querychat

In this article, we will discuss how to execute dynamic query in Oracle PL/SQL using the PL/SQL EXECUTE IMMEDIATE command.

Oracle PL/SQL Execute Immediate

Generally while writing PL/SQL blocks, may it be in stored procedures, functions, or packages, we will know which data columns to use and from which tables. But there are situations in which we don’t know which table or which column to use. This data may have to be calculated at runtime or may be provided from the application. To execute such SQL command where the complete query is known only at runtime, we need to make the query dynamic. The dynamic query in Oracle is executed by PL/SQL EXECUTE IMMEDIATE command.

Likewise, if we ever need to execute a DDL command inside a PL/SQL block, then we have to make the query dynamic, as PL/SQL does not directly support DDL statements.  

The syntax of Oracle PL/SQL EXECUTE IMMEDIATE command is as follows:

EXECUTE IMMEDIATE dynamic_string

   [ INTO { define_variable [, define_variable ...] | record_name } ]

   [ USING [ IN | OUT | IN OUT ] bind_argument

       [, [ IN | OUT | IN OUT ] bind_argument] ... ]

   [ {RETURNING | RETURN } INTO bind_argument [, bind_argument]... ];

Syntax of EXECUTE IMMEDIATE

In the above syntax, the EXECUTE_IMMEDIATE is keyword followed by a dynamic query string which should be of VARCHAR2 data type followed by the optional keywords which includes INTO keyword, if for storing the result of the query into a variable, USING keyword, for binding the arguments, and RETURN keyword to bind the value to returning variables. 

EXAMPLE #1: PL/SQL Execute Immediate Into

In the following example, we will see how we can execute a dynamic query in Oracle PL/SQL with EXECUTE IMMEDIATE command.

We will use the following products table in the example. 

Table: products

SET SERVEROUTPUT ON;

DECLARE

    v_sql_query VARCHAR2(100);

    v_product_name VARCHAR2(50);

BEGIN

    v_sql_query := 'SELECT NAME FROM TEST WHERE Product_id = 1001';

    EXECUTE IMMEDIATE v_sql_query

    INTO v_product_name;

 

    DBMS_OUTPUT.PUT_LINE(v_product_name);

END;

EXECUTE IMMEDIATE INTO

In the above example, we first declared two variables, v_sql_query and v_product_name, to store dynamic query and the result of the query,  in the declaration section. Then in the execution section, we set the query in the v_sql_query followed by its execution using the EXECUTE IMMEDIATE command. 

In the above query, we inserted the output of the query into the v_product_name variable using EXECUTE IMMEDIATE INTO command. This is similar to how we use the SELECT INTO statement.

While writing a dynamic query in Oracle, we must enclose the query within the single quotation (‘’) marks and should not append a semicolon(;) at the end of the query within the quotes. 

The above query gives the following output.

PL/SQL procedure successfully completed. Scissors

EXAMPLE #2: PL/SQL Execute Immediate Using

In the above example, we saw how we can execute a dynamic query in Oracle. In this example, we will see how we can bind arguments in a dynamic query.

The following query is similar to the query in Example 1 except that the PL/SQL block is now inside a stored procedure that takes p_product_id as an input parameter which is later bound into the dynamic query.

CREATE PROCEDURE GETPRODUCT(p_product_id IN INT)

AS

    v_sql_query VARCHAR2(100);

    v_product_name VARCHAR2(50);

BEGIN

    v_sql_query := 'SELECT NAME FROM TEST WHERE Product_id = :1';

    EXECUTE IMMEDIATE v_sql_query

    INTO v_product_name

    USING p_product_id;

 

    DBMS_OUTPUT.PUT_LINE(v_product_name);

END;

EXECUTE IMMEDIATE USING

In the above query notice that the p_product_id parameter is not directly used in the query string v_sql_query. Instead, a position is given in the query string and the p_product_id parameter is later passed using the EXECUTE IMMEDIATE USING command, with the USING  keyword followed by p_product_id in the respective position. 

After the stored procedure is created, we can call the procedure as follows.

EXECUTE GETPRODUCT(1002);

EXECUTE GETPRODUCT(1003);

In the above query we invoked the GETPRODUCT procedure two times. The above queries returns the following output.

PL/SQL procedure successfully completed.

Earphones

PL/SQL procedure successfully completed.

Glue

Still need some help? Connect with a live SQLExpert here for some 1 on 1 help. Your first session is always free!

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc