cancel
Showing results for 
Search instead for 
Did you mean: 

Sqlloader / Sh Shell Script

Prabhu_7
Frequent Advisor

Sqlloader / Sh Shell Script

I have a shell script , from which i call sqlload statement.

SQLLoad < ...... >

this loads data file into my database table.After loading i do lot of process.

But at this point i want to compare no of rows in datafile and database table and ensure that all rows are loaded successfully and then proceed with other process, else i need to throw error EXIT and come out.

How to do this ? Pls help very urgent ....

Thanks!!!!
Raj

8 REPLIES
Yogeeraj_1
Honored Contributor

Re: Sqlloader / Sh Shell Script

hi,

Are your using 9iR1 or up?

If yes, you can try external tables (provides the ability to query a flat file using SQL). With this the need to use sqlldr is lessened. I find the flexibility of SQL over SQLLDR control files to be so useful that I would opt for an external table over sqlldr in most all cases.

This should help fix the problem.

Otherwise, you can compare the log file generated by the load with the number of records processed after each load process.
For example:
the log file will contain a section with the following lines:
...
Table DEPT:
4 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded all fields were null.
....

hope this helps!
Regards
Yogereaj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Radhakrishnan Venkatara
Trusted Contributor

Re: Sqlloader / Sh Shell Script

hi,

here is an example of oracle.

u load data file using sqlldr

sqlldr user/passwd controll=file.ctl data=datafile.dat


u will get the datafile.log
datafile.bad

if datafile.bad is zero then all your rows and tables are in tact.

u can check other wise datafile.log it will tell errors reported.

u can compare controlfile.log and datafile.bad and u can find it out.

radhakrishnan
Negative thinking is a highest form of Intelligence
Prabhu_7
Frequent Advisor

Re: Sqlloader / Sh Shell Script

Hi Yog / RK...

Thanks for ur reply....
i think i can check for .BAD file and if it doesnt exist then i can assume the table is loaded completely..

am i right ?

Raj !!!
Yogeeraj_1
Honored Contributor

Re: Sqlloader / Sh Shell Script

yes.

when you load the data, make sure to use the BAD= option on the command line to avoid reading and writing the same file in during the load!

sqlldr userid=yd/yd@mydb control=load_working.ctl bad=working.bad data=new.bad

After the load, if there are any records in WORKING.BAD, they would be really bad records, records that violated some other constraint, and must be reviewed.

hope this helps!

Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
R. Allan Hicks
Trusted Contributor

Re: Sqlloader / Sh Shell Script

If you are using 9i, I recommend using the external table option new to 9i.

The internal table option allows you to create a table that loads the flat file using sqlldr in a behind the scenes manner. To you it looks like a table, but it is really a flat file.

For example:

create table employees_ext
(employee_id number,
first_name char(30),
last_name char(30),......)
ORGANIZATION EXTERNAL( -- External Table
TYPE oracle_loader -- Access driver
DEFAULT DIRECTORY data_dir -- Files Directory
ACCESS PARAMETERS -- Similar to SQLLDR
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ', '
BADFILE 'bad_emp_ext'
LOGFILE 'log_emp_ext'
MISING FIELDS ARE NULL)
LOCATION ('empl.txt'))
REJCECT LIMIT UNLIMITED;

This example creates a table employees_ext. You can do select count(*) on it and any read operation that you would normally do. Note that the same sqlldr parameters are the same as the access parameters.

So, you can do stuff like:

insert into emp as (select * from employees_ext);



select count(*) into extcount from employees_ext;
select count(*) into empcount from emp;

if extcount <> empcount then
dbms.output.putline('COUNTS DO NOT MATCH');
rowback;
exit;
endif;


This is one of my personal favorite features of 9i.

-Good Luck
"Only he who attempts the absurd is capable of achieving the impossible
Prabhu_7
Frequent Advisor

Re: Sqlloader / Sh Shell Script

Unfortunately i use Oracle 8.0

:-(

Thanks all.

Raj!!!
Prabhu_7
Frequent Advisor

Re: Sqlloader / Sh Shell Script

Unfortunately i use Oracle 8.0

:-(

Thanks all.

Raj!!!
Prabhu_7
Frequent Advisor

Re: Sqlloader / Sh Shell Script

Unfortunately i use Oracle 8.0

:-(

Thanks all.

Raj!!!