Re: export dumpfile -> sqlload conversion ?

From: Yassir Khogaly <yassir_at_khogaly.freeserve.co.uk>
Date: Sat, 31 Oct 1998 20:29:24 -0000
Message-ID: <71frvp$io8$1@newsreader1.core.theplanet.net>

SET ECHO off

REM ------------------------------------------------------------------------

--
REM REQUIREMENTS:
REM SELECT on the given table(s)

REM ------------------------------------------------------------------------

--
REM PURPOSE:
REM Generates a sql*plus script to unload a table to a file and a REM SQL*Loader script to reload the same data. Intent is to create REM a faster alternative to export/import.

REM ------------------------------------------------------------------------

---

set tab off
set heading off heading off feedback off echo off verify off space 1 pagesize
0 linesize 120
accept owner prompt 'What schema owns the table to be unloaded? '
accept table_name prompt 'What table is to be unloaded? ' accept default_precision prompt 'What TOTAL number of digits should be reserved for numbers without defined precision? ' accept default_scale prompt 'What number of DECIMAL digits should be reserved for numbers without defined scale? '

Generate the unload script
spool unload_fixed2.sql select 'SET HEADING OFF FEEDBACK OFF ECHO OFF VERIFY OFF SPACE 0 PAGESIZE 0 TERMOUT OFF' from dual
/

--Calculate the sum of all output field lengths and set the output record size
select 'SET LINESIZE '

       || (sum(decode(data_type,
                      'CHAR',data_length,
                      'VARCHAR',data_length,
    'VARCHAR2',data_length,
                      'DATE',14,
   'NUMBER',decode(data_precision,
                                   '',&default_precision+2,

greatest(data_precision-data_scale,1)+decode(data_scale,0,0,1)+data_scale)+1 ,

                      'FLOAT',&default_precision+2,
           data_length)))

from dba_tab_columns
where owner=upper('&&owner')
and table_name=upper('&&table_name')
/

Generate an appropriate SQL*Plus COLUMN command to control formatting of each output field select 'COLUMN ' || rpad('"'||column_name||'"',32) || ' FORMAT ' || rpad(decode(data_type, 'CHAR','A'||data_length, 'VARCHAR2','A'||data_length, 'VARCHAR','A'||data_length, 'DATE','A14', 'NUMBER',decode(data_precision, '', rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&default_scal e,' 9'),

rpad('0',greatest(data_precision-data_scale,1),'9') || decode(data_scale,0,'','.')

||

decode(data_scale,0,'',rpad('9',data_scale,'9'))),

'FLOAT',rpad('0',&default_precision-&default_scale,'9')||'.'||rpad('9',&defa ult
_scale,'9'),

                 'ERROR'),40)
       || ' HEADING ''X'''

from dba_tab_columns
where owner=upper('&&owner')
and table_name=upper('&&table_name') order by column_id
/

Generate the actual SELECT statement to unload table data select 'SPOOL /tmp/&&owner..&&table_name..DAT' from dual
/
column var1 noprint column var2 noprint select 'a' var1, 0 var2, 'SELECT ' from dual union select 'b', column_id, decode(column_id, 1, ' ', ' , ') || decode(data_type,'DATE','to_char('||'"'||column_name||'"'||',''YYYYMMDDHH24M ISS '') '||'"'||column_name||'"' , '"'||column_name||'"') from dba_tab_columns where owner=upper('&&owner') and table_name=upper('&&table_name') union select 'c', 0, 'FROM &&owner..&&table_name' from dual union select 'd', 0, ';' from dual order by 1,2
/
select 'SPOOL OFF' from dual
/
select 'SET TERMOUT ON' from dual
/

spool off

-
--  Generate the SQL*Loader control file
----------------------------------------------------------------------------
-

set lines 120 pages 0
spool &&owner..&&table_name..CTL
select 'a' var1, 0 var2, 'OPTIONS(DIRECT=TRUE)'   from dual
union
select 'b', 0, 'LOAD DATA'
  from dual
union
select 'c', 0, 'INFILE ''/tmp/&&owner..&&table_name..DAT'''   from dual
union
select 'd', 0, 'BADFILE &&owner..&&table_name..BAD'   from dual
union
select 'e', 0, 'DISCARDFILE &&owner..&&table_name..DSC'   from dual
union
select 'f', 0, 'DISCARDMAX 999'
  from dual
union
select 'm', 0, 'INTO TABLE &&owner..&&table_name'   from dual
union
select 'n', column_id,
rpad(decode(column_id,1,'(',',')||'"'||column_name||'"',31)

                       || decode(data_type,
 'CHAR','CHAR('||data_length||')',
                                 'VARCHAR','CHAR('||data_length||')',
                             'VARCHAR2','CHAR('||data_length||')',
                    'DATE','DATE(14) "YYYYMMDDHH24MISS"',

'NUMBER','DECIMAL
EXTERNAL('||decode(data_precision,

'',&default_precision+2,

greatest(data_precision-data_scale,1)+decode(data_scale,0,0,1)+data_scale+1)

              ||')',
                                 'FLOAT','DECIMAL

EXTERNAL('||to_char(&default_precision+2)||')', 'ERROR--'||data_type)

                       || ' NULLIF ("' ||column_name||'" = BLANKS)'

  from dba_tab_columns
where owner = upper('&&owner')
   and table_name = upper('&&table_name') union
select 'z', 0, ')'
  from dual
order by 1, 2
/

spool off

-
--  Cleanup
----------------------------------------------------------------------------
-

clear column
clear break
clear compute
undef owner
undef table_name
undef default_precision
undef default_scale

Ronald wrote in message <01HW.B25E7C270009277202447DC0_at_news.wxs.nl>...

>Hi there !
>
>we have exp direct=y that's fast
>we have sqload direct=y that's fast too
>
>we don't have imp direct=y that's a pity.
>
>Has anybody tried to convert the data in the export.dmp file to a format
>suitable for sqlload ? I think it could save lots of time since sqlload can
>read tables in direct path and in parallel.
>
>What format is the data in export.dmp in ? When I know that, it should not

>too difficult to write a little program to create the control-files and
>datafiles.
>
>Ideas ? comments ? sources ?
>--
>Ronald
>
>---------------------------------------------------------------------------
>mailto:ronr_at_wxs.nl
>http://home.wxs.nl/~ronr/RonR.html (last update: june 8th, 1998)
>http://www.maccentral.com/news/9804/migration.shtml (forward migration

home)

>
>I think so I mac !
>

begin 666 Yassir Khogaly.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..DMH;V=A;'D[66%S<VER#0I& M3CI987-S:7(@2VAO9V%L>0T*5$E43$4Z4V5N:6]R($]R86-L92!$0D$-"E1% M3#M(3TU%.U9/24-%.BLT-"@P*3$X,2 T-C T,#0R#0I414P[0T5,3#M63TE# M13HK-#0T,3$Y,#8W-S8-"D%$4CM(3TU%.CL[.SM+96YT.SM%;F=L86YD#0I, M04)%3#M(3TU%.T5.0T]$24Y'/5%53U1%1"U04DE.5$%"3$4Z2V5N=#TP1#TP M045N9VQA;F0-"E523#IH='1P.B\O=W=W+FMH;V=A;'DN9G)E97-E<G9E+F-O M+G5K#0I54DPZ:'1T<#HO+W=W=RYA965U+F]R9RYU:PT*14U!24P[4%)%1CM) M3E1%4DY%5#IY87-S:7) :VAO9V%L>2YF<F5E<V5R=F4N8V\N=6L-"E)%5CHQ <.3DX,3 S,50R,#(Y,C1:#0I%3D0Z5D-!4D0-"@`` `
end Received on Sat Oct 31 1998 - 14:29:24 CST