SQL LOADER (sqlldr)

UPS · Post by **UPS** » Fri Apr 13, 2007 3:45 pm

How can I set up SQL loader (sqlldr) within DataStage? We have sequential files that will need to be loaded to our Orable database table. Also, is it possible to sql load a dataset, or the file has to be a sequential file?

Thank you.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 13, 2007 4:13 pm

sqlldr is an Oracle utility. You set up the Oracle utility on the DataStage server machine, but not "within" DataStage.

DataStage can invoke sqlldr through its command line or via the ORAOCIBL or ORABULK stage types.

UPS · Post by **UPS** » Tue Apr 17, 2007 1:24 pm

In the ORABULK stage, I created the datafile and the controlfile and the rest of the properties (properties tab), what goes to the input fields in the General tab that are required? Where exactly is the database connection done? Is it on the server side or can it be done in the ORABULK stage?

chulett · Post by **chulett** » Tue Apr 17, 2007 1:31 pm

The ORABULK stage is old and really was meant only for Oracle 7 databases.

Stick with the ORAOCIBL a.k.a. the 'Oracle OCI Load' stage. There you have a choice of Automatic versus Manual mode. The former is 'bulk loading on the fly' and the latter creates the files you are expecting and you must then 'manually' issue the sqlldr command after it completes.

UPS · Post by **UPS** » Tue Apr 17, 2007 2:48 pm

Thank you!

Is that one of the main differences between the two ("Oracle 7 Load" and "Oracle OCI Load")? So for the ORAOCIBL, will I need another stage to run the sqlldr (like Wrapped stage) if the Load Mode is set to "Manual"? What are the pros and cons of using the "Automatic" or using "Manual" for the Load Mode?

ray.wurlod · Post by **ray.wurlod** » Tue Apr 17, 2007 3:03 pm

Automatic means that sqlldr is kicked off straight away.

Manual means that sqlldr is not kicked off straight away. This can be useful where, for example, your time windows for extraction and loading do not coincide. You can still completely preserve control within DataStage - use an Execute Command activity in a job sequence to initiate sqlldr at the appropriate time.

UPS · Post by **UPS** » Wed Apr 18, 2007 8:57 am

"ORAOCIBL_TEST..Oracle_OCI_Load_0: Internal Error: Unable to initialize the Direct Path Context
ORA-00942: table or view does not exist"

I am receiving the above message. What is exactly the "Direct Path Context?" Is this the connection to the database? the Directory Path field - this is the actual local path where the sql loader can be generated, correct?

Please advise. Thank you.

chulett · Post by **chulett** » Wed Apr 18, 2007 9:11 am

Don't worry about the 'Direct Path Context' part - this is your issue:

ORA-00942: table or view does not exist

Verify your table and schema names in the stage.

DSXchange

SQL LOADER (sqlldr)

SQL LOADER (sqlldr)

Using ORABULK stage

Difference Between "Oracle 7 Load" and "Oracl

Directory Path?