[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 991
  • Last Modified:

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.
0
TOWELLR
Asked:
TOWELLR
  • 3
  • 3
  • 2
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now