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.
