Subhash -- Thanks for the question regarding "DBMS_JOB -- how to pass parameters to the job", version 8.0.5
originally submitted on 11-Mar-2001 23:13 Eastern US time, last updated 11-Apr-2005 13:18Tom's latest followup | GOTO a Bookmarkable Page | Bottom
You Asked (Jump to Tom's latest followup)

Hi Tom

   I have one procedure execute_job with two in parameters
   (batchnumber and loaddate). Procedure as follows :-


create or replace procedure execute_job(batchnumber in number, Loaddate in
varchar2)
       is
        l_theCursor     integer default dbms_sql.open_cursor;
        l_status        integer;
        p_sql           varchar2(200);
        v_job           number;
        BEGIN
          p_sql  :='alter session set nls_date_format= ''dd-mon-yyyy
          hh24:mi:ss'' ';
          dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
          l_status := dbms_sql.execute(l_theCursor);
          dbms_sql.close_cursor(l_theCursor);
          dbms_job.submit(v_job ,  'fileimportedi.LoadFileLater
          (batchnumber);', Loaddate, null);
          commit;
END;


My problem is :-

   when i execute my procedure with following parameters :-

                Execute execute_job(347,'07-mar-2001 19:10:10');

Error comes :-

ERROR at line 1:
ORA-06550: line 1, column 117:
PLS-00201: identifier 'BATCHNUMBER' must be declared
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 72
ORA-06512: at "SYS.DBMS_JOB", line 140
ORA-06512: at "WMS.EXECUTE_JOB2", line 13
ORA-06512: at line 1

If i changed my dbms_job.submit as follows :-
      dbms_job.submit(v_job ,  'fileimportedi.LoadFileLater(347);',
      Loaddate, null);

then it's run successfully(no error comes) and job successfully submitted in
user_jobs table.

+++

Can we pass batchnumber as  parameter for another packaged procedure or not ? if
yes then how?



Thanks

Subhash

 

 
and we said...

What you should do is to create a parameter table:

create table parameters
( jobid   number PRIMARY KEY,
  batch   number,
 
);

and you would then:

....
dbms_job.submit(v_job , 'fileimportedi.LoadFileLater(JOB);',loadDate );
insert into parameters(v_job,batchnumber);
......

Every job can have access to its job number as a parameter like that.  That
would be used to look up the relevant data.  The reason for doing this is so you
are submitting the SAME sql string over and over again to the job queue --

       fileimportedi.LoadFileLater(JOB);

and not:

        fileimportedi.LoadFileLater(1);
        fileimportedi.LoadFileLater(2);
        fileimportedi.LoadFileLater(3);

which would tend to trash your shared pool with lots of UNIQUE sql.

Your loadFileLater routine should delete from the parameter upon successful
completion or you could submit:

  'fileimportedi.LoadFileLater(job);
   declare
       l_job number default job;
   begin
       delete from parameter where jobid = l_job;
   end;'

which would do it automatically for you if loadfilelater succeeded (the delete
would be skipped if the job failed)


short of that, you would have to code:


dbms_job.submit(v_job , 
              'fileimportedi.LoadFileLater(' || batchNumber || ');',
               Loaddate, null);

but that, as i said, would generate UNIQUE sql for each job which is BAD.



 
   Reviews     
GOTO a page to Bookmark Review | Bottom | Top
DBMS_JOB  May 21, 2002
Reviewer:  Ruben  from Sanjose, CA

Hi,

I am trying to call a pl/sql procedure from a form, i want the form to unbind
after it makes the call to the pl/sql procedure as the procedure is huge and
takes a lot of time to execute, for this i want to call the procedure in a
DBMS_JOB.Please can you illustrate how to go about doing the same.(lets assume
that the procedure needed to be invoked is temp(a,b)).


Followup: 
declare
   l_job number;
begin
   dbms_job.submit( l_job, 'temp( ' || a || ',' || b || ');' );
   commit;
end;

is one way to do it.

If you plan on running temp lots with different inputs do this instead:

create table temp_parms( job_id number primary key, a number, b date );

declare
   l_job number;
begin
   dbms_job.submit( l_job, 'temp( JOB );' );
   insert into temp_parms( l_job, a, b );
   commit;
end;


and have TEMP query temp_parms by job_id -- which we passed in -- to get its
parameters (makes better use of binding and that is important)
[@more@]