asked on

How to write a SQL procedure to automatically create table in Oracle?

How to write a SQL procedure to automatically create a table in Oracle?
I want to
- create a table
- create synonym
- grant all the necessary rights
that's all
However, the name of the table need to be dynamic,
If I schedule to the server to run the SQL procedure in December, then the code need to get the SYSDATE then add 1 year, then the name of the table will be INVOICES_2010
How can I do it?

grzessio

try something like 'execute immediate <ddl>;'

create or replace procedure my_proc(in_tbl_name ...
is
begin
 
v_create_table_ddl := 'create table ' || in_tbl_name || ....;
execute immediate v_create_table_ddl;
 
v_create_synonym_ddl := 'create synonym ' || ...
execute immediate v_create_synonym_ddl;
 
v_create_grants_dcl := 'grant ...' || ...
execute immediate v_create_grants_dcl;
 
end my_proc;

Open in new window

kezzemil

You must use dynamic sql and provide the needed rights to the owner of the package/procedure

The procedure can be someting like this :

CREATE OR REPLACE PROCEDURE new_table IS
l_statement VARCHAR2 (1000);
Dummy carchar2(4);
BEGIN
Dummy := to_char(sysdate,'YYYY');
l_statement := 'CREATE_TABLE INVOICES_' || Dummy ;
BEGIN
EXECUTE IMMEDIATE l_statement;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( l_statement
|| '--'
|| SUBSTR (SQLERRM, 1, 100)
);
END;
END;

Billy Ma

ASKER

Can I not create PROCEDURE but put my code in a .sql file, then call it?

kezzemil

off course you can,
use "DECLARE" to start your pl/SQL block in your sql file (do not forget to put the / at the end to execute the PL/SQL block

DECLARE
l_statement VARCHAR2 (1000);
Dummy carchar2(4);
BEGIN
Dummy := to_char(sysdate,'YYYY');
l_statement := 'CREATE_TABLE INVOICES_' || Dummy ;
BEGIN
EXECUTE IMMEDIATE l_statement;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( l_statement
|| '--'
|| SUBSTR (SQLERRM, 1, 100)
);
END;
END;
/

Billy Ma

ASKER

I have created a procedure, and

SQL> EXEC pr_Create_Table;
PL/SQL procedure successfully completed.
but the table does not create.

Billy Ma

ASKER

code attached

/**************************************************************************
*
*	Application:    ABC
*	Purpose:        Create Table
*	Parameters:     NULL
*	Return:         NULL
*
**************************************************************************/
 
CREATE OR REPLACE PROCEDURE ABC.pr_Create_Table
IS
  mv_year      VARCHAR2(4)    := TO_CHAR(ADD_MONTHS(SYSDATE, 12), 'YYYY');
  mv_statement VARCHAR2(1000) := NULL;
 
BEGIN
  mv_statement := 'CREATE TABLE INVOICES_' || mv_year || ' TABLESPACE DW_DATA AS SELECT * FROM INVOICES_U WHERE 1=2';
  BEGIN
    EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'ALTER TABLE INVOICES_' || mv_year 
               || ' ADD CONSTRAINT INVOICES_' || mv_year || '_PK PRIMARY KEY (CO_CODE, INVOICE_NO)'
	       || ' USING INDEX TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX INVOICE_' || mv_year || '_ACCOUNT_I ON INVOICES_' || mv_year || '(CO_CODE, CUST_CODE)'
               || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX INVOICE_' || mv_year || '_BILL_TO_I ON INVOICES_' || mv_year || '(BILL_TO, CO_CODE, DEL_DATE)'
               || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX INVOICE_' || mv_year || '_CO_DEL_DATE_I ON INVOICES_' || mv_year || '(CO_CODE, DEL_DATE)'
               || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX INVOICE_' || mv_year || '_CO_DEL_I ON INVOICES_' || mv_year || '(CO_CODE, DEL_DATE, STATUS, TEAM)'
               || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX INVOICE_' || mv_year || '_CUSTOMER_CO_I ON INVOICES_' || mv_year || '(CO_CODE, CUST_ID)'
               || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX INVOICE_' || mv_year || '_CUSTOMER_I ON INVOICES_' || mv_year || '(CUST_ID)'
               || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX INVOICE_' || mv_year || '_DEL_DATE_I ON INVOICES_' || mv_year || '(DEL_DATE, DRIVER_CODE)'
               || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE TABLE INV_ITEMS_' || mv_year || ' TABLESPACE DW_DATA AS SELECT * FROM INV_ITEMS WHERE 1=2'
               || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'ALTER TABLE INV_ITEMS_' || mv_year
               || ' ADD CONSTRAINT INV_ITEM_' || mv_year || '_PK PRIMARY KEY (CO_CODE, INVOICE_NO, LINE_NO, SUB_LINE)'
	       || ' USING INDEX TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX INV_ITEM_' || mv_year || '_AGREE_I ON INV_ITEMS_' || mv_year || '(AGREE_NO)'
               || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX INV_ITEM_' || mv_year || '_ORDT_I ON INV_ITEMS_' || mv_year || '(CO_CODE, INVOICE_NO, ORDT_TYPE, PROD_TYPE)'
               || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE TABLE WORK_ORDERS_' || mv_year || ' TABLESPACE DW_DATA AS SELECT * FROM WORK_ORDERS WHERE 1=2';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'ALTER TABLE WORK_ORDERS_' || mv_year
               || ' ADD CONSTRAINT WORDER_' || mv_year || '_PK PRIMARY KEY (WO_NUMBER)'
	       || ' USING INDEX TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX WORDER_' || mv_year || '_BILL_TO_I ON WORK_ORDERS_' || mv_year || '(CO_CODE, BILL_TO, SERVICE_DATE, STATUS)'
	       || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX WORDER_' || mv_year || '_CO_SCHEDULE_I ON WORK_ORDERS_' || mv_year || '(CO_CODE, TEAM, SCHEDULE, STATUS, DIST_CODE, SERVICE_DATE)'
	       || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX WORDER_' || mv_year || '_CUST_CODE_I ON WORK_ORDERS_' || mv_year || '(CO_CODE, CUST_CODE)'
	       || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX WORDER_' || mv_year || '_EQUIPMENT_FK_I ON WORK_ORDERS_' || mv_year || '(SEQ_NUMBER)'
	       || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX WORDER_' || mv_year || '_INVOICE_I ON WORK_ORDERS_' || mv_year || '(CO_CODE, INVOICE_NO)'
	       || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE TABLE WO_ITEMS_U_' || mv_year || ' TABLESPACE DW_DATA AS SELECT * FROM WO_ITEMS_U WHERE 1=2';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'ALTER TABLE WO_ITEMS_U_' || mv_year
               || ' ADD CONSTRAINT WO_ITEM_' || mv_year || '_PK PRIMARY KEY (PART_CODE, WO_NUMBER)'
	       || ' USING INDEX TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE TABLE ACCT_DAILY_CONS_' || mv_year || ' TABLESPACE DW_DATA AS SELECT * FROM ACCT_DAILY_CONS WHERE 1=2';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'ALTER TABLE ACCT_DAILY_CONS_' || mv_year
               || ' ADD CONSTRAINT ACCT_DAILY_CONS_' || mv_year || '_PK PRIMARY KEY (DEL_DATE, TEAM, CO_CODE, CUST_CODE, ORDER_PROD_CODE, PROD_CODE, ORDT_TYPE, REDEEM_TYPE)'
	       || ' USING INDEX TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'CREATE INDEX ACCT_DAILY_CONS' || SUBSTR(mv_year, -2) || '_CO_FIN_MTH_I ON ACCT_DAILY_CONS_' || mv_year || '(CO_CODE, FIN_MTH)'
               || ' TABLESPACE DW_INDEX';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'GRANT SELECT ON INVOICES_' || mv_year || ' TO USER_RPT, PUBLIC, AUTO, FIN_RPT';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'GRANT SELECT ON INV_ITEMS_' || mv_year || ' TO USER_RPT, PUBLIC, AUTO, FIN_RPT';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'GRANT SELECT ON WORK_ORDERS_' || mv_year || ' TO USER_RPT, PUBLIC, AUTO, FIN_RPT';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'GRANT SELECT ON WO_ITEMS_U_' || mv_year || ' TO USER_RPT, PUBLIC, AUTO, FIN_RPT';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
  mv_statement := 'GRANT SELECT ON ACCT_DAILY_CONS_' || mv_year || ' TO USER_RPT, PUBLIC, AUTO, FIN_RPT';
  BEGIN
      EXECUTE IMMEDIATE mv_statement;
  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
  END;
 
END pr_Create_Table;
/

Open in new window

ASKER CERTIFIED SOLUTION

kezzemil

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial