Updating an Oracle Table

[Jerry Novak]

Has anyone had any experience updating an Oracle table from PB?
Every week we receive updated flat files that we save in a directory structure by week.
These are automatically FTPed using a process built with PowerBasic.
We have a table set up to accomodate the file and we have an Oracle procedure that will populate the table.

However, is there a way to kick off an Oracle procedure at a certain time automatically?
Any thoughts?

[Michael Mattias]

I have several apps which update Oracle tables. But I cheated, I create ODBC datasources and update that way.

In your case, since you have a procedure written to populate the table, you might want to put that in a separate file and just launch SQLPlus with that file name on the command line.

As far as "kicking off" at certain times, that's easy: Windows scheduler.. start SQLplus.exe at named time(s).

(Or, write your own program to do same if you have a bias against Scheduler. But for goodness sake, don't use SLEEP or loops to test the current time: earn some panache points by using Waitable Timer Object Demo June 2005).

MCM

[Chris Holbrook]

...is there a way to kick off an Oracle procedure at a certain time automatically?

Oracle Scheduler, of course!

[Michael Mattias]

Oracle has a scheduler, too?

What, does everyone but me have one of these?

[Brian Harney]

Oracle Jobs

Oracle Scheduler is something new (Oracle 11?) but Oracle Jobs
goes way back, to at least Oracle 8.0.5.
An Oracle Job can be defined in the database itself as an Oracle object,
with it's own scheduling and control.

Oracle can shell out to the OS, but that's shunned by most shops
for security reasons and is usually turned off by the DBA, in my experience.

Oracle 9 and later have something called an external table, where you define a table with the layout that matches your load file that resides in the file system and you incude the path and filename.

You can then insert into the target table with data selected from the external table. Good simple solution if the load file is ready as is, and no edit checks or business logic has to be performed during the load. Otherwise, you might wanna get fancier and also use a stored procedure and/or triggers to transform/edit the data.

Using cron (Unix) or scheduled-tasks (windows) is good, but if SQLplus or SQLloader cannot connect to the database (listener or database is down or unavailable), the program will sit there, hung. Just a little caveat.

[Michael Mattias]

I think you can do the same kind of thing using SQL-Loader.

I've never used SQL-Loader, but I have the O'Reilly book by Jonathan Gennnick here and "text file to database" actually looks like exactly what it was designed to do.

(I didn't buy the book... Mr. Gennick sent me a copy as a thank-you for providing some help when he wrote it. Plus, he gave me a credit in the Forward, which is actually pretty cool).

[Chris Holbrook]

I've never used SQL-Loader

It's comprehensive but can be slow, have had to write Pro C to get better performance

(... Mr. Gennick sent me a copy as a thank-you for providing some help when he wrote it. Plus, he gave me a credit in the Forward, which is actually pretty cool).

We walk among gods.

[Michael Mattias]

Nah, no big deal.

You can get the same info Mr. Gennick used by downloading some freeware I have posted on my web site and some other stuff I posted on the Flexus web site; then you have to call me on the phone to confirm everything I wrote.

FWIW, the info is the bit-level definitions of COBOL-created data.

I suggest you start reading it only if you want to get a head start on a good night's sleep.

MCM