Link to home
Create AccountLog in
Avatar of pcurrier22
pcurrier22Flag for United States of America

asked on

unable to submit job w/o it breaking

I have a Procedure (p_POChange) that I need to run daily at 6a.  It runs fine manually, but I cannot successfully create a job to schedule it via dbms_job.submit.

'p_POChange' runs a number of queries and updates, then executes another Procedure (pEmail) that sends an email with the results.  'p_POChange' passes 5 parameters to 'pEmail', which is built to be a generic email sender used by various procs.

p_POChange is as follows:

create or replace procedure p_POChange
( Sender out varchar2
, Recipient out varchar2
, CCRecip out varchar2
, Subject out varchar2
, HtmlMsg out varchar2
) as
poHTML VARCHAR2(5000) default null;
BEGIN
  poHTML := poHTML || 'stuff';

  pEmail(Sender => 'Admin@company.com',
             Recipient => 'ab@company.com',
             CCRecip =>  'cd@company.com',
             Subject => 'POs changed.',
             HtmlMsg => poHTML);
END;

Open in new window


Again, this runs fine when executed manually, but when I try to create a job..

DECLARE
jobno NUMBER;
BEGIN
   sys.DBMS_JOB.SUBMIT(jobno, 
      'p_POChange();', 
      SYSDATE, 'trunc(SYSDATE) + 1 + 6/24');
   COMMIT;
END;

Open in new window


it results in...
Error starting at line 1 in command:
DECLARE
jobno NUMBER;
HtmlMsg varchar2(5000);
BEGIN
   sys.DBMS_JOB.SUBMIT(jobno,
      'p_POChange();',
      SYSDATE, 'trunc(SYSDATE) + 1 + 6/24');
   COMMIT;
END;
Error report:
ORA-06550: line 1, column 93:
PLS-00306: wrong number or types of arguments in call to 'p_POChange'
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
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I'm sure this has to do with passing a parameter(s) through with the Proc, but I can't figure it out at all.

I've tried plugging in a bind variable...and it will create the job, but it fails and eventually breaks.
VARIABLE jobno NUMBER
VARIABLE Msg varchar2(1000)
BEGIN
   sys.DBMS_JOB.SUBMIT(:jobno, 
      'p_POChange(:Msg);', 
      SYSDATE, 'trunc(SYSDATE) + 1 + 6/24');
   COMMIT;
END;

Open in new window


this is the message in the alert.log

Fri Dec 31 09:37:22 2010
Errors in file c:\oracle\admin\vmfg\udump\vmfg_j000_11972.trc:
ORA-12012: error on auto execute of job 104
ORA-01008: not all variables bound

Does anyone have any insight into what I'm missing here?  I feel like I'm close, but I've been looking at it too long without a fresh set of eyes.

Thank you very much in advance and Happy New Year!
Pat
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of pcurrier22

ASKER

Thanks for the fast reply...

I see what you're saying.  I guess I assumed that I needed an OUT parameter for each of the values I was passing to the 2nd procedure.  I'll give it a whirl Monday morning and post my results.

thanks again and Happy New Year.
Pat
That did the trick...  thanks a lot.