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> 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> 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>