topic Re: Sqlloader / Sh Shell Script in Operating System - HP-UX

Sqlloader / Sh Shell Script

Prabhu_7 — Thu, 15 May 2003 02:46:11 GMT

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

Re: Sqlloader / Sh Shell Script

Yogeeraj_1 — Thu, 15 May 2003 03:23:59 GMT

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

Re: Sqlloader / Sh Shell Script

Radhakrishnan Venkatara — Thu, 15 May 2003 03:26:51 GMT

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

Re: Sqlloader / Sh Shell Script

Prabhu_7 — Thu, 15 May 2003 03:35:25 GMT

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 !!!

Re: Sqlloader / Sh Shell Script

Yogeeraj_1 — Thu, 15 May 2003 04:03:40 GMT

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

Re: Sqlloader / Sh Shell Script

R. Allan Hicks — Fri, 16 May 2003 17:17:51 GMT

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

Re: Sqlloader / Sh Shell Script

Prabhu_7 — Fri, 16 May 2003 18:59:18 GMT

Unfortunately i use Oracle 8.0

:-(

Thanks all.

Raj!!!

Re: Sqlloader / Sh Shell Script

Prabhu_7 — Fri, 16 May 2003 19:00:07 GMT

Unfortunately i use Oracle 8.0

:-(

Thanks all.

Raj!!!

Re: Sqlloader / Sh Shell Script

Prabhu_7 — Fri, 16 May 2003 19:00:18 GMT

Unfortunately i use Oracle 8.0

:-(

Thanks all.

Raj!!!