Page 1 of 1
SQL LOADER (sqlldr)
Posted: Fri Apr 13, 2007 3:45 pm
by UPS
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.
Posted: Fri Apr 13, 2007 4:13 pm
by ray.wurlod
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.
Using ORABULK stage
Posted: Tue Apr 17, 2007 1:24 pm
by UPS
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?
Posted: Tue Apr 17, 2007 1:31 pm
by chulett
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.
Difference Between "Oracle 7 Load" and "Oracl
Posted: Tue Apr 17, 2007 2:48 pm
by UPS
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?
Posted: Tue Apr 17, 2007 3:03 pm
by ray.wurlod
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.
Directory Path?
Posted: Wed Apr 18, 2007 8:57 am
by UPS
"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.
Posted: Wed Apr 18, 2007 9:11 am
by chulett
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.