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)).

Hi Tom,

under what conditions is the JOB keyword replaced by the actual job id? I have not been able to find documentation to cover this.

The test below seems to indicate that it's multiple times, selective, and there's some magic in there!

Note that in the dbms_job the INSERT SELECT statement refers to column JOB in the WHERE clause but that does not get replaced otherwise we would have more than two rows in test1; the last statement shows that there are 12 other jobs for this user.

NPIVS@MDMD.WORLD> create table test1 (job_id number, text varchar2(4000));

Table created.

NPIVS@MDMD.WORLD> get afiedt.sql
1 declare l_job_id number := NULL;
2 begin
3 dbms_job.submit (l_job_id,
4 'DECLARE l_job NUMBER := JOB;' ||
5 'BEGIN ' ||
6 'INSERT INTO TEST1 (job_id, text) SELECT JOB, SUBSTR(WHAT, 1, 4000 - 39) || '' '' || JOB FROM USER_JOBS WHERE JOB = l_jo
b;' ||
7 'INSERT INTO TEST1 (job_id, text) values (JOB, ''The job id is '' || JOB);' ||
8 'COMMIT;' ||
9 'END;');
10* end;
NPIVS@MDMD.WORLD> /

PL/SQL procedure successfully completed.

NPIVS@MDMD.WORLD> commit;

Commit complete.

NPIVS@MDMD.WORLD> -- Some time passes
NPIVS@MDMD.WORLD> -- Check job is complete
NPIVS@MDMD.WORLD> select job, what, this_date, next_date, failures from user_jobs where what like 'DECLARE l_job %';

no rows selected

NPIVS@MDMD.WORLD> -- Job has completed so it had no failures
NPIVS@MDMD.WORLD> select * from test1;

JOB_ID
----------
TEXT
------------------------------------------------------------
1585450
DECLARE l_job NUMBER := JOB;BEGIN INSERT INTO TEST1 (job_id,
text) SELECT JOB, SUBSTR(WHAT, 1, 4000 - 39) || ' ' || JOB
FROM USER_JOBS WHERE JOB = l_job;INSERT INTO TEST1 (job_id,
text) values (JOB, 'The job id is ' || JOB);COMMIT;END; 1585
450

1585450
The job id is 1585450


NPIVS@MDMD.WORLD> select count(*) from user_jobs where 45 = 45;

COUNT(*)
----------
12

NPIVS@MDMD.WORLD>

Okay thanks for that.

I had incorrectly thought that it was some sort of string substitution but your reference indicates it is the standard PL/SQL re-writing of SQL statements to use bind variables.

Cheers
Dany

I understand that dbms_jobs are executed as anonymous pl/sql blocks. There is a claim being made in our team
that these anonymous pl/sql blocks are being parsed
again and again. Following is an example

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN pkg1.procedure1();
:mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0;
END IF; END;

Is there anything wrong with the way we are using
dbms_job? Any other comments?

Thanx!

Yeah, in my experiments, I have already noticed that
the subsequent parses are soft...I think it is just what
is happenning in the job that is taking time. Then
someone sees in top sql an anonymous block - euqtes it
to multiple parsing and files a bug. But I just wanted
to make sure with you that we are not doing anything
wrong here..

Thanx!

When executed select * from jobs$
after migration of the 8.1.7.4 - 64bits database to 9.2.0.5.0 - 64bit
it throws me an error 'ORA-00932' What can i do to sovle this..?

Yet another example of Oracle docs were insufficient

Hi Tom

Is there any way to pass parameter in dbms_job.submit without having parameter table? I am getting this error and I did find some where you showed an example but cant find it now.

lkondal@ORA9i> DECLARE
2 n NUMBER;
3 p_userlogin varchar2(15) := 'lk';
4 BEGIN
5 dbms_job.submit(n,
6 'P_Exp_Imp2.call_Exp( '|| p_userlogin||');' ,
7 SYSDATE);
8 COMMIT;
9 END;
10 /
DECLARE
*
ERROR at line 1:
ORA-06550: line 1, column 114:
PLS-00201: identifier 'LK' must be declared
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 5


lkondal@ORA9i>

I tried with various combination of quotes and || but all resulted in error or insteat of passing value of p_userlogin it goes as p_userlogin.

Whats the concept/rule/way to pass string in parameter?
Thanks.

Regards.

Hi Tom

Thanks a lot and it works and I now know what is it.

Thanks and Regards.

Hello Tom,

The technique of submitting a job together with inserting values into a parameter table seems very efficient for small paramters.
How would you pass an array of lets say 1.000.000 primary keys to a process submitted by a job. We have a "manager" procedure that runs a query that returns 5.000.000 primary keys to delete. We would like to do this in parallel with 5 processes submitted with jobs. The same technique can be applied if we want to delete from several tables at the same time, pass a list of 1.000.000 rows and a table name to a job and let it run on its own. If the procedures was called in the same session we would pass the list as an array with nocopy, but given that the what of the job is a string that doesn't quite work.
How would you accomplish something like that(besides using pipes)?

Thank you very much,

Hello Tom,

Using a table would mean writting everything back to disk. I was hoping we can avoid that somehow. Is that possible?

Tom,
I read your first reply and you say:

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

My question is why would you INSERT into the parameter table AFTER submitting the job? Shouldn't you be doing that BEFORE submitting the job? I understand that you still need to COMMIT before the job runs. But any logical reason why the INSERT should be done afterwards?

Thank you.

I understand why you are doing it that way. You need the JOB value to pass to the INSERT statement.

Tom,

I use DBMS_JOB.SUBMIT from a database trigger to call a package and to pass a barcode_no value as a VARCHAR2 parameter to that package. The parameter has values like `00000026¿, or `00057¿.
However, the package always receives value like `26¿ or `57¿ which is not what I need. I need the same value I passed in.
Everything in the trigger and the package declared as VARCHAR2.
Is is Oracle bug?

Hi Tom,

In your first answer to the question you use an example with parameter table. Since I want to keep the record inserted there for history purposes along with some more useful information gathered during the execution, could I rely that DBMS_JOB.SUBMIT will always return unique values of the JOB parameter that is passed. By always I mean really always - the same job will be started multiple times maybe for years, until dismissed :-).

SELECT JOBSEQ.NEXTVAL 
FROM
 DUAL

Hi there!
I have a similar problem:
I have a procedure with a VARCHAR2 table as parameter. I send VARCHAR2 table from java to this procedure and it works fine, but i need create a job, which run the same procedure with the same VARCHAR2 parameter. How to do this.

I have:

CREATE OR REPLACE TYPE "VARCHAR2_TABLE" IS TABLE OF VARCHAR2(300)

PROCEDURE PPP1(param1 IN VARCHAR2_TABLE) IS
BEGIN
...
END;

and i need to create procedure, creating the job from the interface like that:

PROCEDURE JOB_PPP1(param1 IN VARCHAR2_TABLE) IS
BEGIN
SYS.dbms_scheduler.create_job(
job_name => 'JOB_NAMED_PPP'
,job_type => 'PLSQL_BLOCK'
,program_name => 'PPP1'
,number_of_arguments => 1
,start_date => SYSTIMESTAMP
,repeat_interval => NULL
,enabled => TRUE
,comments => 'JOB Procedure PPP1'
);

SYS.dbms_scheduler.SET_JOB_ARGUMENT_VALUE ('JOB_NAMED_PPP', 1, param1 );
COMMIT;
END;


Thanks for helping

Tom,

Is there a way to retrieve the JOB's LOG_ID in a procedure scheduled using dbms_scheduler ?

Thanks

BC

I have to do multi threading inside a package for which i am using DBMS_SCHEDULER.RUN_JOB. can any one help me with how to call the same.

I know we are not supposed to be using DBMS_JOB in 19c, but we support a legacy application where nothing much is allowed to be changed.

Interestingly, we are getting "wrong number of arguments in call to JOB" when we try this.

l_job number default job;

Any thoughts, Tom?

This doesn't work either...

dbms_job.submit( l_job, 'temp( JOB );' );

l_job and JOB do not have the same value in 19c!