EXPORTING ASCII FILES - FOR SERGE TAYLOR
Date: 5 Oct 93 09:12:58 EST
Message-ID: <1993Oct5.091258.1_at_mcvax2.d48.lilly.com>
From: 72072.2122_at_compuserve.com
Reply-To: 72072.2122_at_compuserve.com
chicago ,---------+ detroit
Tom Harleman | |
INOUG Committee Member |INdiana |
Paradigm Consulting, Inc. st. | Oracle |
11080 Willowmere Drive louis | Users |
Indianapolis, IN 46280 | Group|
USA | |
| _,+cincinnati
INTERNET: 72072.2122_at_compuserve.com / _,'
Compuserve: 72072,2122 /_,-'louisville
`
EXECUTE_TRIGGER('DISCLAIMER');
Utilities for Oracle
====================
Submitted by Joe Harleman
Applied Database Design, Inc.
Pensacola, Fl 32514
904-479-3416
Placed in the public domain 10-JUN-92 without restriction.
EXPORT AND IMPORT for ANY PLATFORM
Using these utilities you can export your data from Oracle using a
'where clause' into :
- a SQL*Plus .SQL file with data embedded in INSERT statements or
- a SQL*Loader .CTL file with data embedded (comments at the beginning of
the .CTL file explain how to use
SQL*Loader from VAX DCL to load
the .CTL back into the database).
- this utility will not work properly if the table as embedded characters
recognized by SQL*Plus (e.g. single quotes, ampersands)
EXPV5A.SQL V5 export to SQL EXPV5B.SQL " " " " temp file EXPV5C.SQL V5 export to CTL EXPV5D.SQL " " " " temp file EXPV6A.SQL V6 export to SQL EXPV6B.SQL " " " " temp file EXPV6C.SQL V6 export to CTL EXPV6D.SQL " " " " CODE.CTL an example of an table exported to a CTL file CODE.SQL an example of an table exported to a SQL file ----------------------------------- cut here ------------------------------ REM *************************************************************REM EXPV5A.SQL
REM - EXPORTS ORACLE V5 TABLE DATA TO SQL SCRIPT
REM *************************************************************REM
REM CREATE EXPORT/IMPORT SCRIPT
REM AUTHOR: JOE HARLEMAN
REM APPLIED DATABASE DESIGN, INC. REM 8640 JERNIGAN RD. #45 REM PENSACOLA, FL 32514 REM 904-479-3416
PROMPT
PROMPT EXPV5A.SQL CREATE EXPORT/IMPORT SCRIPT
PROMPT
PROMPT Note: use " to surround char variables in the where clause
PROMPT the where clause is mandatory (use "X" = "X" if all desired) PROMPT the word WHERE will be automatically addedPROMPT
ACCEPT TABLE_OWNER CHAR PROMPT 'TABLE OWNER ...(OPS$ACCTG) .......: ' ACCEPT TABLE_NAME CHAR PROMPT 'TABLE NAME ....(CUSTOMER).........: ' ACCEPT WHERE_CLAUSE CHAR PROMPT 'WHERE CLAUSE ..(CUST_ID = "ABC")..: ' ACCEPT EXPORT_FILE CHAR PROMPT 'EXPORT TO FILE (CUSTOMER.SQL).....: ' SET TERMOUT OFF SET ECHO OFF
SET PAGESIZE 0
SET HEADING OFF
SET LINESIZE 80
SET FEEDBACK OFF
SET VERIFY OFF
SET EMBEDDED ON
SET RECSEP OFF
SET SPACE 0 COLUMN X1 NEWLINE
COLUMN X2 NEWLINE
COLUMN X3 NEWLINE
COLUMN X4 NEWLINE
COLUMN X5 NEWLINE
COLUMN X6 NEWLINE
COLUMN X7 NEWLINE
COLUMN X8 NEWLINE
COLUMN X9 NEWLINE SPOOL EXPV5B.SQL SELECT
'REM*********************************************************' X1,
'REM EXPV5B.SQL' X2,
'REM CREATED BY EXPV5A.SQL' X3,
'REM*********************************************************' X4,
'REM' X5,
'SELECT '||''''||'INSERT INTO '||CREATOR||'.'||TNAME||' ('||''''||',' X6
FROM CATALOG
WHERE TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
/
SELECT ''''||CNAME||','||''''||',' CN
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
AND COLNO !=
(SELECT MAX(COLNO)
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER') )
ORDER BY COLNO
/
SELECT ''''||CNAME||''''||',' CN
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
AND COLNO =
(SELECT MAX(COLNO)
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER') )
ORDER BY COLNO
/
SELECT ''''||' ) VALUES ('||''''||',' X1
FROM CATALOG
WHERE TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
/
SELECT DECODE(COLTYPE,'DATE',''''||''''||''''||'''',
'CHAR',''''||''''||''''||'''',
NULL)||
DECODE(COLTYPE,'DATE','||',
'CHAR','||',
'NVL(TO_CHAR(')||
CNAME||
DECODE(COLTYPE,'DATE','||',
'CHAR','||',
'),'||''''||'NULL'||''''||')')||
DECODE(COLTYPE,'DATE',''''||''''||''''||'''',
'CHAR',''''||''''||''''||'''',
NULL)||
'||'||''''||','||''''||
',' CN
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME') AND CREATOR = UPPER('&&TABLE_OWNER')
AND COLNO !=
(SELECT MAX(COLNO)
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER') )
ORDER BY COLNO
/
SELECT DECODE(COLTYPE,'DATE',''''||''''||''''||'''',
'CHAR',''''||''''||''''||'''',
NULL)||
DECODE(COLTYPE,'DATE','||',
'CHAR','||',
'NVL(TO_CHAR(')||
CNAME||
DECODE(COLTYPE,'DATE','||',
'CHAR','||',
'),'||''''||'NULL'||''''||')')||
DECODE(COLTYPE,'DATE',''''||''''||''''||'''',
'CHAR',''''||''''||''''||'''',
NULL)||
' ' CN
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME') AND CREATOR = UPPER('&&TABLE_OWNER')
AND COLNO =
(SELECT MAX(COLNO)
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER') )
/
SELECT '||'||
''''||
');'||
'''' X1,
'FROM '||CREATOR||'.'||TNAME X2,
'WHERE '||TRANSLATE('&&WHERE_CLAUSE','"','''') X3,
'/' X4
FROM CATALOG
WHERE TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
/
SPOOL OFF SPOOL &&EXPORT_FILE START EXPV5B SPOOL OFF EXIT
----------------------------------- cut here ------------------------------
REM*********************************************************
REM EXPV5B.SQL
REM CREATED BY EXPV5A.SQL
REM*********************************************************
REM
SELECT 'INSERT INTO OPS$PMDBA.PRINTER (',
'PRINTER_CODE,',
'PRINTER_QUEUE',
' ) VALUES (',
NVL(TO_CHAR(PRINTER_CODE),'NULL')||',',REM EXPV5C.SQL
''''||PRINTER_QUEUE||''''
||');' FROM OPS$PMDBA.PRINTER WHERE 'X' = 'X' / ----------------------------------- cut here ------------------------------ REM *************************************************************
REM - EXPORTS ORACLE V5 TABLE DATA TO SQL*LOADER SCRIPT
REM ************************************************************* REM AUTHOR: JOE HARLEMAN REM APPLIED DATABASE DESIGN, INC. REM 8640 JERNIGAN RD. #45 REM PENSACOLA, FL 32514 REM 904-479-3416
PROMPT
PROMPT EXPV5C.SQL CREATE EXPORT/IMPORT SCRIPT FOR SQL*LOADER
PROMPT
PROMPT Note: use " to surround char variables in the where clause
PROMPT the where clause is mandatory (use "X" = "X" if all desired) PROMPT the word WHERE will be automatically addedPROMPT
ACCEPT TABLE_OWNER CHAR PROMPT 'TABLE OWNER ...(OPS$ACCTG) .......: ' ACCEPT TABLE_NAME CHAR PROMPT 'TABLE NAME ....(CUSTOMER).........: ' ACCEPT WHERE_CLAUSE CHAR PROMPT 'WHERE CLAUSE ..(CUST_ID = "ABC")..: ' ACCEPT EXPORT_FILE CHAR PROMPT 'EXPORT TO FILE (CUSTOMER.CTL).....: ' SET TERMOUT OFF SET ECHO OFF
SET PAGESIZE 0
SET LINESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET EMBEDDED ON
SET RECSEP OFF
SET SPACE 0 COLUMN X1 NEWLINE
COLUMN X2 NEWLINE
COLUMN X3 NEWLINE
COLUMN X4 NEWLINE
COLUMN X5 NEWLINE
COLUMN X6 NEWLINE
COLUMN X7 NEWLINE
COLUMN X8 NEWLINE
COLUMN X9 NEWLINE
COLUMN X10 NEWLINE
COLUMN X11 NEWLINE
COLUMN X12 NEWLINE
COLUMN X13 NEWLINE
COLUMN X14 NEWLINE
COLUMN X15 NEWLINE
COLUMN X16 NEWLINE
COLUMN X17 NEWLINE
COLUMN X18 NEWLINE
COLUMN X19 NEWLINE
COLUMN X20 NEWLINE SPOOL EXPV5D.SQL SELECT
'REM*********************************************************' X1,
'REM EXPV5D.SQL' X2,
'REM CREATED BY EXPV5C.SQL' X3,
'REM*********************************************************' X4,
'REM' X5,
'COL X1 NEWLINE' X6,
'COL X2 NEWLINE' X7,
'COL X3 NEWLINE' X8,
'COL X4 NEWLINE' X9,
'COL X5 NEWLINE' X10,
'COL X6 NEWLINE' X11,
'COL X7 NEWLINE' X12,
'COL X8 NEWLINE' X13,
'COL X9 NEWLINE' X14,
'COL X10 NEWLINE' X15,
' ' X16
FROM CATALOG
WHERE TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
/
SELECT
'SELECT '||''''||'-- '||TNAME||'.CTL'||''''||' X1,' X1,
''''||'LOAD DATA'||''''||' X2,' X2,
''''||'INFILE *' ||''''||' X3,' X3,
''''||'APPEND' ||''''||' X4,' X4,
''''||'CONTINUEIF THIS (1:1) != '||
''''||''''||'~'||''''||''''||
''''||' X5,' X5,
''''||'INTO TABLE '||TNAME||''''||' X6,' X6,
''''||'FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '||
''''||''''||'"'||''''||''''||
''''||' X7,' X7,
''''||'('||''''||' X8,' X8
FROM CATALOG
WHERE TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
/
SELECT ''''||CNAME||','||''''||',' CN
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
AND COLNO !=
(SELECT MAX(COLNO)
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER') )
ORDER BY COLNO
/
SELECT ''''||CNAME||''''||',' CN
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
AND COLNO =
(SELECT MAX(COLNO)
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER') )
ORDER BY COLNO
/
SELECT ''''||')'||''''||' X9,' X1,
''''||'BEGINDATA'||''''||' X10' X2,
'FROM DUAL' X3,
'/' X4,
' ' X5,
'SELECT' X6
FROM CATALOG
WHERE TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
/
SELECT ''''||' "'||''''||'||'||CNAME||'||'||''''||'",'||''''||',' CN
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
AND COLNO !=
(SELECT MAX(COLNO)
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER') )
ORDER BY COLNO
/
SELECT ''''||' "'||''''||'||'||CNAME||'||'||''''||'"'||''''||',' CN
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
AND COLNO =
(SELECT MAX(COLNO)
FROM COLUMNS
WHERE COLUMNS.TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER') )
/
COL X1 FOLD_AFTER
SELECT ''''||
'~'||
''''||' X1' X1,
'FROM '||CREATOR||'.'||TNAME X2,
'WHERE '||TRANSLATE('&&WHERE_CLAUSE','"','''') X3,
'/' X4
FROM CATALOG
WHERE TNAME = UPPER('&&TABLE_NAME')
AND CREATOR = UPPER('&&TABLE_OWNER')
/
SPOOL OFF
SPOOL &&EXPORT_FILE
START EXPV5D
SPOOL OFF
EXIT
EXIT
----------------------------------- cut here ------------------------------
REM*********************************************************
REM EXPV5D.SQL
REM CREATED BY EXPV5C.SQL
REM*********************************************************
REM
COL X1 NEWLINE
COL X2 NEWLINE
COL X3 NEWLINE
COL X4 NEWLINE
COL X5 NEWLINE
COL X6 NEWLINE
COL X7 NEWLINE
COL X8 NEWLINE
COL X9 NEWLINE
COL X10 NEWLINE
SELECT '-- TRANS_AUDIT.CTL' X1,
'LOAD DATA' X2,
'INFILE *' X3,
'APPEND' X4,
'CONTINUEIF THIS (1:1) != ''~''' X5,
'INTO TABLE TRANS_AUDIT' X6,
'FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY ''"''' X7,
'(' X8,
'TRACE_ID,',
'TRANS_CODE,',
'TRANS_DATE,',
'TRANS_TIME,',
'TRANS_ID,',
'GRADE,',
'PROD_ORDER_ID,',
'BATCH_ID,',
'CAUSE_CODE,',
'QTY_GROSS,',
'QTY_NET,',
'HOLD_ID,',
'PDL_PREDICTED,',
'PDL_ROW_PREDICTED,',
'PROD_ID,',
'PROD_ENDS,',
'COMMENTS_HEO,',
'COMMENTS_LCO,',
'STAT_CODE,',
'CONT_ID,',
'CONT_TYPE_CODE,',
'DEST_CODE,',
'MACH_ID,',
'QTY_CONS,',
'FROM_GRADE,',
'FROM_CAUSE_CODE,',
'USER_ID,',
'CUST_BATCH_ID,',
'ERR_SQL_CODE,',
'ERR_IO_STATUS,',
'ERR_PROGRAM_NAME,',
'ERR_PROGRAM_ROUTINE,',
'ERR_TRANS_CODE,',
'ERR_PROGRAM_STATUS,',
'UPDATE_FLAG',
')' X9,
'BEGINDATA' X10
FROM DUAL
/
SELECT
' "'||TRACE_ID||'",',
' "'||TRANS_CODE||'",',
' "'||TRANS_DATE||'",',
' "'||TRANS_TIME||'",',
' "'||TRANS_ID||'",',
' "'||GRADE||'",',
' "'||PROD_ORDER_ID||'",',
' "'||BATCH_ID||'",',
' "'||CAUSE_CODE||'",',
' "'||QTY_GROSS||'",',
' "'||QTY_NET||'",',
' "'||HOLD_ID||'",',
' "'||PDL_PREDICTED||'",',
' "'||PDL_ROW_PREDICTED||'",',
' "'||PROD_ID||'",',
' "'||PROD_ENDS||'",',
' "'||COMMENTS_HEO||'",',
' "'||COMMENTS_LCO||'",',
' "'||STAT_CODE||'",',
' "'||CONT_ID||'",',
' "'||CONT_TYPE_CODE||'",',
' "'||DEST_CODE||'",',
' "'||MACH_ID||'",',
' "'||QTY_CONS||'",',
' "'||FROM_GRADE||'",',
' "'||FROM_CAUSE_CODE||'",',
' "'||USER_ID||'",',
' "'||CUST_BATCH_ID||'",',
' "'||ERR_SQL_CODE||'",',
' "'||ERR_IO_STATUS||'",',
' "'||ERR_PROGRAM_NAME||'",',
' "'||ERR_PROGRAM_ROUTINE||'",',
' "'||ERR_TRANS_CODE||'",',
' "'||ERR_PROGRAM_STATUS||'",',
' "'||UPDATE_FLAG||'"',
'~' X1
FROM OPS$TYADA.TRANS_AUDIT
WHERE CONT_ID = '1381'
/
----------------------------------- cut here ------------------------------ REM *************************************************************REM EXPV6A.SQL
REM - EXPORTS ORACLE V6 TABLE DATA TO SQL SCRIPT
REM *************************************************************REM
REM CREATE EXPORT/IMPORT SCRIPT
REM AUTHOR: JOE HARLEMAN
REM APPLIED DATABASE DESIGN, INC. REM 8640 JERNIGAN RD. #45 REM PENSACOLA, FL 32514 REM 904-479-3416
PROMPT
PROMPT EXPV6A.SQL CREATE EXPORT/IMPORT SCRIPT
PROMPT
PROMPT Note: use " to surround char variables in the where clause
PROMPT the where clause is mandatory (use "X" = "X" if all desired) PROMPT the word WHERE will be automatically addedPROMPT
ACCEPT TABLE_OWNER CHAR PROMPT 'TABLE OWNER ...(OPS$ACCTG) .......: ' ACCEPT TABLE_NAME CHAR PROMPT 'TABLE NAME ....(CUSTOMER).........: ' ACCEPT WHERE_CLAUSE CHAR PROMPT 'WHERE CLAUSE ..(CUST_ID = "ABC")..: ' ACCEPT EXPORT_FILE CHAR PROMPT 'EXPORT TO FILE (CUSTOMER.SQL).....: ' SET TERMOUT OFF SET ECHO OFF
SET PAGESIZE 0
SET HEADING OFF
SET LINESIZE 80
SET FEEDBACK OFF
SET VERIFY OFF
SET EMBEDDED ON
SET RECSEP OFF
SET SPACE 0 COLUMN X1 NEWLINE
COLUMN X2 NEWLINE
COLUMN X3 NEWLINE
COLUMN X4 NEWLINE
COLUMN X5 NEWLINE
COLUMN X6 NEWLINE
COLUMN X7 NEWLINE
COLUMN X8 NEWLINE
COLUMN X9 NEWLINE
COLUMN X10 NEWLINE
COLUMN X11 NEWLINE
COLUMN X12 NEWLINE
COLUMN X13 NEWLINE
COLUMN X14 NEWLINE
COLUMN X15 NEWLINE
COLUMN X16 NEWLINE
COLUMN X17 NEWLINE
COLUMN X18 NEWLINE
COLUMN X19 NEWLINE
COLUMN X20 NEWLINE
COLUMN X21 NEWLINE SPOOL EXPV6B.SQL SELECT
'REM*********************************************************' X1,
'REM EXPV6B.SQL' X2,
'REM CREATED BY EXPV6A.SQL' X3,
'REM*********************************************************' X4,
'REM' X5,
'COL X1 NEWLINE' X6,
'COL X2 NEWLINE' X7,
'COL X3 NEWLINE' X8,
'COL X4 NEWLINE' X9,
'COL X5 NEWLINE' X10,
'COL X6 NEWLINE' X11,
'COL X7 NEWLINE' X12,
'COL X8 NEWLINE' X13,
'COL X9 NEWLINE' X14,
'COL X10 NEWLINE' X15,
' ' X16,
'SELECT '||''''||'REM '||'&&EXPORT_FILE'||''''||' X1,' X17,
''''||'REM '||''''||' X2' X18,
'FROM DUAL' X19,
'/' X20,
'SELECT '||''''||'INSERT INTO '||OWNER||'.'||TABLE_NAME||' ('||''''||',' X21
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
/
SELECT ''''||COLUMN_NAME||','||''''||',' CN
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID !=
(SELECT MAX(COLUMN_ID)
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER') )
ORDER BY COLUMN_ID
/
SELECT ''''||COLUMN_NAME||''''||',' CN
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID =
(SELECT MAX(COLUMN_ID)
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER') )
ORDER BY COLUMN_ID
/
SELECT ''''||' ) VALUES ('||''''||',' X1
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER')
/
SELECT DECODE(DATA_TYPE,'DATE',''''||''''||''''||'''',
'CHAR',''''||''''||''''||'''',
NULL)||
DECODE(DATA_TYPE,'DATE','||',
'CHAR','||',
'NVL(TO_CHAR(')||
COLUMN_NAME||
DECODE(DATA_TYPE,'DATE','||',
'CHAR','||',
'),'||''''||'NULL'||''''||')')||
DECODE(DATA_TYPE,'DATE',''''||''''||''''||'''',
'CHAR',''''||''''||''''||'''',
NULL)||
'||'||''''||','||''''||
',' CN
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID !=
(SELECT MAX(COLUMN_ID)
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER') )
ORDER BY COLUMN_ID
/
SELECT DECODE(DATA_TYPE,'DATE',''''||''''||''''||'''',
'CHAR',''''||''''||''''||'''',
NULL)||
DECODE(DATA_TYPE,'DATE','||',
'CHAR','||',
'NVL(TO_CHAR(')||
COLUMN_NAME||
DECODE(DATA_TYPE,'DATE','||',
'CHAR','||',
'),'||''''||'NULL'||''''||')')||
DECODE(DATA_TYPE,'DATE',''''||''''||''''||'''',
'CHAR',''''||''''||''''||'''',
NULL)||
' ' CN
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID =
(SELECT MAX(COLUMN_ID)
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER') )
/
SELECT '||'||
''''||
');'||
'''' X1,
'FROM '||OWNER||'.'||TABLE_NAME X2,
'WHERE '||TRANSLATE('&&WHERE_CLAUSE','"','''') X3,
'/' X4
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
/
SPOOL OFF SPOOL &&EXPORT_FILE START EXPV6B SPOOL OFF EXIT
----------------------------------- cut here ------------------------------
REM*********************************************************
REM EXPV6B.SQL
REM CREATED BY EXPV6A.SQL
REM*********************************************************
REM
COL X1 NEWLINE
COL X2 NEWLINE
COL X3 NEWLINE
COL X4 NEWLINE
COL X5 NEWLINE
COL X6 NEWLINE
COL X7 NEWLINE
COL X8 NEWLINE
COL X9 NEWLINE
COL X10 NEWLINE
SELECT 'REM DT.SQL' X1,
'REM ' X2
FROM DUAL
/
SELECT 'INSERT INTO OPS$RZX2122.DT (',
'LOT_ID,',
'DATE_CODE,',
'QC_DATE',
' ) VALUES (',
unknown command beginning "DECODE(DAT..." - rest of line ignored.
''''||LOT_ID||''''||',',
''''||DATE_CODE||''''||',',
NVL(TO_CHAR(QC_DATE'DD-MMM-YYYY HH:MI:SS')''''
||');'
FROM OPS$RZX2122.DT
WHERE 'X' = 'X'
/
----------------------------------- cut here ------------------------------
REM *************************************************************
REM EXPV6C.SQL
REM - EXPORTS ORACLE V6 TABLE DATA TO SQL*LOADER SCRIPT
REM *************************************************************REM
REM CREATE EXPORT/IMPORT SCRIPT
REM AUTHOR: JOE HARLEMAN
REM APPLIED DATABASE DESIGN, INC. REM 8640 JERNIGAN RD. #45 REM PENSACOLA, FL 32514 REM 904-479-3416
PROMPT
PROMPT EXPV6C.SQL CREATE EXPORT/IMPORT SCRIPT
PROMPT
PROMPT Note: use " to surround char variables in the where clause
PROMPT the where clause is mandatory (use "X" = "X" if all desired) PROMPT the word WHERE will be automatically addedPROMPT
ACCEPT TABLE_OWNER CHAR PROMPT 'TABLE OWNER ...(OPS$ACCTG) .......: ' ACCEPT TABLE_NAME CHAR PROMPT 'TABLE NAME ....(CUSTOMER).........: ' ACCEPT WHERE_CLAUSE CHAR PROMPT 'WHERE CLAUSE ..(CUST_ID = "ABC")..: ' ACCEPT EXPORT_FILE CHAR PROMPT 'EXPORT TO FILE (CUSTOMER.CTL).....: ' SET TERMOUT OFF SET ECHO OFF
SET PAGESIZE 0
SET LINESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET EMBEDDED ON
SET RECSEP OFF
SET SPACE 0 COLUMN X1 NEWLINE
COLUMN X2 NEWLINE
COLUMN X3 NEWLINE
COLUMN X4 NEWLINE
COLUMN X5 NEWLINE
COLUMN X6 NEWLINE
COLUMN X7 NEWLINE
COLUMN X8 NEWLINE
COLUMN X9 NEWLINE
COLUMN X10 NEWLINE
COLUMN X11 NEWLINE
COLUMN X12 NEWLINE
COLUMN X13 NEWLINE
COLUMN X14 NEWLINE
COLUMN X15 NEWLINE
COLUMN X16 NEWLINE
COLUMN X17 NEWLINE
COLUMN X18 NEWLINE
COLUMN X19 NEWLINE
COLUMN X20 NEWLINE
COLUMN X21 NEWLINE SPOOL EXPV6D.SQL SELECT
'REM*********************************************************' X1,
'REM EXPV6D.SQL' X2,
'REM CREATED BY EXPV6C.SQL' X3,
'REM*********************************************************' X4,
'REM' X5,
'COL X1 NEWLINE' X11,
'COL X2 NEWLINE' X12,
'COL X3 NEWLINE' X13,
'COL X4 NEWLINE' X14,
'COL X5 NEWLINE' X15,
'COL X6 NEWLINE' X16,
'COL X7 NEWLINE' X17,
'COL X8 NEWLINE' X18,
'COL X9 NEWLINE' X19,
'COL X10 NEWLINE' X20,
' ' X21
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
/
SELECT
'SELECT'||''''||'-- '||TABLE_NAME||'.CTL'||''''||' X1,' X1,
''''||'--'||''''||' X2,' X2,
''''||'-- $ SQLLOAD / CONTROL='||TABLE_NAME||'.CTL, -'||''''||' X3,' X3,
''''||'-- LOG='||TABLE_NAME||'.LOG, -'||''''||' X4,' X4,
''''||'-- BAD='||TABLE_NAME||'.BAD, -'||''''||' X5,' X5,
''''||'-- DISCARD='||TABLE_NAME||'.DSC'||''''||' X6,' X6,
''''||'--'||''''||' X7,' X7,
''''||'LOAD DATA'||''''||' X8,' X8,
''''||'INFILE *' ||''''||' X9,' X9,
''''||'APPEND' ||''''||' X10,' X10,
''''||'CONTINUEIF THIS (1:1) != '||
''''||''''||'~'||''''||''''||
''''||' X11,' X11,
''''||'INTO TABLE '||OWNER||'.'||TABLE_NAME||''''||' X12,' X12,
''''||'FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '||
''''||''''||'"'||''''||''''||
''''||' X13,' X13,
''''||'('||''''||' X14,' X14
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER')
/
SELECT ''''||COLUMN_NAME||','||''''||',' CN
FROM ALL_TAB_COLUMNS
WHERE ALL_TAB_COLUMNS.TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID !=
(SELECT MAX(COLUMN_ID)
FROM ALL_TAB_COLUMNS
WHERE ALL_TAB_COLUMNS.TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER') )
ORDER BY COLUMN_ID
/
SELECT ''''||COLUMN_NAME||''''||',' CN
FROM ALL_TAB_COLUMNS
WHERE ALL_TAB_COLUMNS.TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID =
(SELECT MAX(COLUMN_ID)
FROM ALL_TAB_COLUMNS
WHERE ALL_TAB_COLUMNS.TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER') )
ORDER BY COLUMN_ID
/
SELECT ''''||')'||''''||' X9,' X1,
''''||'BEGINDATA'||''''||' X10' X2,
'FROM DUAL' X3,
'/' X4,
' ' X5,
'SELECT' X6
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
/
SELECT ''''||' "'||''''||'||'||COLUMN_NAME||'||'||''''||'",'||''''||',' CN
FROM ALL_TAB_COLUMNS
WHERE ALL_TAB_COLUMNS.TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID !=
(SELECT MAX(COLUMN_ID)
FROM ALL_TAB_COLUMNS
WHERE ALL_TAB_COLUMNS.TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER') )
ORDER BY COLUMN_ID
/
SELECT ''''||' "'||''''||'||'||COLUMN_NAME||'||'||''''||'"'||''''||',' CN
FROM ALL_TAB_COLUMNS
WHERE ALL_TAB_COLUMNS.TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER')
AND COLUMN_ID =
(SELECT MAX(COLUMN_ID)
FROM ALL_TAB_COLUMNS
WHERE ALL_TAB_COLUMNS.TABLE_NAME = UPPER('&&TABLE_NAME')
AND OWNER = UPPER('&&TABLE_OWNER') )
/
COL X1 FOLD_AFTER
SELECT ''''||
'~'||
''''||' X1' X1,
'FROM '||OWNER||'.'||TABLE_NAME X2,
'WHERE '||TRANSLATE('&&WHERE_CLAUSE','"','''') X3,
'/' X4
FROM ALL_TABLES
WHERE TABLE_NAME = UPPER('&&TABLE_NAME') AND OWNER = UPPER('&&TABLE_OWNER')
/
SPOOL OFF SPOOL &&EXPORT_FILE START EXPV6D SPOOL OFF EXIT
----------------------------------- cut here ------------------------------
REM*********************************************************
REM EXPV6D.SQL
REM CREATED BY EXPV6C.SQL
REM*********************************************************
REM
COL X1 NEWLINE
COL X2 NEWLINE
COL X3 NEWLINE
COL X4 NEWLINE
COL X5 NEWLINE
COL X6 NEWLINE
COL X7 NEWLINE
COL X8 NEWLINE
COL X9 NEWLINE
COL X10 NEWLINE
SELECT'-- NATION.CTL' X1,
'--' X2,
'-- $ SQLLOAD / CONTROL=NATION.CTL, -' X3,
'-- LOG=NATION.LOG, -' X4,
'-- BAD=NATION.BAD, -' X5,
'-- DISCARD=NATION.DSC' X6,
'--' X7,
'LOAD DATA' X8,
'INFILE *' X9,
'APPEND' X10,
'CONTINUEIF THIS (1:1) != ''~''' X11,
'INTO TABLE CTSS.NATION' X12,
'FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY ''"''' X13,
'(' X14,
'CODE_PK,',
'NAME',
')' X9,
'BEGINDATA' X10
FROM DUAL
/
SELECT
' "'||CODE_PK||'",',
' "'||NAME||'"',
'~' X1
FROM CTSS.NATION
WHERE 'X'='X'
/
Received on Tue Oct 05 1993 - 15:12:58 CET