?
Solved

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

Posted on 2009-04-26
7
Medium Priority
?
566 Views
Last Modified: 2013-12-19
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?
0
Comment
Question by:mawingho
  • 3
  • 3
7 Comments
 
LVL 7

Expert Comment

by:grzessio
ID: 24239341
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

0
 
LVL 1

Expert Comment

by:kezzemil
ID: 24239384
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;
0
 

Author Comment

by:mawingho
ID: 24239391
Can I not create PROCEDURE but put my code in a .sql file, then call it?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 1

Expert Comment

by:kezzemil
ID: 24239457
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;
/

0
 

Author Comment

by:mawingho
ID: 24239759
I have created a procedure, and

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

Author Comment

by:mawingho
ID: 24239764
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

0
 
LVL 1

Accepted Solution

by:
kezzemil earned 2000 total points
ID: 24240037
1) This small test to prove that the concept works

"
SQL>
SQL> desc INVOICES_2010;
ERROR:
ORA-04043: object INVOICES_2010 does not exist


SQL> DECLARE
  2    mv_year      VARCHAR2(4)    := TO_CHAR(ADD_MONTHS(SYSDATE, 12), 'YYYY');
  3    mv_statement VARCHAR2(1000) := NULL;
  4  BEGIN
  5    mv_statement := 'CREATE TABLE INVOICES_' || mv_year || ' AS SELECT * FROM AI_INTERVENTIONS WHERE 1=2';
  6    BEGIN
  7      EXECUTE IMMEDIATE mv_statement;
  8    EXCEPTION
  9      WHEN OTHERS THEN
 10        DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
 11    END;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> desc INVOICES_2010;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AII_ID                                             NUMBER
 AII_DATE                                  NOT NULL DATE
 AII_NS_START                                       VARCHAR2(5)
 AII_NS_END                                         VARCHAR2(5)
 AII_I_START                                        VARCHAR2(5)
 AII_I_END                                          VARCHAR2(5)
 AII_INTERVENTION                                   VARCHAR2(9)
 AII_BTM_NUMBER                                     NUMBER(6)
 AII_PERID                                 NOT NULL VARCHAR2(8)
 AII_PF                                    NOT NULL NUMBER(1)
 AII_GUARD                                 NOT NULL NUMBER(1)
 AII_PLANNED                               NOT NULL NUMBER(1)
 AII_VALIDATED                             NOT NULL NUMBER(1)
 AII_TEAM                                           VARCHAR2(35)
"

2) Have you enabled dbms_output in your sql session before executing the procedure ?
As this procedure does perform error handling (which is a good thing), it will ALWAYS return "PL/SQL procedure successfully completed".  In order to capture the errors, you should put "set serveroutput on" in your sql session.

To continue the example where the table is created by now, I'll try to create it again by reexecuting the procedure

"
SQL> set serveroutput off
SQL>
SQL> DECLARE
  2    mv_year      VARCHAR2(4)    := TO_CHAR(ADD_MONTHS(SYSDATE, 12), 'YYYY');
  3    mv_statement VARCHAR2(1000) := NULL;
  4  BEGIN
  5    mv_statement := 'CREATE TABLE INVOICES_' || mv_year || ' AS SELECT * FROM AI_INTERVENTIONS WHERE 1=2';
  6    BEGIN
  7      EXECUTE IMMEDIATE mv_statement;
  8    EXCEPTION
  9      WHEN OTHERS THEN
 10        DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
 11    END;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL>
"

As you can see, I do not get an error although the table already existed and I can not create it 2 times.
Let's put the serveroutput on

"
SQL> set serveroutput on;
SQL> DECLARE
  2    mv_year      VARCHAR2(4)    := TO_CHAR(ADD_MONTHS(SYSDATE, 12), 'YYYY');
  3    mv_statement VARCHAR2(1000) := NULL;
  4  BEGIN
  5    mv_statement := 'CREATE TABLE INVOICES_' || mv_year || ' AS SELECT * FROM AI_INTERVENTIONS WHERE 1=2';
  6    BEGIN
  7      EXECUTE IMMEDIATE mv_statement;
  8    EXCEPTION
  9      WHEN OTHERS THEN
 10         DBMS_OUTPUT.put_line (mv_statement || '--' || SUBSTR(SQLERRM, 1, 100));
 11    END;
 12  END;
 13  /
CREATE TABLE INVOICES_2010 AS SELECT * FROM AI_INTERVENTIONS WHERE
1=2--ORA-00955: name is already used by an existing object

PL/SQL procedure successfully completed.

SQL>
"

NOW, I do get the error message and I know the problem why my procedure fails.
So, try to set the serveroutput on and re-execute your procedure to see the real problem/error why it fails.
"
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question