Not a before routine, can't update job parameters once a job is running.Teej wrote:*bang my head on the table* Why didn't I think of THAT?kcbland wrote:Do a select max surrogate key on the target table prior to the job run and pass it in as a job parameter.
Hey wait...
How do you pass values FROM the select into a job parameter? Right now I'm thinking of this being done in a before-job routine...
-T.J.
There's 2 really slick solutions. This would be my favorite. Write a text file listing all tables you need to go do a select max on. Write a sql script that takes as a parameter the table name. Write a anonymous block pl/sql procedure that takes a table name parameter, looks at the table ddl and gets the primary key column. Then, do a select max on that table.column. Now, write a controlling shell script that reads the text of of table lists, and in a loop peel off the table name and then run a sqlplus session with a background & giving it the anonymous block pl/sql script with the table name piping output to a log file. After all are fire off do a wait statement. Then, parse all output files for the max value output and stuff into a nice text file of table | max value.
Add to your job control the ability to read this file and feed as a parameter the appropriate max value. Or, if using server jobs, read this file into a stage variable on initialize, parsing for the required table.
Another technique is to make a centipede looking job, with a bunch of OCI stages doing select max's on their respective table, sending output to the same hash file of tablename and max value. Dump the hash file to text file if wanted, or leave as hash and read from there.