sqlldr oracle 9i

[himanshu_2]

We are running Oracle 9i on our HPUX 11i with the recently released patch for oracle 9i applied. It so happends that the sqlldr now fails to load the data with the same set of control file and data as earlier. When we changed the sqlldr binary to the original 9i version, the data was loaded correctly.

Any pointers?

[T G Manikandan]

Probably the sqlldr executable could have been corrupted.

Relink the sqlldr Oracle executable with the following steps:
1.cd $ORACLE_HOME/rdbms/lib
2.make -f ins_rdbms.mk isqlldr

Make sure $ORACLE_HOME & LD_LIBRARY_PATH is properly set.


Thanks

[Yogeeraj_1]

Hi,

To add to TG's reply (yes! make sure you did the relinking properly),

I think with the version of database you have, you can start using and reep the benefits from the numerous advantages of using external tables. Like:

o merge a flat file with an existing table in one statement.
o sort a flat file on the way into a table you want compressed nicely.
o do a parallel direct path load -- without splitting up the input file, writing umpteen scripts and so on
o run sqlldr in effect from a stored procedure or trigger (insert is not sqlldr)
o do multi-table inserts
o flow the data through a pipelined plsql function for cleansing/transformation

and so on. they are *instead of* sqlldr -- to get data into the database without having to use sqlldr in the first place.

You would not normally query them day to day in an operational system, you use them to load data.

Consider the example below:
(compares External Tables with SQLLDR)
======================================

yd@ORA920.MYDB.MU> create or replace directory data_dir as '/tmp/'
2 /
Directory created.

yd@ORA920.MYDB.MU> create table external_table
2 (EMPNO NUMBER(4) ,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 )
11 ORGANIZATION EXTERNAL
12 ( type oracle_loader
13 default directory data_dir
14 access parameters
15 ( fields terminated by ',' )
16 location ('emp.dat')
17 )
18 /
Table created.

yd@ORA920.MYDB.MU> create or replace view emp_view
2 as
3 select * from scott.emp, all_objects;
View created.


Suppose, that you have a file containing data (e.g. /tmp/emp.dat)


yd@ORA920.MYDB.MU> !wc /tmp/emp.dat
410340 568568 57946304 /tmp/emp.dat

yd@ORA920.MYDB.MU> select count(*) from external_table
2 /

COUNT(*)
----------
410340



yd@ORA920.MYDB.MU> set timing on
yd@ORA920.MYDB.MU> create table emp as select * from external_table;

Table created.

Elapsed: 00:00:03.13


yd@ORA920.MYDB.MU> insert /*+ append */ into emp select * from external_table;

410340 rows created.

Elapsed: 00:00:07.69


yd@ORA920.MYDB.MU> truncate table emp;

Table truncated.

===========
versus
===========
$ time sqlldr / /tmp/emp.ctl direct=true

SQL*Loader: Release 9.2.0.1.0 - Production on Thu Aug 7 17:15:02 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Load completed - logical record count 410340.

real 0m8.747s
user 0m2.422s
sys 0m0.588s
$


External tables are so much easier the sqlldr -- insert /*+ append */ is showing about the same performance, CTAS even better.

You can use SQL to filter and transform the data much easier.

Hope this helps!

Regards
YOgeeraj

[kwalther]

In reference to external tables...I'm not impressed with the concept because you still have to format the data in csv or something before you can insert that data into a temp table.
Other than moving the data off the database into a temporary holding area until you direct load it, I don't see that much of a benefit; of course I haven't researched any of the parameters either. I do appreciate your sharing of the information on this topic and I will investigate it further because of this article.
There???s another document at http://www.dbasupport.com/oracle/ora9i/External_Tables9i.shtml which explains external tables for those interested.
On a side note: when I need text data formatted into an acceptable pill for oracle to swallow, I usually use access to do a quick csv conversion.

[himanshu_2]

There are reasons for not taking an alternate route, other than sqlldr.
The strange part about the problem is:
The new sqlldr fails to work when there are filler fields in the control file, otherwise it works fine. More over the same set of control file ( with filler fields) work fine if the sqlldr is run from the SQL command line rather than from an application as in the earlier case.