Automating Oracle SQL*LDR

run.sh

#!/bin/sh

#nohup load.sh > a.out 2>&1 &;

/bin/sh truncate.sh
/bin/sh sqlldr.sh
/bin/sh load.sh
perl load.pl > logs/load.log
cat logs/load.log
/bin/sh count.sh

truncate.sh

#!/bin/sh

unalias rm
rm bad/*.bad
rm dsc/*.dsc
rm log/*.log
rm ctl/*.ctl
alias rm 'rm -i'

sqlplus <username>/<password> @constraints.sql disable
sqlplus <username>/<password> @constraints.sql disable

sqlplus <username>/<password><;
.
.
.
exit;
!

sqlplus <username>/<password> @constraints.sql enable
sqlplus <username>/<password> @constraints.sql enable

constraints.sql

set serveroutput on;
execute dbms_output.enable(1000000);
spool ./logs/&1\.sql;
set head off;
select 'alter table '||table_name||' modify constraint '||constraint_name||' &1;' from user_constraints 
where table_name like 'IMP_%'
or table_name in ('listof table names');
spool off;
@@./logs/&1\.sql;
@@./logs/&1\.sql;
set serveroutput off;
exit;

constraints.sh

#!/bin/sh

#nohup load.sh > a.out 2>&1 &

rm -f /export/home/oracle/sbin/sqlldr/log/*.log
sqlplus @constraints.sql disable 
sqlplus <username>/<password> @constraints.sql disable 
/bin/sh truncate.sh
/bin/sh sqlldr.sh
/bin/sh load.sh
sqlplus <username>/<password> @constraints.sql enable  
sqlplus <username>/<password> @constraints.sql enable  
perl load.pl > load.log
cat load.log
/bin/sh counts.sh

sqlldr.sh

#!/bin/sh

sqlplus <username>/<password><<!
set serveroutput on;
execute dbms_output.enable(1000000);
@sqlldr.sql;
alter procedure sqlldr compile;
execute sqlldr(pTABLE=>'<table name>',pFILE=>'<file name>');
.
.
.
execute dbms_output.disable;
set serveroutput off;
exit
!

sqlldr.sql

create or replace PROCEDURE SQLLDR
(
	 pTABLE IN VARCHAR2 DEFAULT NULL
	,pTYPE IN VARCHAR2 DEFAULT 'TAB_SEPARATED'
	,pOPTION IN VARCHAR2 DEFAULT 'INSERT' 					--INSERT is default, otherwise APPEND,REPLACE,TRUNCATE
	,pDATA IN VARCHAR2 DEFAULT '/export/home/oracle/sbin/crm/sqlldr/data/'
	,pCTL IN VARCHAR2 DEFAULT '/export/home/oracle/sbin/crm/sqlldr/ctl/'
	,pDATE IN VARCHAR2 DEFAULT 'MM/DD/YY'
	,pFILE IN VARCHAR2 DEFAULT NULL
)
AS

	TYPE cCURSOR IS REF CURSOR;
	lCURSOR cCURSOR;
	lTABLE_NAME VARCHAR2(30);
	lCOLUMN_NAME VARCHAR2(30);
	lDATA_TYPE VARCHAR2(106);
	lDATA_LENGTH NUMBER;
	lDATA_PRECISION NUMBER;
	lDATA_SCALE NUMBER;

	lPOSITION INTEGER DEFAULT 0;
	lBUFFER VARCHAR2(1024);
	lFIRST BOOLEAN DEFAULT TRUE;
	lFH UTL_FILE.FILE_TYPE;

BEGIN

	dbms_output.put_line(pFILE||' '||pDATA||' '||pCTL);

	IF NOT pDATA IS NULL AND NOT pCTL IS NULL AND NOT pFILE IS NULL THEN

		lFH := UTL_FILE.FOPEN (pCTL, pFILE||'.ctl', 'w');

		IF UTL_FILE.IS_OPEN (lFH) = TRUE THEN

			UTL_FILE.PUT_LINE (lFH, 'LOAD DATA');
			UTL_FILE.PUT_LINE (lFH, 'INFILE '''||pDATA||pFILE||'.dat'||'''');
			UTL_FILE.PUT_LINE (lFH, 'INTO TABLE '||pTABLE);
			UTL_FILE.PUT_LINE (lFH, pOPTION);
			UTL_FILE.PUT_LINE (lFH,'FIELDS TERMINATED BY "	"');
			UTL_FILE.PUT_LINE (lFH, 'TRAILING NULLCOLS');
			UTL_FILE.PUT_LINE (lFH, '(');

			OPEN lCURSOR FOR 'SELECT table_name, column_name, data_type, data_length, data_precision, data_scale FROM USER_TAB_COLUMNS WHERE table_name='''||pTABLE||''' ORDER BY COLUMN_ID';
			LOOP

				FETCH lCURSOR INTO lTABLE_NAME,lCOLUMN_NAME,lDATA_TYPE,lDATA_LENGTH,lDATA_PRECISION,lDATA_SCALE;
				EXIT WHEN lCURSOR%NOTFOUND;

				IF lFIRST THEN
					UTL_FILE.PUT (lFH, CHR(9)||' ');
					lFIRST := FALSE;
				ELSE
					UTL_FILE.PUT (lFH, CHR(9)||','); 
				END IF;

				SELECT column_name||' '||
	
				DECODE
				(
					 data_type
					,'VARCHAR2','CHAR('||data_length||')'
					,'CHAR','CHAR('||data_length||')'
					,'NUMBER',DECODE(NVL(data_scale,0),0,'INTEGER','FLOAT')
					,'FLOAT','FLOAT'
					,'DATE','DATE "'||pDATE||'"'
				)||

				DECODE
				(
					 data_type
					--,'VARCHAR2',	' NULLIF '||column_name||'=BLANKS'
					--,'CHAR',	' NULLIF '||column_name||'=BLANKS'
					,'NUMBER',	' EXTERNAL DEFAULTIF '||column_name||'=BLANKS'
					,'FLOAT',	' EXTERNAL DEFAULTIF '||column_name||'=BLANKS'
					,'DATE',	' NULLIF '||column_name||'=BLANKS'
				)

				into lBUFFER
				FROM USER_TAB_COLUMNS
				WHERE TABLE_NAME = lTABLE_NAME AND COLUMN_NAME = lCOLUMN_NAME
				ORDER BY COLUMN_ID;

				UTL_FILE.PUT_LINE (lFH, lBUFFER);

			END LOOP;
			CLOSE lCURSOR;

			UTL_FILE.PUT_LINE (lFH, ')');

		END IF;
		UTL_FILE.FCLOSE (lFH);

	END IF;

EXCEPTION WHEN OTHERS THEN
	CLOSE lCURSOR;
	IF UTL_FILE.IS_OPEN (lFH) = TRUE THEN
		UTL_FILE.FCLOSE (lFH);
	END IF;
	DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
	RAISE;
END;
/

alter procedure SQLLDR compile;
/

load.sh

#!/bin/sh

sqlldr parfile=load.par data=data/<file name>.dat control=ctl/<table name>.ctl log=log/<table name>.log bad=bad/<table name>.bad discard=dsc/<table name>.dsc
.
.
.

load.sql

#!/bin/sh

#sqlldr parfile=load.par data=data/.dat control=ctl/.ctl log=log/.log bad=bad/.bad discard=dsc/.dsc

sqlplus <username>/<password><<!
set serveroutput on;
execute dbms_output.enable(1000000);
set head off termout off echo off;
spool load.sh;
select 'sqlldr parfile=load.par data=data/'||table_name||'.dat control=ctl/'||table_name||'.ctl log=log/'||table_name||'.log bad=bad
/'||table_name||'.bad discard=dsc/'||table_name||'.dsc' from user_tables;
select table_name from user_tables;
spool off;
execute dbms_output.disable;
set serveroutput off;
exit
!

load.par

userid          = <username>/<password>   
discardmax      = 2
errors          = 1000000
direct          = TRUE

load.pl

opendir(DIR,"log") || die "Cannot open directory log : $!";

@files = readdir(DIR); $file = @files;

for ($i = 0; $i < $file; $i++)
{
        if (($files[$i] ne '.') && ($files[$i] ne '..'))
        {

                open (IN,"log/".$files[$i]); # || "Cannot open file ".$files[$i]." : $!"; next;
                $loaded=0;$skipped=0;$read=0;$rejected=0;$discarded=0;

                while()
                {
                        if ($_ =~ /^\s*(\d*) Rows successfully loaded.*$/i) { $loaded = $1; }
                        if ($_ =~ /^Total logical records skipped: \s* (\d*)$/i) { $skipped = $1; }
                        if ($_ =~ /^Total logical records read: \s* (\d*)$/i) { $read = $1; }
                        if ($_ =~ /^Total logical records rejected: \s* (\d*)$/i) { $rejected = $1; }
                        if ($_ =~ /^Total logical records discarded: \s* (\d*)$/i) { $discarded = $1; }
                }

                close (IN);

                #print "$files[$i],$loaded, $skipped, $read, $rejected, $discarded\n";
                if ($read > $loaded)
                {
                        print "ERROR,$files[$i],read = $read,loaded = $loaded, skipped = $skipped, rejected = $rejected, discarded = $discarded\n";         
                }
                else
		{
			print "OK,$files[$i],read = $read,loaded = $loaded, skipped = $skipped, rejected = $rejected, discarded = $discarded\n";
		}

        }
}

closedir DIR;

#Table ... :
#  32072 Rows successfully loaded.
#  7018 Rows not loaded due to data errors.
#  0 Rows not loaded because all WHEN clauses were failed.
#  0 Rows not loaded because all fields were null.
#
#Bind array size not used in direct path.
#Space allocated for memory besides bind array:        0 bytes
#
#Total logical records skipped:          0
#Total logical records read:         39090
#Total logical records rejected:      7018
#Total logical records discarded:        0

count.sh & count.sql

#!/bin/sh

$ORACLE_HOME/bin/sqlplus <username>/<password><<!
@count.sql;
exit
!
cat count.log

set termout off echo off feed off trimspool on head off pages 0;
spool count.log;
select 'select count(*),'''||table_name||''' from '||table_name||';' from dba_tables;
spool off;
set termout on;
@count.log;
set head on feed on