Re: Script to generate Table DDL

From: M. Bhatti <mohammed.bhatti_at_mci.com>
Date: Wed, 11 Nov 1998 16:48:09 -0500
Message-ID: <364A0619.C6B9ED84@mci.com>

Jorge Meirim wrote:

> I'am glad you have mentioned and called it that way.
> Try next time imp user/pwd file=xpto indexfile=whatevername_you_want
> Peek the whatevername_you_want.sql file and you migh't get a
> surprise.
> To get indexes just "grep -v -i 'rem'
> whatevername_you_want.sql'>to_your_index_one.sql
> To get the entire file just do a «sed '/ REM //g'
> whatevername_you_want.sql>new_file.sql» or awk it.
> Simple.
>
> Regards
>
> Jorge Meirim
>
> Steven Hauser escreveu na mensagem <7283ta$8p1$1_at_garnet.tc.umn.edu>...
>
> >URL: http://www.tc.umn.edu/~hause011/
> >
> >Script is ugly_DDL.ksh, extracts sql from export files.
> >
> >HEH.
> >
> >--
> >
> >---------------------------------------------------------
> >Steven Hauser, hause011_at_tc.umn.edu
> >Phone: (612)626-7135
> >Fax: (612)625-6853
> >---------------------------------------------------------

   The script below might help. Now I think about it, it's kinda overkill compared to Jorge's more efficient solution, but I did this when I was on NT (no grep, sed on NT). I've also used it in a unix environment. Only thing I changed was the utl_file_dir refrence. I also have one for tablespaces and indexes if anybody wants to modify for there own use.

If you can see any improvements, let me know as well.

mkb

/********************************************************************
** Purpose: Get table ddl from db and write to OS file
** Inputs:  None
** Returns: Nothing
** Created: 02/24/1998 By: MKB

** Version Info: PL/SQL v2.x Oracle 7.x (Oracle 8.x not tested)

********************************************************************/

CREATE OR REPLACE PROCEDURE sp_Table_DDL AS

/* File variables */
/*
** For UNIX change the line below to
** sFileDir VARCHAR2(50) := '/export/home/oracle'
** or whatever directory you want the file to be in.
**
** Make sure that the following line is in init.ora
** utl_file_dir = '/export/home/oracle/'
** or whatever directory you want the file to be in.

*/
sFileDir VARCHAR2(50) := 'd:\ora-dba';
sFileName VARCHAR2(30) := 'table.ddl';
v_FileHandle UTL_FILE.FILE_TYPE;

/* Local variables */
sCreatetable VARCHAR2(100) := '';
sPctfree VARCHAR2(20);
sPctused VARCHAR2(20);
sTablespace VARCHAR2(70);

sStorage1 VARCHAR2(100) := '';
sStorage2 VARCHAR2(100) := '';
sStorage3 VARCHAR2(100) := '';

sCol VARCHAR2(35) := '';
sData_Type VARCHAR2(15) := '';
sNullable VARCHAR2(8) := '';
sColString VARCHAR2(200) := '';

/* Loop Counters */
nCol_Count NUMBER := 0;
nLoop_Count NUMBER := 0;

BEGIN /* Open the file */
v_FileHandle := UTL_FILE.FOPEN(sFileDir,sFileName,'w');

/* Need SYS to grant select on dba_tables, dba_tab_columns to SYSTEM */ FOR recExp1 IN

    (select distinct owner, table_name, tablespace_name,

        pct_free, pct_used, initial_extent, next_extent,
        min_extents, max_extents, pct_increase
        from dba_tables
        where owner not in ('SYS', 'SYSTEM'))

LOOP     sCreatetable := 'CREATE TABLE ' || recExp1.owner ||'.'|| recExp1.table_name;

    sPctfree := 'PCTFREE ' || TO_CHAR(recExp1.pct_free);     sPctfree := LPAD(sPctfree, LENGTH(sPctfree) + 2);

    sPctused := 'PCTUSED ' || TO_CHAR(recExp1.pct_used);     sPctused := LPAD(sPctused, LENGTH(sPctused) + 2);

    sTablespace := 'TABLESPACE ' || recExp1.tablespace_name;     sTablespace := LPAD(sTablespace, LENGTH(sTablespace) + 2);

    sStorage1 := 'STORAGE (INITIAL ' || TO_CHAR(recExp1.initial_extent/1024)

                    || 'K NEXT '  || TO_CHAR(recExp1.next_extent/1024)

|| 'K';

    sStorage1 := LPAD(sStorage1, LENGTH(sStorage1) + 2);

    sStorage2 := 'MINEXTENTS ' || TO_CHAR(recExp1.min_extents)

                    || ' MAXEXTENTS ' || TO_CHAR(recExp1.max_extents);

    sStorage2 := LPAD(sStorage2, LENGTH(sStorage2) + 11);

    sStorage3 := 'PCTINCREASE ' || TO_CHAR(recExp1.pct_increase)||');';     sStorage3 := LPAD(sStorage3, LENGTH(sStorage3) + 11);

    UTL_FILE.PUT(v_FileHandle, sCreatetable);     UTL_FILE.NEW_LINE(v_FileHandle);

    nLoop_Count := 1;

    SELECT COUNT(*) INTO nCol_Count FROM DBA_TAB_COLUMNS

        WHERE TABLE_NAME = recExp1.table_name;

    FOR recExp2 IN

        (select column_name, data_type, data_length,
            data_precision, nullable, data_scale
        from dba_tab_columns
        where owner not in ('SYS', 'SYSTEM')
        and table_name = recExp1.table_name)

    LOOP

        sCol := recExp2.column_name ||' ';
        IF (recExp2.data_type = 'VARCHAR2' OR recExp2.data_type =
'CHAR') THEN
            /* VARCHAR2 and CHAR */
            sData_Type := recExp2.data_type || '(' ||
TO_CHAR(recExp2.data_length) ||') ';
        ELSIF recExp2.data_type = 'DATE' THEN
            /* DATE */
            sData_Type := 'DATE ';
        ELSIF recExp2.data_type = 'NUMBER' THEN
            /* NUMBER */
            IF recExp2.data_scale = 0 OR recExp2.data_scale IS NULL THEN

                sData_Type := 'NUMBER ';
            ELSE
                sData_Type := 'NUMBER'||'('||
TO_CHAR(recExp2.data_precision)
                                  ||','||
TO_CHAR(recExp2.data_scale)||') ';
               --sp_putlongline(sData_Type);
            END IF;
        ELSIF recExp2.data_type = 'LONG' THEN
            /* LONG */
            sData_Type := 'LONG ';
        ELSIF recExp2.data_type = 'RAW' THEN
            /* RAW */
            sData_Type := 'RAW('|| TO_CHAR(recExp2.data_length) ||') ';
        ELSIF recExp2.data_type = 'LONG RAW' THEN
            /* LONG RAW */
            sData_Type := 'LONG RAW ';
        ELSIF recExp2.data_type = 'ROWID' THEN
            /* ROWID */
            sData_Type := 'ROWID ';
        ELSIF recExp2.data_type = 'MLSLABEL' THEN
            /* MLSLABEL */
            sData_Type := 'MLSLABEL ';
        END IF;

        IF recExp2.nullable <> 'Y' THEN
            sNullable := 'NOT NULL';
        ELSE
            sNullable := '';
        END IF;

        IF nLoop_Count = 1 THEN
            IF nCol_Count > 1 THEN
                sColString := '(
'||RPAD(sCol,30)||LTRIM(RTRIM(RPAD(sData_Type,18)||sNullable)) ||',';
                sColString := LPAD(sColString, LENGTH(sColString) + 4);
                UTL_FILE.PUT(v_FileHandle, sColString);
                UTL_FILE.NEW_LINE(v_FileHandle);
            ELSE
                sColString := '(
'||RPAD(sCol,30)||LTRIM(RTRIM(RPAD(sData_Type,18)||sNullable)) ||')';
                sColString := LPAD(sColString, LENGTH(sColString) + 4);
                UTL_FILE.PUT(v_FileHandle, sColString);
                UTL_FILE.NEW_LINE(v_FileHandle);
            END IF;
        ELSIF nLoop_Count <> nCol_Count THEN
            sColString :=
RPAD(sCol,30)||LTRIM(RTRIM(RPAD(sData_Type,18)||sNullable)) ||',';
            sColString := LPAD(sColString, LENGTH(sColString) + 6);
            UTL_FILE.PUT(v_FileHandle, sColString);
            UTL_FILE.NEW_LINE(v_FileHandle);
        ELSE
            sColString :=RPAD(sCol, 30)
||LTRIM(RTRIM(RPAD(sData_Type,18)||sNullable))||' )';
            sColString := LPAD(sColString, LENGTH(sColString) + 6);
            UTL_FILE.PUT(v_FileHandle, sColString);
            UTL_FILE.NEW_LINE(v_FileHandle);
        END IF;
        nLoop_Count := nLoop_Count + 1;

    END LOOP;     nLoop_Count := 0;

    UTL_FILE.PUT(v_FileHandle, sPctfree);
    UTL_FILE.NEW_LINE(v_FileHandle);
    UTL_FILE.PUT(v_FileHandle, sPctused);
    UTL_FILE.NEW_LINE(v_FileHandle);
    UTL_FILE.PUT(v_FileHandle, sTablespace);
    UTL_FILE.NEW_LINE(v_FileHandle);
    UTL_FILE.PUT(v_FileHandle, sStorage1);
    UTL_FILE.NEW_LINE(v_FileHandle);
    UTL_FILE.PUT(v_FileHandle, sStorage2);
    UTL_FILE.NEW_LINE(v_FileHandle);
    UTL_FILE.PUT(v_FileHandle, sStorage3);
    UTL_FILE.NEW_LINE(v_FileHandle);
    UTL_FILE.NEW_LINE(v_FileHandle);
    UTL_FILE.NEW_LINE(v_FileHandle);

END LOOP; UTL_FILE.FCLOSE(v_FileHandle);

DBMS_OUTPUT.PUT_LINE('Done table.ddl'); --sp_putlongline('Done this is aswell...');

END;
/ Received on Wed Nov 11 1998 - 15:48:09 CST