Dynamic SQL

    Its allowed you to built SQL statements whose contents are not known until runtime.
    Here we are building the string and passed it to the server for execution.
      
Disadvantage

    slower than static SQL.
    since syntax checking and object validation cannot be done until runtime.

        CREATE TABLE trans_jan_to_dec_12 (  cust_id NUMBER(3), bill_date DATE, bill_amt NUMBER(6,2) );
        CREATE TABLE trans_jan_to_dec_13 (  cust_id NUMBER(3), bill_date DATE, bill_amt NUMBER(6,2) );

        BEGIN
        --inserting data into trans_jan_to_dec_12
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'12-MAR-12',4000);
        INSERT INTO trans_jan_to_dec_12 VALUES(115,'21-JAN-12',5700);
        INSERT INTO trans_jan_to_dec_12 VALUES(116,'19-JUN-12',3300);
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'19-JUN-12',1200);
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'27-AUG-12',1300);
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'16-MAR-12',1000);
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'14-APR-12',2000);
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'12-SEP-12',7000);
        --inserting data into trans_jan_to_dec_13
        INSERT INTO trans_jan_to_dec_13 VALUES(123,'13-FEB-13',5600);
        INSERT INTO trans_jan_to_dec_13 VALUES(143,'24-APR-13',2300);
        INSERT INTO trans_jan_to_dec_13 VALUES(152,'15-JUL-13',7600);
        INSERT INTO trans_jan_to_dec_13 VALUES(114,'30-MAR-13',1000);
        INSERT INTO trans_jan_to_dec_13 VALUES(114,'02-APR-13',2000);
        INSERT INTO trans_jan_to_dec_13 VALUES(114,'09-SEP-13',7000);
        END;
        /

        SELECT * FROM trans_jan_to_dec_12;
        SELECT * FROM trans_jan_to_dec_13;

        --simple example 1

        DECLARE
               v_cust_id   number(3);
               v_bill_date date;
               v_bill_amt  number(6,2);
               Query_str VARCHAR2(200) := 'SELECT * FROM trans_jan_to_dec_12 WHERE cust_id = 115';
        BEGIN
           EXECUTE IMMEDIATE Query_str INTO v_cust_id, v_bill_date, v_bill_amt;
           dbms_output.put_line('customer id :  ' || v_cust_id);
           dbms_output.put_line('Bill date   :  ' || v_bill_date);
           dbms_output.put_line('Bill Amount :  ' || v_bill_amt);
        END;
        /
 
sample output
       
        customer id :  115
        Bill date   :  21-JAN-12
        Bill Amount :  5700
       
        --simple example 2 (with bind variable)
       
USING PLACEHOLDERS
 
    Placeholders can be used to replace a missing value at runtime. A placeholder is a name preceded by : (colon).
    The placeholder is to be replaced with a value through USING clause of EXECUTE IMMEDIATE statement.

        DECLARE
               v_cust_id   number(3);
               v_bill_date date;
               v_bill_amt  number(6,2);
               Query_str VARCHAR2(200) := 'SELECT * FROM trans_jan_to_dec_12 WHERE cust_id = :idval';
        BEGIN
           EXECUTE IMMEDIATE Query_str INTO v_cust_id, v_bill_date, v_bill_amt USING 115;
           dbms_output.put_line('customer id :  ' || v_cust_id);
           dbms_output.put_line('Bill date   :  ' || v_bill_date);
           dbms_output.put_line('Bill Amount :  ' || v_bill_amt);
        END;

--sample output        
        customer id :  115
        Bill date   :  21-JAN-12
        Bill Amount :  5700
       

        CREATE OR REPLACE PROCEDURE total_purchase_amt_sp (p_year IN NUMBER, p_cust_id IN NUMBER)
        AS
          v_total_amt   NUMBER(8);
          sql_query   VARCHAR2(3000) := 'SELECT SUM(bill_amt) FROM trans_jan_to_dec_' || p_year || ' WHERE cust_id = :1';
        BEGIN
             EXECUTE IMMEDIATE sql_query INTO v_total_amt USING p_cust_id ;
             dbms_output.put_line('Total purchase amount of the customer with id '||p_cust_id ||' in 20'|| p_year ||' is ' || v_total_amt ||' INR');
        END total_purchase_amt_sp;
        /


        BEGIN
           total_purchase_amt_sp(12,114);
           total_purchase_amt_sp(13,114);
        END;
        /

--sample output
 
    Total purchase amount of the customer with id 114 in 2012 is 16500 INR
    Total purchase amount of the customer with id 114 in 2013 is 10000 INR


DDL statements using Dynamic Sql

        DECLARE
           Query_str VARCHAR2(200) := 'TRUNCATE TABLE trans_jan_to_dec_12';
        BEGIN
           EXECUTE IMMEDIATE Query_str;
        END;
        /

SELECT * FROM trans_jan_to_dec_12;


    Fetching row from cursor

            CREATE OR REPLACE PROCEDURE dynamic_sql_with_cursor_sp         
(p_year IN NUMBER)
            AS
              v_cust_id   NUMBER(3);
              v_tot_amt  NUMBER;
              TYPE all_data_rc IS REF CURSOR;
              all_rec all_data_rc;
              sql_query   CLOB := 'SELECT cust_id,SUM(bill_amt) FROM trans_jan_to_dec_' || p_year || ' GROUP BY cust_id';
              v_sum NUMBER(20) := 0;
            BEGIN
                             dbms_output.put_line('***************************************');
                             dbms_output.put_line('Customer total purchace record in 20'|| p_year);
                             dbms_output.put_line('***************************************');
                 OPEN all_rec FOR sql_query;
                      LOOP
                          FETCH all_rec INTO v_cust_id,v_tot_amt;
                          EXIT WHEN all_rec%NOTFOUND;
                          v_sum := v_sum + v_tot_amt;
                             dbms_output.put_line('customer id :  ' || v_cust_id);
                             dbms_output.put_line('Bill Amount :  ' || v_tot_amt);
                             dbms_output.put_line('--------------------');
                      END LOOP;
                     
                             dbms_output.put_line('***************************************');
                             dbms_output.put_line('Total amount    :         '|| v_sum);
                             dbms_output.put_line('***************************************');
                           
            END dynamic_sql_with_cursor_sp;
            /


        BEGIN
           dynamic_sql_with_cursor_sp(12);
        END;       

--sample output
       
        ***************************************
        Customer total purchase record in 2012
        ***************************************
        customer id :  114
        Bill Amount :  16500
        --------------------
        customer id :  115
        Bill Amount :  5700
        --------------------
        customer id :  116
        Bill Amount :  3300
        --------------------
        ***************************************
        Total amount    :         25500
        ***************************************


DROP PROCEDURE total_purchase_amt_sp;
DROP TABLE     trans_jan_to_dec_12;
DROP TABLE     trans_jan_to_dec_13;