Link to home
Start Free TrialLog in
Avatar of TOWELLR
TOWELLR

asked on

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.
Avatar of Sean Stuber
Sean Stuber

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.
Avatar of TOWELLR

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
Avatar of TOWELLR

ASKER

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.
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.
>> 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.
Glad to be of help, but may i ask why a B grade? Is there anything else that you wanted to know?