SQL> Create or replace Procedure create_dyn_table
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 :='ORDERS_FOR_'||replace(trim(i_region_name),'','_');
11 sqlString :='CREATE TABLE '||tableName||
12 '(order_id NUMBER(10)PRIMARY KEY,
13 sale_date DATE NOT NULL,
14 total_number NUMBER,
15 total_price NUMBER(15,2),
16 company_id NUMBER(6))';
17 EXECUTE IMMEDIATE sqlString ;
18 returnCode :=0;
19 EXCEPTION WHEN OTHERS THEN
20 returnCode :=SQLCODE;
21 errorMessage :='ERR:Creating table '||tableName ||'-'||SQLERRM;
22 End;
23 /
Procedure created.
SQL>