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

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