Oracle job to sqlload data from a shell script.

I am trying to plan a load routine for the following:

Every time a file name gets loaded in this table a trigger will fire to perform the following:

1. Start an Oracle Job to passing file name and ctl file name to a shell script.
2. Shell script executes sqlldr routine.

I was hoping to setup this job in the trigger.

BEGIN
  dbms_scheduler.create_job(job_name        => 'myjob',
                            job_type        => 'executable',
                            job_action      => '/app/oracle/x.sh <filename>, <ctlname>',
                            enabled         => TRUE,
                            auto_drop       => TRUE);
END;
/

exec dbms_scheduler.run_job('myjob');

Our developer says he is having trouble passing variable names to the DBMS_JOB call.

Any ideas how to accomplish this process.
TOWELLRAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
dbms_scheduler and dbms_job are two different scheduling mechanisms within oracle.

If you set up a dbms_schedule, your developer should not be using dbms_job to talk to it.


as for passing the parameters, your shell script can look them up from a table.  It can connect and read them then execute.

You could also skip the shell completely and use a pl/sql procedure to create an external table to process the file for you.
0
TOWELLRAuthor Commented:
Thanks for the info.  Interesting idea to load as external table.

The problem is that the shell script will perform several other tasks after the load.

In the dbms_schedule command

For    job_action      => '/app/oracle/x.sh <filename>, <ctlname>'

is it possible to pass filename and ctlname as variables or do they not get interpreted correctly?
0
Jinesh KamdarCommented:
I guess u can pass variables to it. Try somethng like this.

PROCEDURE load_file (filename IN VARCHAR2, control_filename IN VARCHAR2)
IS
BEGIN
  dbms_scheduler.create_job(job_name        => 'myjob',
                            job_type        => 'executable',
                            job_action      => '/app/oracle/x.sh ' || filename || ' ' || ctl_filename,
                            enabled         => TRUE,
                            auto_drop       => TRUE);
END load_file;

Call this SP from ur trigger with the parameters deriving values from the INSERTed values.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdstuberCommented:
maybe I misunderstood the request...

TOWELLER,  are you looking for a way to pass parameters to the creation of the job
or to pass parameters at the time the job runs.

looks like jinesh assumed the former
and I assumed the latter.

can you clarify?
0
TOWELLRAuthor Commented:
I am trying to pass variables when I create the job.  The proc will lookup the filename and ctlfile name and try to create a job with those variables.

He says that the job_action portion has to be hard coded

Trying to find out if this is true.
0
sdstuberCommented:
sort of half right.

the job action has to be fixed at creation time
but does not have to be hardcoded.

The script that jinesh submitted above with concatenated parameters will accomplish that.
0
Jinesh KamdarCommented:
>> He says that the job_action portion has to be hard coded

I don't think this is true. The job_action is just a parameter that accepts a command string and so can be built just as other strings can be built using variables and expressions.
0
Jinesh KamdarCommented:
Glad to be of help, but may i ask why a B grade? Is there anything else that you wanted to know?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.