Re: Getting SQL Scripts
From: mark tomlinson <marktoml_at_gdi.net>
Date: 1998/01/16
Message-ID: <34bf8d80.700817_at_newshost.us.oracle.com>#1/1
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: _at_tabexp <owner> <table name> <ASCII Character Code> REM EX: _at_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 ~.
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('
) ||
column_name ||
decode(data_type ,'NUMBER', ') || 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
set lines 1000
REM
REM Let's export some Data!!
REM
spool &2
_at_cmd.sql
spool off
host rm cmd.sql
set termout on
prompt "Export Completed."
REM
REM End of file.
Received on Fri Jan 16 1998 - 00:00:00 CET
Date: 1998/01/16
Message-ID: <34bf8d80.700817_at_newshost.us.oracle.com>#1/1
On Fri, 16 Jan 1998 04:47:35 -0600, ShivKumar <vihs_at_rocketmail.com> wrote:
>
>Also is there any option to EXPORT data from tables to a TEXT file , so
>that I can use those text files to Load data again into the tables using
>SQL Loader ?
>
>Thanx for any help
Here is a script to do that part...
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: _at_tabexp <owner> <table name> <ASCII Character Code> REM EX: _at_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
_at_cmd.sql
spool off
host rm cmd.sql
set termout on
prompt "Export Completed."
REM
REM End of file.
Received on Fri Jan 16 1998 - 00:00:00 CET