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('my job');
Our developer says he is having trouble passing variable names to the DBMS_JOB call.
Any ideas how to accomplish this process.
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_type => 'executable',
job_action => '/app/oracle/x.sh <filename>, <ctlname>',
enabled => TRUE,
auto_drop => TRUE);
END;
/
exec dbms_scheduler.run_job('my
Our developer says he is having trouble passing variable names to the DBMS_JOB call.
Any ideas how to accomplish this process.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
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.
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.
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.
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?
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.