Xref: alice comp.databases.oracle.server:33560 Path: alice!news-feed.fnsi.net!newspump.monmouth.com!newspeer.monmouth.com!newsfeed.cwix.com!204.71.1.48!spamkiller1.cwix.com!news.cwix.com!not-for-mail Message-ID: <364A0619.C6B9ED84@mci.com> From: "M. Bhatti" X-Mailer: Mozilla 4.01 [en] (WinNT; I) MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: Script to generate Table DDL X-Priority: 3 (Normal) References: <721vg7$cie@bgtnsc02.worldnet.att.net> <7283ta$8p1$1@garnet.tc.umn.edu> <72ahts$lak@brown.telepac.pt> Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Lines: 243 Date: Wed, 11 Nov 1998 16:48:09 -0500 X-Trace: news.cwix.com 910820693 166.36.174.158 (Wed, 11 Nov 1998 21:44:53 GMT) NNTP-Posting-Date: Wed, 11 Nov 1998 21:44:53 GMT Organization: CWIX 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@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@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; /