SQL> Create or replace Procedure create_dyn_table2
2 (i_region_name VARCHAR2,
3 returnCode OUT NUMBER,
4 errorMessage OUT VARCHAR2)
5 authid current_user
6 Is
7 sqlString VARCHAR2(1000);
8 tableName VARCHAR2(30);
9 Begin
10 tableName :='ORDER_ITEMS_FOR_'||replace(trim(i_region_name),'','_');
11 sqlString :='CREATE TABLE '||tableName||
12 '(order_id NUMBER(10)NOT NULL,
13 item_id VARCHAR2(10)NOT NULL,
14 unit_price NUMBER(11,2)NOT NULL,
15 quantity NUMBER)';
16
17 EXECUTE IMMEDIATE sqlString ;
18
19 returnCode :=0;
20
21 EXCEPTION WHEN OTHERS THEN
22
23 returnCode :=SQLCODE;
24
25 errorMessage :='ERR:Creating/Altering table '||tableName ||'-'||SQLERRM;
26 End;
27 /
Procedure created.
SQL>