|
Tip of the Week
Tip for Week of February 9, 2004
Generate Spool File Layout
This tip comes from
Gautam Arora, DBA, in NY, USA.
Use this simple SQL script--lay.sql--to generate the layout for the spooled file.
Run the script before spooling the table to get the layout.
Layout contains :
------------------
Column Name,
Data Type,
Length of Column,
Start Position of Column,
End Position of Column,
SQL*LOADER script,
R/L of Rec is "End Position" in Last Line
Paste the following script into c:\lay.sql, and run this script at the SQL prompt.
set linesize 400
set heading off
set pagesize 9999
select column_name
,data_type
,len
,start_pos
,end_pos
,(case when (data_type='CHAR' or data_type='VARCHAR2')
then ','||lower(column_name)||' '||'position('||start_pos||':'||end_pos||')'
when data_type='NUMBER'
then ','||lower(column_name)||' '||'position('||start_pos||':'||end_pos||')
integer external defaultif ('||st!art_pos||':'||end_pos||')=blanks'
when data_type='DATE'
then ','||lower(column_name)||' '||'position('||start_pos||':'||end_pos||') date nullif
('||start_pos||':'||end_pos||')=blanks' end) as scr
from (select column_name, data_type, len, ((end_pos-len)+1) as start_pos, end_pos
from (select column_name, data_Type, len, sum(len) over (order by column_id) as end_pos
from (select column_name
,column_id
,substr(data_type,1,10) data_Type
,decode(data_type,
'NUMBER',(case when length(column_name) <= 10
then 10
when length(column_name) > 10
then length(column_name)
! ! end)
,'CHAR',data_length
,'DATE',length(sysdate)) as len
from user_tab_cols
where table_name='&table_name'
order by column_id)));
Example: You want to spool scott.emp.
SQL>spool c:\emp.lay
SQL>@c:\lay.sql
Note the R/L (End Pos in Last Line) from emp.lay. Now you are ready to 'spool set' the following and change the line size with R/L:
SQL> set linesize 77
SQL> set colsep ""
SQL> set pagesize 0
SQL> set newpage none
SQL> set feedback off
SQL> set termout off
SQL> set recsep off
SQL> set sqlprompt ""
SQL> set heading off
SQL> set echo off
SQL> spool c:\emp.txt
SQL> select * from emp;
SQL> spool off
|
Bookmark
