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

Store procedure to createa new table with existing table structure

deekay Aug 19, 2010 10:31 AM

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 Aug 19, 2010 10:42 AM (in response to deekay)

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 Aug 19, 2010 10:43 AM (in response to deekay)

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
Like Show 0 Likes(0)

Actions

3. Re: Store procedure to createa new table with existing table structure

737905 Aug 19, 2010 11:01 AM (in response to Frank Kulash)

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

Go to original post