REM ===========================================================
REM
REM Custom-delimited Export Creator
REM for Oracle7 / SQL*Plus
REM
REM Created 11/15/96
REM by Ken Shirey
REM Oracle Database Administrator
REM Commerical Data Systems
REM email: commdata_at_phoenix.net
REM
REM This program may be freely copied, modified,
REM and distributed, provided that it retains this
REM header. Use at your own risk.
REM
REM Usage: @tabexp <owner> <table name> <ASCII Character Code>
REM EX: @tabexp SCOTT EMP 44
REM
REM caveats:
REM No NLS translation is specified. System defaults apply.
REM Date is formatted as system default.
REM Hull Crush Depth is 1000 characters.
REM LONG columns will cause errors, so they are not exported.
REM Delimiter characters in the text will be converted to a ~.
REM ===========================================================
set echo off
set feedback off
set pages 0
set lines 100
set verify off
prompt Creating command file for table: &1 . &2 ...
set termout off
spool cmd.sql
REM
REM start the statement...
REM
select 'Select rtrim(' from dual;
REM
REM We're converting everything to CHAR
REM Watch for extra spaces at the end of CHAR columns
REM Convert any embedded delimiter characters to tildes (~)
REM
select decode(data_type ,'NUMBER','to_char(' ,'DATE', 'to_char('
,'CHAR','rtrim(translate(' ,'VARCHAR2', 'rtrim(translate(' ,NULL
) || column_name || decode(data_type ,'NUMBER', ') || chr(&3) || '
,'DATE', ') || chr(&3) || '
,'CHAR' , ',chr(&3),chr(126) )) || chr(&3) ||'
,'VARCHAR2', ',chr(&3),chr(126) )) || chr(&3) ||'
,' || chr(&3) || ' ) from all_tab_columns
where table_name = upper('&2') and owner = upper('&1')
and data_type not in ('LONG','LONG RAW');
REM
REM Remember that we concatenated an extra "||" on the end
REM of the last column. Can't leave it hanging, so put
REM another delimiter on the end to fix the statement's syntax.
REM
select 'chr(&3))' from dual;
REM
REM Now, tack on the predicate...
REM
select 'from &1' || '.' || '&2;' from dual; spool off
REM Done with extract statement. Wanna see it?
set termout on
get cmd.sql
prompt prompt Hit any key to start export, or <ctrl>-c to cancel
accept keypress
prompt
prompt Now exporting table: owner = &1
prompt table name = &2 ,
prompt delimited by ASCII(&3)
set termout off
set lines 1000
REM
REM Let's export some Data!!
REM
spool &2
@cmd.sql
spool off
host rm cmd.sql
set termout on
prompt "Export Completed."
REM
REM End of file.
Received on Wed Apr 29 1998 - 00:00:00 CDT
