Dynamic PL/SQL : Dynamic SQL statement « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. Dynamic SQL statement

SQL>
SQL>
SQL> CREATE TABLE customer_region(
  2     region_id    NUMBER(4)    PRIMARY KEY,
  3     region_name  VARCHAR2(11) NOT NULL
  4  );

Table created.

SQL>
SQL>
SQL> INSERT INTO customer_region VALUES (1,'REGION1');

1 row created.

SQL> INSERT INTO customer_region VALUES (2,'REGION2');

1 row created.

SQL> INSERT INTO customer_region VALUES (3,'REGION3');

1 row created.

SQL> INSERT INTO customer_region VALUES (4,'REGION4');

1 row created.

SQL>
SQL>
SQL> Create or replace Procedure myProc(returnCode OUT NUMBER,errorMessage OUT VARCHAR2)
  2  Is
  3    CURSOR csr_region IS SELECT region_name FROM customer_region;
  4    Dyn_proc_name    VARCHAR2(100);
  5    Dyn_plsql_string VARCHAR2(1000);
  6  BEGIN
  7    FOR idx IN csr_region LOOP
  8      EXECUTE IMMEDIATE 'BEGIN update_dyn_'||replace(trim(idx.region_name),'','_')||'(:1,:2);END;'
  9      USING OUT returnCode,OUT errorMessage;
 10      IF returnCode <>0 THEN
 11        EXIT;
 12      END IF;
 13    END LOOP;
 14  EXCEPTION WHEN OTHERS THEN
 15    returnCode := SQLCODE;
 16    errorMessage := SQLERRM;
 17  END;
 18  /

Procedure created.

SQL>
SQL> drop table customer_region;

Table dropped.

SQL>
SQL>
SQL>