call SQLLDR from PL/SQL

[Beantree]

How can you call a SQLLDR job from a PL/SQL procedure? As an alternative, can you schedule a SQLLDR job in Oracle?

 

[THoey]

I have asked your first question before and did not get an answer. It almost looks like you have to create a DLL file and then call the function from there.
Terry M. Hoey

 

[rcurva]

And if you are running in unix, you can create a shell script that performs loading and schedule the script to run from your crontab.

If running in NT, create a batch program to do the loading and schedule the script to run using "at" in NT.

 

[carp]

The problem arises from the fact that SQLLDR is a utility that is invoked from the O/S command line, so it is difficult (so far as I know, it's impossible, but there's probably a way) to invoke it from within PL/SQL. Consequently, as rcurva indicates, you are driven to O/S level scripts that are invoked via the O/S's scheduling utility.

 

[rcurva]

You can also read more about "Creating External Procedures" in Oracle. This is something about creating C or Java programs that call OS commands like sqlldr. From pl/sql procedure you can make an external call to the programs that you made.

This implementation is available both in Unix and NT, but my knowledge is short on this one, but I hope I sparked an idea in you.

 

[Kenrae]

It's possible, I've made it on Unix. It's a matter of creating a dynamic library (.dll or .so) that can be called from pl/sql. You can have a

system()

call on this library.

By the way, I think the simplest approach is what rcurva suggested. Just create a script or batch file calling sqlldr and schedule it. System scheduling is usually safer than Oracle jobs.