Calling SQL*Loader from a Stored Procedure, etc... 1

[THoey]

I have an Oracle 7.3.4 database on NT that I am migrating to Oracle 8.1.7. I am also using this chance to improve upon the design and process of the whole system. I have rebuilt my main table (2 weeks data, 6 million rows avg.) to be partitioned.

I currently have a batch file that does the following steps that I want to change over to stored procedures/functions and to schedule through DBMS_JOB:

- Delete old data from table
- Drop Indexes
- Load new data via SQL*Loader
- Recreate Indexes
- Create summary data into summary table
...

I have written a stored procedure and a function that handles the truncating of a partitions (one day) worth of data and this appears to work really well. The only thing I noticed when I was testing it was that the index was put into an unusable state after the truncation was completed. Not sure if it was still rebuilding the index or not, but I tried to add some data back in and it errored. Any ideas on this?

I can write the SQL to drop and recreate the indexes. Not really sure I need this if the truncation/index unusable issue gets resolved.

The next major step is SQL*Loader. I would like to verify that the file exists in the appropriate location and if so, call SQL*Loader to load it. The procedure would need to wait for it to complete as there are a total of three files I need to load and then the summary data is created based on this new data. Can someone give me a kick in the right direction as to any packages, procedures, functions that I should use for this?

Also, any other suggestions would be greatly appreciated... Terry M. Hoey

 

[carp]

Well, verifying the file exists should be pretty easy. Use utl_file.fopen('/path/to/the/file','file_name','R'), which should raise an exception if the file isn't there.(Caution: Do NOT use 'W' when fopening the file. This will erase your data!).

Calling SQL Loader from within a stored procedure might be a bit trickier, since it is an OS command line utility. I'm not sure how you would do this, but will watch this space in eager anticipation!

 

[Kenrae]

Well, in Oracle 8 you can execute native code writen in C or Java. You can use this tool to execute OS command lines, I've done it.
First you must create a dinamic linked library (.so in Unix machines or .dll if you use NT). This library, coded in C, can then execute its parameter (a string) with

 system

call (at least in Unix that's the call, I don't know what call to use in NT machines, but there's surely one).
If you want to know more about it, search for extproc in Oracle documentation or even in this forum, I think we've talked about it.
You can also visit this site:

http://www2.athenet.net/orant/odlapr98/library/support/scr/content/376/376.htm

Hope this helps

 

[THoey]

Thanks, but that link is no good. Can't be found... Terry M. Hoey

 

[Kenrae]

Well, this link was good some hours ago, I don't know what have happened...
Try this one:

http://www.orafaq.com/scripts/c_src/extproc.txt

Or go directly to

http://www.orafaq.com

and search for "External Procedure"

 

[THoey]

Thanks Kenrae. I went to Oracle's support site and did a search for extproc. I got the same answer you gave. Write a .DLL. So... Time to look for a compiler. Not going to give up on this... Terry M. Hoey

 

[karluk]

Global indexes are, indeed, marked as unusable after truncating a partition. You will have to rebuild them, or drop and recreate them as you do currently. Either that, or experiment with using local indexes.

 

[THoey]

Thanks Karluk. I will take a look at the local indexes... Terry M. Hoey

 

[darshbal]

Was someone able to get pl/sql to run the sql loader file and if so would they mind if they can share teh code here