Dynamic SQL(Execute Immediate)

The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block.
The main argument to EXECUTE IMMEDIATE is the string containing the SQL statement to execute.

You can build up the string using concatenation, or use a predefined string.

When constructing a single SQL statement in a dynamic string, do not include a semicolon (;) at the end
inside the quotation mark.

When constructing a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement
and at the end of the anonymous block;

There will be a semicolon immediately before the end of the string literal,
and another following the closing single quotation mark

DECLARE
     sqlString  VARCHAR2(200);
     codeBlock VARCHAR2(200);
    BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE execute_table (col1 VARCHAR(10))';
      FOR v_Counter IN 1..10 LOOP
        sqlString :=
          'INSERT INTO execute_table
            VALUES (''Row ' || v_Counter || ''')';
       EXECUTE IMMEDIATE sqlString;
     END LOOP;
     codeBlock :=
       'BEGIN
          FOR v_Rec IN (SELECT * FROM execute_table) LOOP
            DBMS_OUTPUT.PUT_LINE(v_Rec.col1);
          END LOOP;
        END;';
     EXECUTE IMMEDIATE codeBlock;
     EXECUTE IMMEDIATE 'DROP TABLE execute_table';
   END;

=================

DECLARE
      sqlString  VARCHAR2(1000);
      codeBlock VARCHAR2(1000);

      CURSOR c_EconMajor IS
        SELECT *
          FROM lecturer
         WHERE major = 'Economics';
    BEGIN
     sqlString :=
       'INSERT INTO session (department, course, description,
                             max_lecturer, current_lecturer,
                             num_credits)
          VALUES (:dep, :course, :descr, :max_s, :cur_s, :num_c)';

     EXECUTE IMMEDIATE sqlString USING
       'ECN', 103, 'Economics 103', 10, 0, 3;

     FOR myLecturerRec IN c_EconMajor LOOP
       EXECUTE IMMEDIATE
          'INSERT INTO myStudent
              (student_ID, department, course, grade)
            VALUES (:id, :dep, :course, NULL)'
         USING myLecturerRec.ID, 'ECN', 103;

       codeBlock :=
         'BEGIN
            UPDATE session SET current_lecturer = current_lecturer + 1
            WHERE department = :d and course = :c;
          END;';

       EXECUTE IMMEDIATE codeBlock USING 'ECN', 103;
     END LOOP;
   END;

==================

DECLARE
         block_to_execute VARCHAR2(200) :=
            'BEGIN
                SELECT YourRow,YourDesc
                INTO :1, :2 FROM myTable
                WHERE YourRow = 2;
             END;';

      YourRow NUMBER;
       YourDesc VARCHAR2(100);
   BEGIN
       EXECUTE IMMEDIATE block_to_execute USING OUT YourRow, OUT YourDesc;
       DBMS_OUTPUT.PUT_LINE(YourRow || ' ' || YourDesc);
   END;


===============

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   TYPE NumList IS TABLE OF NUMBER;
   TYPE NameList IS TABLE OF VARCHAR2(25);
   emp_cv EmpCurTyp;
   empids NumList;
   enames NameList;
   sals   NumList;
BEGIN
   OPEN emp_cv FOR 'SELECT employee_id, last_name FROM employees';
   FETCH emp_cv BULK COLLECT INTO empids, enames;
   CLOSE emp_cv;
   EXECUTE IMMEDIATE 'SELECT salary FROM employees'
      BULK COLLECT INTO sals;
END;
/

=====================


DECLARE
   TYPE NameList IS TABLE OF VARCHAR2(15);
   enames    NameList;
   bonus_amt NUMBER := 50;
   sql_stmt  VARCHAR(200);
BEGIN
   sql_stmt := 'UPDATE employees SET salary = salary + :1
                RETURNING last_name INTO :2';
   EXECUTE IMMEDIATE sql_stmt
      USING bonus_amt RETURNING BULK COLLECT INTO enames;
END;
/


=====================

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   TYPE NameList IS TABLE OF VARCHAR2(15);
   empids NumList;
   enames NameList;
BEGIN
   empids := NumList(101,102,103,104,105);
   FORALL i IN 1..5
      EXECUTE IMMEDIATE
        'UPDATE employees SET salary = salary * 1.04 WHERE employee_id = :1
         RETURNING last_name INTO :2'
         USING empids(i) RETURNING BULK COLLECT INTO enames;
END;

Ur's

AmarAlam