I need script which generates the script for existing tables....
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: I need script which generates the script for existing tables....

Hybrid View

  1. #1
    Join Date
    Jul 2001
    Location
    Bangalore
    Posts
    29

    Angry

    Hi,

    I want script to generate the script for existing tables which I created from wizard. If anybody have this script, please let me know. Please help me.


    thankx
    mustak

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello

    set verify off
    set feedback off
    set echo off long 5000
    set pagesize 0 head off lines 1000
    set termout on trimspool on
    set serveroutput on size 1000000

    Prompt
    Prompt This script generates another script that will include all the CREATE
    Prompt TABLE statements for those in the database (except for the SYS user).
    Prompt (NB ensure screen buffer set to 1000 to avoid truncating long lines)
    Prompt The dbms_output package must be available. Script runs for user accessible tables
    Prompt only (change USER_/ALL_ to DBA_ if necessary)
    Promp

    clear columns
    Accept Table_name Prompt 'Enter Table name to reverse engineer: '
    Accept owner Prompt 'Enter owmer of Tables to reverse engineer: '
    Accept add_owner Prompt 'Enter Y to prefix schema/user name to tablename, N if not: '
    Accept storage Prompt 'Enter Y to add storage clause, N if not: '

    rem
    rem ***************************************************************************
    rem ***** Set the following usesegs flag to Y to use the total number of bytes
    rem ***** that the segment is currently using (instead of the table's
    rem ***** originally-specified INITIAL_EXTENT value) as the value to use for
    rem ***** the INITIAL_EXTENT in the script file that this script creates, so
    rem ***** that the table will be recreated in a single extent. (The NEXT_EXTENT
    rem ***** will be limited to no more than the INITIAL_EXTENT value.)
    rem ***** (Set to 'N' to use the originally-specified INITIAL_EXTENT value).
    rem ***************************************************************************
    rem
    def usesegs='N'

    Set termout off
    Spool RE.tab

    declare
    cursor tab_cursor is select
    upper(owner),
    upper(table_name),
    pct_free,
    pct_used,
    ini_trans,
    max_trans,
    tablespace_name,
    initial_extent,
    next_extent,
    min_extents,
    max_extents,
    freelists,
    freelist_groups,
    pct_increase
    from all_tables
    where owner != 'SYS'
    AND owner LIKE UPPER('&&owner')
    AND table_name LIKE UPPER('&&Table_name')
    order by owner, table_name;
    cursor segments_cursor (s_own VARCHAR2, s_tab VARCHAR2) is select
    bytes
    from USER_segments
    where segment_name = s_tab and --owner = s_own and
    segment_type = 'TABLE';
    cursor col_cursor (c_own VARCHAR2, c_tab VARCHAR2) is select
    owner,
    upper(column_name),
    upper(data_type),
    data_length,
    data_precision,
    data_scale,
    nullable,
    default_length,
    data_default,
    column_id
    from all_tab_columns
    where table_name = c_tab and owner = c_own
    order by column_id;
    cursor pk_cursor (c_own VARCHAR2, c_tab VARCHAR2) is select
    owner,
    upper(constraint_name) constr_name,
    DECODE(constraint_type,'C','CHECK','P','PRIMARY KEY','R','FOREIGN KEY',constraint_type) constr_type,
    search_condition search_cond,
    R_owner,
    R_constraint_name
    from all_constraints
    where table_name = c_tab and owner = c_own
    order by constraint_name;
    cursor pkcol_cursor (c_own VARCHAR2, c_tab VARCHAR2, c_pk varchar2) is select
    column_name
    from all_cons_COLUMNS
    where table_name = c_tab and owner = c_own and constraint_name = c_pk
    order by position;

    cursor CU_pk_tabl_name (c_own VARCHAR2, c_PK VARCHAR2) is select
    table_name
    from all_constraints
    where constraint_name = c_pk and owner = c_own;


    L_storage CHAR(1) DEFAULT '&&storage';
    L_add_owner CHAR(1) DEFAULT '&&add_owner';
    lv_constr_name all_constraints.constraint_name%TYPE;
    lv_R_constr_name all_constraints.R_constraint_name%TYPE;
    lv_R_owner all_constraints.R_owner%TYPE;
    lv_constr_type VARCHAR2(100);
    lv_search_cond VARCHAR2(32000);
    lv_owner all_tables.owner%TYPE;
    lv_table_name all_tables.table_name%TYPE;
    lv_r_tabl_name all_tables.table_name%TYPE;
    lv_pct_free all_tables.pct_free%TYPE;
    lv_pct_used all_tables.pct_used%TYPE;
    lv_ini_trans all_tables.ini_trans%TYPE;
    lv_max_trans all_tables.max_trans%TYPE;
    lv_tablespace_name all_tables.tablespace_name%TYPE;
    lv_initial_extent all_tables.initial_extent%TYPE;
    lv_next_extent all_tables.next_extent%TYPE;
    lv_min_extents all_tables.min_extents%TYPE;
    lv_max_extents all_tables.max_extents%TYPE;
    lv_freelists all_tables.freelists%TYPE;
    lv_freelist_groups all_tables.freelist_groups%TYPE;
    lv_pct_increase all_tables.pct_increase%TYPE;
    segment_bytes USER_segments.bytes%TYPE;
    lv_column_name all_tab_columns.column_name%TYPE;
    lv_data_type all_tab_columns.data_type%TYPE;
    lv_data_length all_tab_columns.data_length%TYPE;
    lv_data_precision all_tab_columns.data_precision%TYPE;
    lv_data_scale all_tab_columns.data_scale%TYPE;
    lv_nullable all_tab_columns.nullable%TYPE;
    lv_default_length all_tab_columns.default_length%TYPE;
    lv_data_default all_tab_columns.data_default%TYPE;
    lv_column_id all_tab_columns.column_id%TYPE;
    lv_lineno number := 0;
    initial_extent_size varchar2(16);
    next_extent_size varchar2(16);
    a_lin varchar2(80);

    function wri(x_lin in varchar2, x_str in varchar2,
    x_force in number) return varchar2 is
    L_force NUMBER;
    begin
    --L_force:=1;
    --DBMS_OUTPUT.PUT_LINE('Line #'||lv_lineno);
    if length(x_lin) + length(x_str) > 80
    then
    lv_lineno := lv_lineno + 1;
    -- insert into tabl_temp values (lv_lineno, x_lin);
    DBMS_OUTPUT.PUT_LINE(x_lin);
    if x_force = 0
    then
    return x_str;
    else
    lv_lineno := lv_lineno + 1;
    -- insert into tabl_temp values (lv_lineno, x_str);
    DBMS_OUTPUT.PUT_LINE(x_str);
    return '';
    end if;
    else
    if x_force = 0
    then
    return x_lin||x_str;
    else
    lv_lineno := lv_lineno + 1;
    -- insert into tabl_temp values (
    -- lv_lineno, x_lin||x_str);
    DBMS_OUTPUT.PUT_LINE(x_lin||x_str);
    return '';
    end if;
    end if;
    end wri;
    --
    PROCEDURE PR_get_constr_col (lv_owner VARCHAR2, lv_table_name VARCHAR2, lv_constr_name VARCHAR2)
    IS
    BEGIN
    open PKcol_cursor(lv_owner, lv_table_name, lv_constr_name);
    loop
    fetch pkcol_cursor into
    lv_column_name;
    exit when pkcol_cursor%notfound;
    a_lin := wri(a_lin,' '||RPAD(' ',LENGTH(lv_constr_type)+1)||lv_column_name,1);
    end loop;
    close pkcol_cursor;
    END PR_get_constr_col;
    --
    begin
    a_lin := '';
    open tab_cursor;
    loop
    fetch tab_cursor into
    lv_owner,
    lv_table_name,
    lv_pct_free,
    lv_pct_used,
    lv_ini_trans,
    lv_max_trans,
    lv_tablespace_name,
    lv_initial_extent,
    lv_next_extent,
    lv_min_extents,
    lv_max_extents,
    lv_freelists,
    lv_freelist_groups,
    lv_pct_increase;
    exit when tab_cursor%notfound;

    if '&&usesegs' = 'Y' then
    open segments_cursor (lv_owner, lv_table_name);
    fetch segments_cursor into segment_bytes;
    if segments_cursor%found then
    lv_initial_extent := segment_bytes;
    if lv_next_extent > lv_initial_extent then
    lv_next_extent := lv_initial_extent;
    end if;
    end if;
    close segments_cursor;
    end if;
    a_lin := wri(a_lin, 'create table ', 0);
    IF L_add_owner = 'Y' THEN
    a_lin := wri(a_lin, lv_owner || '.' || lv_table_name, 0);
    ELSE
    a_lin := wri(a_lin, lv_table_name, 0);
    END IF;
    a_lin := wri(a_lin, ' (', 1);
    if (to_char(lv_ini_trans) = '0') then
    lv_ini_trans := 1;
    end if;
    if (to_char(lv_max_trans) = '0') then
    lv_max_trans := 1;
    end if;
    open col_cursor(lv_owner, lv_table_name);
    loop
    fetch col_cursor into
    lv_owner,
    lv_column_name,
    lv_data_type,
    lv_data_length,
    lv_data_precision,
    lv_data_scale,
    lv_nullable,
    lv_default_length,
    lv_data_default,
    lv_column_id;
    exit when col_cursor%notfound;
    if lv_column_id <> 1 then
    a_lin := wri(a_lin, ' ,', 0);
    else
    a_lin := wri(a_lin, ' ', 0);
    end if;
    a_lin := wri(a_lin,
    LOWER(lv_column_name), 0);
    a_lin := wri(a_lin, ' ' || lv_data_type, 0);
    if lv_data_type = 'CHAR' or lv_data_type = 'VARCHAR2' or
    lv_data_type = 'RAW'
    then
    a_lin := wri(a_lin,
    '(' || lv_data_length || ')', 0);
    end if;
    if (lv_data_type = 'NUMBER' and
    nvl(lv_data_precision, 0) != 0) or
    lv_data_type = 'FLOAT'
    then
    if nvl(lv_data_scale, 0) = 0 then
    a_lin := wri(a_lin,
    '(' || lv_data_precision || ')',
    0);
    else
    a_lin := wri(a_lin,
    '(' || lv_data_precision ||
    ',' || lv_data_scale || ')', 0);
    end if;
    end if;
    if lv_default_length != 0 then
    if lv_default_length < 80 then
    a_lin := wri(a_lin, ' DEFAULT ', 0);
    a_lin := wri(a_lin, lv_data_default, 0);
    else
    dbms_output.put_line(
    'Skipping default clause on ' ||
    'column ' || lv_column_name);
    dbms_output.put_line(
    ' on table ' || lv_table_name);
    dbms_output.put_line(
    ' since length is ' ||
    to_char(lv_default_length));
    end if;
    end if;
    if lv_nullable = 'N' then
    a_lin := wri(a_lin, ' NOT NULL', 1);
    ELSE
    a_lin := wri(a_lin, ' NULL', 1);
    end if;
    end loop;
    close col_cursor;
    a_lin := wri(a_lin, ')', 1);
    a_lin := wri(a_lin, ' PCTFREE ' || to_char(lv_pct_free), 1);
    a_lin := wri(a_lin, ' PCTUSED ' || to_char(lv_pct_used), 1);
    a_lin := wri(a_lin, ' INITRANS ' || to_char(lv_ini_trans), 1);
    a_lin := wri(a_lin, ' MAXTRANS ' || to_char(lv_max_trans), 1);
    --
    IF L_storage = 'Y' THEN
    a_lin := wri(a_lin, ' TABLESPACE ' || lv_tablespace_name, 1);
    a_lin := wri(a_lin, ' STORAGE (', 1);
    /* Calculate extent sizes in Mbytes or Kbytes, if possible */
    if mod(lv_initial_extent, 1048576) = 0 then
    initial_extent_size :=
    to_char(lv_initial_extent / 1048576) || 'M';
    elsif mod(lv_initial_extent, 1024) = 0 then
    initial_extent_size :=
    to_char(lv_initial_extent / 1024) || 'K';
    else
    initial_extent_size := to_char(lv_initial_extent);
    end if;
    if mod(lv_next_extent, 1048576) = 0 then
    next_extent_size :=
    to_char(lv_next_extent / 1048576) || 'M';
    elsif mod(lv_next_extent, 1024) = 0 then
    next_extent_size :=
    to_char(lv_next_extent / 1024) || 'K';
    else
    next_extent_size := to_char(lv_next_extent);
    end if;
    a_lin := wri(a_lin, ' INITIAL ' || initial_extent_size, 1);
    a_lin := wri(a_lin, ' NEXT ' || next_extent_size, 1);
    a_lin := wri(a_lin, ' MINEXTENTS ' || to_char(lv_min_extents),
    1);
    a_lin := wri(a_lin, ' MAXEXTENTS ' || to_char(lv_max_extents),
    1);
    a_lin := wri(a_lin, ' PCTINCREASE ' || to_char(lv_pct_increase),
    1);
    a_lin := wri(a_lin, ' FREELISTS ' || to_char(lv_freelists), 0);
    a_lin := wri(a_lin, ' FREELIST GROUPS ' ||
    to_char(lv_freelist_groups), 1);
    a_lin := wri(a_lin, ' )', 1);
    END IF;
    a_lin := wri(a_lin, ' ;', 1); end loop;
    close tab_cursor;

    exception
    when others then
    rollback;
    raise_application_error(-20000,
    'Unexpected error on ' || lv_table_name ||
    ', ' || lv_column_name || ': ' ||
    to_char(SQLCODE) || ' - Aborting...');
    end;
    /

    Spool Off
    undef table_name
    undef owner

    Prompt
    Prompt You now have a script called RE.Tab to use as required
    Prompt

    set verify on
    set feedback on
    set pagesize 30 head on lines 100
    set termout on trimspool on
    set serveroutput off

    regards
    hrishy

  3. #3
    Join Date
    Feb 2001
    Posts
    66
    Hi Krishy,
    I just ran this script, but the constraints aren't in the output file.
    Did I miss something?

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello m_d_marian

    The script below can help you to create constraints as well.
    Hope this helps.

    set verify off
    set feedback off
    set echo off long 5000
    set pagesize 0 head off lines 1000
    set termout on trimspool on
    set serveroutput on size 1000000

    Prompt
    Prompt This script generates another script that will include all the CREATE
    Prompt CONSTRAINT statements for those in the database (except for the SYS user).
    Prompt (NB ensure screen buffer set to 1000 to avoid truncating long lines)
    Prompt The dbms_output package must be available. Script runs for user accessible tables
    Prompt only (change USER_/ALL_ to DBA_ if necessary)
    Promp

    clear columns
    Accept Table_name Prompt 'Enter Table name to reverse engineer constraints for: '
    Accept owner Prompt 'Enter owmer of Tables to reverse engineer constraints for: '
    Accept constraint_type Prompt 'Enter type of constraint to create for (P,U,R,C,%): '
    Accept add_owner Prompt 'Enter Y to prefix schema/user name to tablename, N if not: '
    Accept storage Prompt 'Enter Y to add storage clause, N if not: '

    set termout oFF
    Spool RE.Con

    declare
    cursor tab_cursor is select
    upper(t.owner),
    upper(t.table_name),
    t.pct_free,
    t.pct_used,
    t.ini_trans,
    t.max_trans,
    t.tablespace_name,
    t.initial_extent,
    t.next_extent,
    t.min_extents,
    t.max_extents,
    t.freelists,
    t.freelist_groups,
    t.pct_increase,
    --
    upper(c.constraint_name) constr_name,
    DECODE(constraint_type,'C','CHECK','P','PRIMARY KEY','R','FOREIGN KEY','U','UNIQUE',constraint_type) constr_type,
    c.search_condition search_cond,
    c.R_owner,
    c.R_constraint_name
    --
    from all_tables t,all_constraints c
    where t.owner != 'SYS'
    AND t.owner LIKE UPPER('&&owner')
    AND t.table_name LIKE UPPER('&&Table_name')
    AND t.table_name = c.table_name
    AND t.owner = c.owner
    AND c.constraint_type LIKE UPPER('&&constraint_type')
    order by DECODE(constraint_type,'P',1,'U',2,'R',3,'C',4,5),t.owner, t.table_name;

    cursor segments_cursor (s_own VARCHAR2, s_tab VARCHAR2) is select
    bytes
    from USER_segments
    where segment_name = s_tab and --owner = s_own and
    segment_type = 'TABLE';
    cursor pk_cursor (c_own VARCHAR2, c_tab VARCHAR2)
    is select
    owner,
    upper(constraint_name) constr_name,
    DECODE(constraint_type,'C','CHECK','P','PRIMARY KEY','R','FOREIGN KEY','U','UNIQUE',constraint_type) constr_type,
    search_condition search_cond,
    R_owner,
    R_constraint_name
    from all_constraints
    where table_name = c_tab and owner = c_own
    order by DECODE(constraint_type,'P',1,'U',2,'R',3,'C',4,5);
    cursor pkcol_cursor (c_own VARCHAR2, c_tab VARCHAR2, c_pk varchar2) is select
    column_name
    from all_cons_COLUMNS
    where table_name = c_tab and owner = c_own and constraint_name = c_pk
    order by position;

    cursor CU_pk_tabl_name (c_own VARCHAR2, c_PK VARCHAR2) is select
    table_name
    from all_constraints
    where constraint_name = c_pk and owner = c_own;

    CURSOR CU_ind (c_own VARCHAR2, c_tab VARCHAR2,c_ind VARCHAR2)
    IS
    SELECT owner,
    index_name,
    uniqueness,
    tablespace_name,
    ini_trans,
    max_trans,
    pct_free,
    pct_increase,
    initial_extent,
    next_extent
    FROM all_indexes
    WHERE owner = c_own
    AND table_name LIKE c_tab
    AND index_name LIKE c_ind
    ORDER BY 1,2
    ;
    R_ind CU_ind%ROWTYPE;

    L_space NUMBER(3);
    L_storage CHAR(1) DEFAULT '&&storage';
    L_add_owner CHAR(1) DEFAULT '&&add_owner';
    lv_constr_name all_constraints.constraint_name%TYPE;
    lv_R_constr_name all_constraints.R_constraint_name%TYPE;
    lv_R_owner all_constraints.R_owner%TYPE;
    lv_constr_type VARCHAR2(100);
    lv_search_cond VARCHAR2(32000);
    lv_sc VARCHAR2(32000);
    lv_owner all_tables.owner%TYPE;
    lv_table_name all_tables.table_name%TYPE;
    lv_r_tabl_name all_tables.table_name%TYPE;
    lv_pct_free all_tables.pct_free%TYPE;
    lv_pct_used all_tables.pct_used%TYPE;
    lv_ini_trans all_tables.ini_trans%TYPE;
    lv_max_trans all_tables.max_trans%TYPE;
    lv_tablespace_name all_tables.tablespace_name%TYPE;
    lv_initial_extent all_tables.initial_extent%TYPE;
    lv_next_extent all_tables.next_extent%TYPE;
    lv_min_extents all_tables.min_extents%TYPE;
    lv_max_extents all_tables.max_extents%TYPE;
    lv_freelists all_tables.freelists%TYPE;
    lv_freelist_groups all_tables.freelist_groups%TYPE;
    lv_pct_increase all_tables.pct_increase%TYPE;
    segment_bytes USER_segments.bytes%TYPE;
    lv_column_name all_tab_columns.column_name%TYPE;
    lv_data_type all_tab_columns.data_type%TYPE;
    lv_data_length all_tab_columns.data_length%TYPE;
    lv_data_precision all_tab_columns.data_precision%TYPE;
    lv_data_scale all_tab_columns.data_scale%TYPE;
    lv_nullable all_tab_columns.nullable%TYPE;
    lv_default_length all_tab_columns.default_length%TYPE;
    lv_data_default all_tab_columns.data_default%TYPE;
    lv_column_id all_tab_columns.column_id%TYPE;
    lv_lineno number := 0;
    initial_extent_size varchar2(16);
    next_extent_size varchar2(16);
    a_lin varchar2(2000);

    function wri(x_lin in varchar2, x_str in varchar2,
    x_force in number) return varchar2 is
    L_force NUMBER;
    begin
    if length(x_lin) + length(x_str) > 80
    then
    lv_lineno := lv_lineno + 1;
    -- insert into tabl_temp values (lv_lineno, x_lin);
    DBMS_OUTPUT.PUT_LINE(x_lin);
    if x_force = 0
    then
    return x_str;
    else
    lv_lineno := lv_lineno + 1;
    -- insert into tabl_temp values (lv_lineno, x_str);
    DBMS_OUTPUT.PUT_LINE(x_str);
    return '';
    end if;
    else
    if x_force = 0
    then
    return x_lin||x_str;
    else
    lv_lineno := lv_lineno + 1;
    -- insert into tabl_temp values (
    -- lv_lineno, x_lin||x_str);
    DBMS_OUTPUT.PUT_LINE(x_lin||x_str);
    return '';
    end if;
    end if;
    end wri;
    --
    PROCEDURE PR_get_constr_col (lv_owner VARCHAR2, lv_table_name VARCHAR2, lv_constr_name VARCHAR2,lv_constr_type VARCHAR2)
    IS
    L_comma CHAR(1) DEFAULT '';
    BEGIN
    a_lin := wri(a_lin,' '||RPAD(' ',LENGTH(lv_constr_type)+1)||'(',1);
    open PKcol_cursor(lv_owner, lv_table_name, lv_constr_name);
    loop
    fetch pkcol_cursor into
    lv_column_name;
    exit when pkcol_cursor%notfound;
    a_lin := wri(a_lin,' '||RPAD(' ',LENGTH(lv_constr_type)+1)||L_comma||lv_column_name,1);
    L_comma := ',';
    end loop;
    close pkcol_cursor;
    a_lin := wri(a_lin,' '||RPAD(' ',LENGTH(lv_constr_type)+1)||')',1);
    END PR_get_constr_col;
    --
    begin
    a_lin := '';
    open tab_cursor;
    loop
    fetch tab_cursor into
    lv_owner,
    lv_table_name,
    lv_pct_free,
    lv_pct_used,
    lv_ini_trans,
    lv_max_trans,
    lv_tablespace_name,
    lv_initial_extent,
    lv_next_extent,
    lv_min_extents,
    lv_max_extents,
    lv_freelists,
    lv_freelist_groups,
    lv_pct_increase,
    lv_constr_name,
    lv_constr_type,
    lv_search_cond,
    lv_R_owner,
    lv_R_constr_name;
    exit when tab_cursor%notfound;
    --
    --
    -- Ignore system constraints for NOT NULL
    --

    IF lv_constr_type='CHECK'
    AND lv_constr_name like 'SYS%'
    AND lv_search_cond like '%IS NOT NULL'
    THEN
    NULL;
    ELSE
    IF L_add_owner = 'Y' THEN
    a_lin := wri(a_lin,'ALTER TABLE '||lv_owner||'.'||lv_table_name||' ADD (',1);
    ELSE
    a_lin := wri(a_lin,'ALTER TABLE '||lv_table_name||' ADD (',1);
    END IF;
    IF lv_constr_type='CHECK'
    and lv_search_cond is NOT NULl THEN
    IF lv_constr_name LIKE 'SYS%' THEN
    a_lin := wri(a_lin,' CHECK',1);
    a_lin := wri(a_lin,'-- original system constraint name was '||lv_constr_name,1);
    ELSE
    a_lin := wri(a_lin,' CONSTRAINT '||lv_constr_name,1);
    a_lin := wri(a_lin,' '||lv_constr_type,1);
    END IF;
    a_lin := wri(a_lin,' (',1);
    --
    -- Cannot output lines longer than 80? chars so loop
    lv_sc := SUBSTR(lv_search_cond,1,80);
    WHILE LENGTH(lv_sc)>0
    LOOP
    L_space := INSTR(lv_sC,' ',-1);
    IF L_space != 0 THEN
    lv_sc := SUBSTR(lv_sc,1,L_space);
    END IF;
    IF LENGTH(RTRIM(lv_sc))>0 THEN
    a_lin := wri(a_lin,' '||RTRIM(lv_sc),1);
    END IF;
    lv_search_cond := SUBSTR(lv_search_cond,LENGTH(lv_sc)+1);
    lv_sc := SUBSTR(lv_search_cond,1,80);
    END LOOP;
    a_lin := wri(a_lin,' )',1);
    ELSE
    a_lin := wri(a_lin,' CONSTRAINT '||lv_constr_name,1);
    a_lin := wri(a_lin,' '||lv_constr_type,1);
    -- Get constraint columns
    PR_get_constr_col(lv_owner, lv_table_name, lv_constr_name,lv_constr_type);
    END IF;
    -- Get index details for PK or UK
    IF lv_constr_type='PRIMARY KEY'
    OR lv_constr_type='UNIQUE' THEN
    OPEN CU_ind (lv_owner,lv_table_name,lv_constr_name);
    FETCH CU_ind INTO R_ind;
    CLOSE CU_ind;
    a_lin := wri(a_lin,' USING INDEX',1);
    a_lin := wri(a_lin,' PCTFREE '||R_ind.pct_free,1);
    a_lin := wri(a_lin,' INITRANS '||R_ind.INI_TRANS,1);
    a_lin := wri(a_lin,' MAXTRANS '||R_ind.max_trans,1);
    IF L_storage = 'Y' THEN
    a_lin := wri(a_lin,' TABLESPACE '||R_ind.tablespace_name,1);
    END IF;
    END IF;
    -- Get other end of FK (reference tablename and columns)
    IF lv_constr_type='FOREIGN KEY' THEN
    -- a_lin := wri(a_lin,' '||RPAD(' ',LENGTH(lv_constr_type)+1)||')',1);
    OPEN CU_pk_tabl_name (lv_r_owner,lv_r_constr_name);
    FETCH CU_pk_tabl_name INTO lv_r_tabl_name;
    IF L_add_owner = 'Y' THEN
    a_lin := wri(a_lin,' REFERENCES '||lv_r_owner||'.'||lv_r_tabl_name,1);
    ELSE
    a_lin := wri(a_lin,' REFERENCES '||lv_r_tabl_name,1);
    END IF;
    -- a_lin := wri(a_lin,' '||RPAD(' ',LENGTH(lv_constr_type)+1)||'(',1);
    CLOSE CU_pk_tabl_name;
    PR_get_constr_col(lv_owner, lv_R_tabl_name, lv_r_constr_name,lv_constr_type);
    END IF;
    -- Get index/storage stuff here
    a_lin := wri(a_lin,')',1);
    a_lin := wri(a_lin,'/',1);
    -- a_lin := wri(a_lin,'--',1);
    END IF;

    end loop;
    close tab_cursor;

    exception
    when others then
    rollback;
    raise_application_error(-20000,
    'Unexpected error on ' || lv_table_name ||
    ', ' || lv_column_name || ': ' ||
    to_char(SQLCODE) || ' - Aborting...scl='||LENGTH(lv_search_cond));
    end;
    /

    Spool Off
    undef table_name
    undef owner

    set verify on
    set feedback on
    set pagesize 30 head on lines 100
    set termout on trimspool on
    set serveroutput off

    Prompt
    Prompt You now have a script called RE.Con to use as required
    Prompt

    regards
    hrishy

  5. #5
    Join Date
    Jan 2001
    Posts
    153
    i would suggest to use TOAD or SQL NAv for generation of TABLE SCRIPTS and source code scripts !! everything is just an click away..
    Vijay.s

  6. #6
    Join Date
    Oct 2000
    Posts
    467
    Hi,
    The best way could be to take an export .
    exp userid/pw file = exp.dmp full=y rows=n

    Cheers
    Vinit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width