3 Replies Latest reply on Aug 19, 2010 11:01 AM by 737905

    Store procedure to createa new  table with existing table structure

    deekay
      Hi All,


      THis is what I want to achieve,

      I want to create procedure which will take a input parameter as name of a table lets say 'EMP',
      and this procedure will check whether that table exist or not?
      if exist then
      it will create a new table with name appending _dummy to existing table name (ex: new table will be EMP_DUMMY) ,
      and new table should have the same table structure ,index and constraint imposed on EMP table.


      NB: It should not be created using as select like create table EMP_DUMMY as select * from EMP where condition=false;



      Can any one please help me how to achieve this?






      Thanks,
      Deekay
        • 1. Re: Store procedure to createa new  table with existing table structure
          737905
          To start with,

          try something like this:
          SQL> ed
          Wrote file afiedt.buf
          
            1  declare
            2  v_tab_name VARCHAR2(100) := '&1';
            3  cursor c1 is SELECT * from all_tab_cols where table_name = v_tab_name;
            4  v_sql_Stmt varchar2(32767) := NULL;
            5  BEGIN
            6  v_sql_stmt := 'CREATE TABLE '||v_tab_name||'_DUMMY (';
            7  FOR I IN C1 LOOP
            8  v_sql_stmt := v_sql_stmt ||I.column_name||' '||I.data_type||'('||I.data_length||') '
            9                           ||(CASE WHEN I.nullable = 'N' THEN 'NOT NULL' ELSE NULL END);
           10  v_sql_stmt := v_sql_stmt || ',';
           11  END LOOP;
           12  v_sql_stmt := SUBSTR(v_sql_stmt,1,LENGTH(v_sql_stmt) - 1)||')';
           13  dbms_output.put_line(v_sql_stmt);
           14  EXECUTE IMMEDIATE v_sql_stmt;
           15* end;
          SQL> 
          
          SQL> /
          Enter value for 1: EMP
          old   2: v_tab_name VARCHAR2(100) := '&1';
          new   2: v_tab_name VARCHAR2(100) := 'EMP';
          CREATE TABLE EMP_DUMMY (EMPID NUMBER(22) )
          
          PL/SQL procedure successfully completed.
          
          SQL> desc emp_dummy;
           Name                                      Null?    Type
           ----------------------------------------- -------- ----------------------------
           EMPID                                              NUMBER(22)
          After making column not null:
          SQL> alter table emp modify (empid number not null);
          
          Table altered.
          
          SQL> desc emp;
           Name                                      Null?    Type
           ----------------------------------------- -------- ----------------------------
           EMPID                                     NOT NULL NUMBER
          
          SQL> declare
            2  v_tab_name VARCHAR2(100) := '&1';
            3  cursor c1 is SELECT * from all_tab_cols where table_name = v_tab_name;
            4  v_sql_Stmt varchar2(32767) := NULL;
            5  BEGIN
            6  v_sql_stmt := 'CREATE TABLE '||v_tab_name||'_DUMMY (';
            7  FOR I IN C1 LOOP
            8  v_sql_stmt := v_sql_stmt ||I.column_name||' '||I.data_type||'('||I.data_length||') '
            9                           ||(CASE WHEN I.nullable = 'N' THEN 'NOT NULL' ELSE NULL END);
           10  v_sql_stmt := v_sql_stmt || ',';
           11  END LOOP;
           12  v_sql_stmt := SUBSTR(v_sql_stmt,1,LENGTH(v_sql_stmt) - 1)||')';
           13  dbms_output.put_line(v_sql_stmt);
           14  EXECUTE IMMEDIATE v_sql_stmt;
           15  end;
           16  /
          Enter value for 1: EMP
          old   2: v_tab_name VARCHAR2(100) := '&1';
          new   2: v_tab_name VARCHAR2(100) := 'EMP';
          CREATE TABLE EMP_DUMMY (EMPID NUMBER(22) NOT NULL)
          
          PL/SQL procedure successfully completed.
          
          SQL> desc emp_dummy
           Name                                      Null?    Type
           ----------------------------------------- -------- ----------------------------
           EMPID                                     NOT NULL NUMBER(22)
          
          SQL> 
          Edited by: AP on Aug 19, 2010 3:42 AM
          • 2. Re: Store procedure to createa new  table with existing table structure
            Frank Kulash
            Hi, Deekay,

            Check out the Oracle-supplied function dbms_metadata.get_ddl:
            http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_metada.htm#i1019414
            • 3. Re: Store procedure to createa new  table with existing table structure
              737905
              As Frank suggested, you can also do something like:
              declare
              v_tab_name varchar2(100) := 'EMP';
              v_tab_struct CLOB := EMPTY_CLOB;
              amt number := 0;
              pos number := 0;
              v_buffer VARCHAR2(10000) := NULL;
              BEGIN
              SELECT dbms_metadata.get_ddl('TABLE',v_tab_name) INTO v_tab_struct
              FROM dual;
              amt := 100;
              pos := 1;
              LOOP
              dbms_lob.read(v_tab_struct,amt,pos,v_buffer);
              dbms_output.put_line(v_buffer);
              pos := amt + pos;
              EXIT WHEN pos > DBMS_LOB.GETLENGTH(v_tab_struct);
              END LOOP;
              IF (dbms_lob.isopen(v_tab_struct) <> 0) THEN
              DBMS_LOB.CLOSE(v_tab_struct);
              END IF;
              END;
              Modified version:
              SQL> declare
                2  v_tab_name varchar2(100) := 'EMP';
                3  v_tab_struct CLOB := EMPTY_CLOB;
                4  amt number := 0;
                5  pos number := 0;
                6  v_buffer VARCHAR2(10000) := NULL;
                7  BEGIN
                8  SELECT dbms_metadata.get_ddl('TABLE',v_tab_name) INTO v_tab_struct
                9  FROM dual;
               10  amt := DBMS_LOB.GETLENGTH(v_tab_struct);
               11  pos := 1;
               12  LOOP
               13  dbms_lob.read(v_tab_struct,amt,pos,v_buffer);
               14  --dbms_output.put_line(v_buffer);
               15  pos := amt + pos;
               16  EXIT WHEN pos > DBMS_LOB.GETLENGTH(v_tab_struct);
               17  END LOOP;
               18  IF (dbms_lob.isopen(v_tab_struct) <> 0) THEN
               19  DBMS_LOB.CLOSE(v_tab_struct);
               20  END IF;
               21  v_buffer := REPLACE(v_buffer,CHR(34)||v_tab_name||CHR(34),CHR(34)
               22                                      ||v_tab_name||'_DUMMY'||CHR(34));
               23  execute immediate v_buffer;
               24  END;
               25  /
              
              PL/SQL procedure successfully completed.
              
              SQL> desc emp_dummy;
               Name                                                                                                                   
               ---------------------------------------------------------------------------------------------------
               EMPID                                                                                                                  
              
              SQL> desc emp;
               Name                                                                                                                   
               ---------------------------------------------------------------------------------------------------
               EMPID                                                                                                                  
              
              SQL> 
              Edited by: AP on Aug 19, 2010 4:01 AM