Script sql for sqlldr

Hi

This is a script sql to create a dat and a ctl file to load data on an oracle table from a user to another user.

I searched this on internet without success so i had wrote it.

Enjoy :

--

-- Created by Giovanni Palleschi - Ver. 1.0 - 10/10/2011

--

-- This script sql permits to create files dat and ctl for sqlldr oracle utility

-- Script accepts in input two parameters :

--

-- 1) Table Name

-- 2) Condition to extract

--

-- Ej. sqlplus pippo/pluto @gpgen_sqlloader.sql TABLE1 "1=1"

--

-- In this mode will be extracted all rows from table TABLE1.

-- Script will produce two files :

--

-- ./TABLE1.dat

-- ./TABLE1.ctl

--

--

set echo off

set termout off

set feedback off

SET serverout ON size unlimited

set linesize 8192

set pagesize 0

set verify off

set heading off

host echo ' Start Generacion sqlloader files for table &1 and condition &2'

host echo ' '

host echo ' '

host echo ' ......... Working ......... '

host echo ' '

host echo ' '

spool ./gen_dat.wrk

DECLARE

-- TO MODIFY

Separator VARCHAR2(1) := CHR(29);

DateFormat VARCHAR2(20) := 'YYYYMMDD HHMISS';

CURSOR UserTabColumns_cursor ( TableName IN VARCHAR2 )

IS

SELECT

COLUMN_ID,

COLUMN_NAME,

DATA_TYPE,

DATA_LENGTH

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = TableName ORDER BY COLUMN_ID;

recUserTabColumns UserTabColumns_cursor%ROWTYPE;

BEGIN

DBMS_OUTPUT.PUT_LINE('###QUERY###SET LINESIZE 8192');

DBMS_OUTPUT.PUT_LINE('###QUERY###SET PAGESIZE 0');

DBMS_OUTPUT.PUT_LINE('###QUERY###SET HEADING OFF');

DBMS_OUTPUT.PUT_LINE('###QUERY###SELECT ');

DBMS_OUTPUT.PUT_LINE('###CTRL###LOAD DATA');

DBMS_OUTPUT.PUT_LINE('###CTRL###INFILE ''./&1..dat''');

DBMS_OUTPUT.PUT_LINE('###CTRL###BADFILE ''./LOG/&1..bad''');

DBMS_OUTPUT.PUT_LINE('###CTRL###DISCARDFILE ''./LOG/&1..dsc''');

DBMS_OUTPUT.PUT_LINE('###CTRL###APPEND');

DBMS_OUTPUT.PUT_LINE('###CTRL###INTO TABLE &1');

DBMS_OUTPUT.PUT_LINE('###CTRL###FIELDS TERMINATED BY '''|| Separator || '''');

DBMS_OUTPUT.PUT_LINE('###CTRL###TRAILING NULLCOLS');

DBMS_OUTPUT.PUT_LINE('###CTRL###(');

OPEN UserTabColumns_cursor('&1');

LOOP

FETCH UserTabColumns_cursor INTO recUserTabColumns;

EXIT WHEN UserTabColumns_cursor%NOTFOUND;

IF recUserTabColumns.COLUMN_ID > 1 THEN

DBMS_OUTPUT.PUT_LINE('###QUERY###||''' || Separator || '''||');

DBMS_OUTPUT.PUT_LINE('###CTRL###,');

END IF;

IF recUserTabColumns.DATA_TYPE = 'DATE' THEN

DBMS_OUTPUT.PUT_LINE('###QUERY### to_char(' || recUserTabColumns.COLUMN_NAME || ',''' || DateFormat || ''')');

DBMS_OUTPUT.PUT_LINE('###CTRL###' || recUserTabColumns.COLUMN_NAME || ' DATE ' || '"' || DateFormat ||'"');

ELSIF recUserTabColumns.DATA_TYPE IN ('LONG RAW','LONG','RAW') THEN

DBMS_OUTPUT.PUT_LINE('###CTRL### ' || recUserTabColumns.COLUMN_NAME);

DBMS_OUTPUT.PUT_LINE('###QUERY### ''''');

ELSIF recUserTabColumns.DATA_TYPE in ('VARCHAR2','NVARCHAR2','NCHAR','CHAR') THEN

DBMS_OUTPUT.PUT_LINE('###QUERY### REPLACE(' || recUserTabColumns.COLUMN_NAME || ',CHR(10),'' '')');

DBMS_OUTPUT.PUT_LINE('###CTRL### ' || recUserTabColumns.COLUMN_NAME || ' CHAR(' || recUserTabColumns.DATA_LENGTH || ')');

ELSE

DBMS_OUTPUT.PUT_LINE('###QUERY### ' || recUserTabColumns.COLUMN_NAME);

DBMS_OUTPUT.PUT_LINE('###CTRL### ' || recUserTabColumns.COLUMN_NAME);

END IF;

END LOOP;

CLOSE UserTabColumns_cursor;

DBMS_OUTPUT.PUT_LINE('###QUERY###FROM &1 WHERE &2;');

DBMS_OUTPUT.PUT_LINE('###CTRL###)');

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20001,

'Oracle Error MSG: >' || SQLERRM(SQLCODE) || '<');

END;

/

spool off

host grep "###QUERY###" ./gen_dat.wrk > ./gen_dat.wrk2

host sed -e "s/###QUERY###//" -e "s/ *$//" ./gen_dat.wrk2 > ./gen_dat.sql

host grep "###CTRL###" ./gen_dat.wrk > ./gen_dat.wrk2

host sed -e "s/###CTRL###//" -e "s/ *$//" ./gen_dat.wrk2 > ./&1..ctl

spool ./gen_dat.wrk

@./gen_dat.sql

spool off

host sed -e "s/ *$//" ./gen_dat.wrk > ./&1..dat

--host rm -fr ./gen_dat.wrk

--host rm -fr ./gen_dat.wrk2

--host rm -fr ./gen_dat.sql

host echo ' '

host echo ' '

host echo ' End Generacion sqlloader files for table &1 and codition &2 '

host echo ' '

host echo ' sqlldr userid=user/password control=./&1..ctl log=./&1..log'

host echo ' '

host echo ' '

quit;

Stay tuned.