Task: Oracle procedure, what generate CREATE TABLE statement

Task

I've given the follow task:
Make a PL/SQL procedure for Oracle, what put CRATE TABLE statement to DBMS output.
The signature of prodecure is
PROCEDURE cr_tab(p_owner VARCHAR2, p_table VARCHAR2)

The procedure enought to work with follow types:

• CHAR
• VARCHAR2
• NCHAR
• NVARCHAR2
• BLOB
• CLOB
• NCLOB
• NUMBER
• FLOAT
• BINARY_FLOAT
• DATE
• ROWID

Here is a test table:

CREATE TABLE type_table (
  c10 CHAR(10) DEFAULT 'bubu', 
  vc20 VARCHAR2(20), 
  nc10 NCHAR(10), 
  nvc15 NVARCHAR2(15), 
  blo BLOB, 
  clo CLOB, 
  nclo NCLOB, 
  num NUMBER, 
  num10_2 NUMBER(10,2), 
  num10 NUMBER(10) DEFAULT 100,
  flo FLOAT, 
  bin_flo binary_float DEFAULT '2e+38', 
  bin_doub binary_double DEFAULT 2e+40,
  dat DATE DEFAULT TO_DATE('2007.01.01', 'yyyy.mm.dd'), 
  rid ROWID
);

Solution

This task is solvable with DBA_TAB_COLUMNS table. The procedure is the follow:

CREATE OR REPLACE procedure cr_tab(p_owner VARCHAR2, p_table VARCHAR2) IS
  sep VARCHAR2(2);
  output VARCHAR2(32767);
  cursor cols IS
    SELECT * FROM DBA_TAB_COLUMNS WHERE owner=UPPER(p_owner) AND table_name = UPPER(p_table);
BEGIN
  sep:='';
  dbms_output.put_line('CREATE TABLE '||p_table||' (');
 
  FOR col IN cols
  LOOP
    output:=output||sep||col.column_name||' '||col.data_type;
    IF col.data_type = 'NUMBER' AND col.data_precision IS NOT NULL THEN
      output:=output||'('||col.data_precision||','||col.data_scale||')';
    ELSIF INSTR(col.data_type, 'CHAR') > 0 THEN
      output:=output||'('||col.char_length||')';
    END IF;
 
 
    IF col.data_default IS NOT NULL then
      output:=output||' DEFAULT '||col.data_default;
    END IF;
    sep:=','||chr(10);
  END LOOP;
  output:=output||chr(10)||');';
  dbms_output.put_line(output);
END;

Test

Call the procedure:

SET SERVEROUTPUT ON;
 
BEGIN
  cr_tab('myuser','type_table');
END;

After this block executed the DBMS output is:

CREATE TABLE type_table (
C10 CHAR(10) DEFAULT 'bubu',
VC20 VARCHAR2(20),
NC10 NCHAR(10),
NVC15 NVARCHAR2(15),
BLO BLOB,
CLO CLOB,
NCLO NCLOB,
NUM NUMBER,
NUM10_2 NUMBER(10,2),
NUM10 NUMBER(10,0) DEFAULT 100,
FLO FLOAT,
BIN_FLO BINARY_FLOAT DEFAULT '2e+38',
BIN_DOUB BINARY_DOUBLE DEFAULT 2e+40,
DAT DATE DEFAULT TO_DATE('2007.01.01', 'yyyy.mm.dd'),
RID ROWID
);

Feel free to use this procedure anywhere.

Please, enter any comment!