Home » SQL & PL/SQL » SQL & PL/SQL » Struggling with DBMS_JOB.SUBMIT syntax (11g)
Struggling with DBMS_JOB.SUBMIT syntax [message #608317] Tue, 18 February 2014 10:39 Go to next message
bristolsmithy
Messages: 3
Registered: February 2014
Junior Member
Hi,

I'm a bit of a pl/sql newby and have a need to use a DBMS_JOB.SUBMIT procedure in a procedure thatI've built, however, I'm really struggling with the syntax. Basically, I need to just submit a single occurance of the job as a one off and I need to pass a single variable as a parameter with the call. I've taken out the extrenuous bits, but here's my code:
procedure TEST_ANDY is
   v_count_active number;
   V_CONTEXT_ID NUMBER;
INT_JOB_ID NUMBER := NULL;
   others EXCEPTION;
BEGIN
Select pack_install.get_context_id() into v_context_id from dual;
select count(*) into v_process_profile_count from RCT_ODR_QUEUE;
exception when no_data_found then null;
END;
IF v_process_profile_count > 0
THEN
BEGIN
BEGIN
     DBMS_JOB.SUBMIT (
         job => INT_JOB_ID,
          what => 'HSBC_PACK_ONDEMAND_REPORTING.odr_activate_next_workflow(' ||v_context_id|| ');',
          next_date => SYSDATE,
          interval => INT_JOB_ID 
     );
     COMMIT;
END;
	exception when no_data_found then null;
	END;
	ELSE
	END IF;
	pack_log.log_end;
exception when others then
  raise_application_error(-20000, sqlerrm);
END;


Apologies for the noddyness of my code/question.. Would appreciate any help though,

Thanks,
Re: Struggling with DBMS_JOB.SUBMIT syntax [message #608319 is a reply to message #608317] Tue, 18 February 2014 10:59 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Three things that catch my eye:

1) If it is a "run once" job, then interval should be set to NULL.
2) remove the "exception when others" part it does absolutely nothing useful besides hiding the possible real errors that happen
3) the code would be easier to read and to understand if the begin/end/if/end if etc... parts were properly indented.

Re: Struggling with DBMS_JOB.SUBMIT syntax [message #608324 is a reply to message #608319] Tue, 18 February 2014 11:58 Go to previous messageGo to next message
bristolsmithy
Messages: 3
Registered: February 2014
Junior Member
Hi, thanks for the pointers. OK, taking your advice on board, I tried hard coding the DBMS_JOB Submit like this
BEGIN
     DBMS_JOB.SUBMIT (
         job => null,
          what => 'HSBC_PACK_ONDEMAND_REPORTING.odr_activate_next_workflow(388);',
          next_date => SYSDATE,
          interval => null 
     );
     COMMIT;
END;


However, this gave me a failure:
Quote:
ORA-06550: line 3, column 10:
PLS-00363: expression ' NULL' cannot be used as an assignment target
ORA-06550: line 2, column 6:
PL/SQL: Statement ignored

BEGIN
DBMS_JOB.SUBMIT (
job => null,
what => 'HSBC_PACK_ONDEMAND_REPORTING.odr_activate_next_workflow(388);',
next_date => SYSDATE,
interval => null
);
COMMIT;
END;


I also tried re-writing my main procedure following the rest of your advise:
procedure TEST_ANDY is
V_CONTEXT_ID NUMBER;
INT_JOB_ID NUMBER := NULL;
others EXCEPTION;
BEGIN
		Select pack_install.get_context_id() into v_context_id from dual;
		DBMS_JOB.SUBMIT (
		job => INT_JOB_ID,
		what => 'HSBC_PACK_ONDEMAND_REPORTING.odr_activate_next_workflow(' ||v_context_id|| ');',
		next_date => SYSDATE,
		interval => null 
	     );
     COMMIT;
END;


Now here's the strange thing, it does call the procedure, but the procedure then doesn't run properly, I beleieve it may be an issue with the parameter I'm passing, I've I run this directly from a command window and it works fine:
exec HSBC_PACK_ONDEMAND_REPORTING.odr_activate_next_workflow(388);
Re: Struggling with DBMS_JOB.SUBMIT syntax [message #608332 is a reply to message #608324] Tue, 18 February 2014 15:15 Go to previous message
bristolsmithy
Messages: 3
Registered: February 2014
Junior Member
Ah ha.. Figured it out... My syntax was actually fine, issue was that the procedure being called required me to stipulate a partition before being run.
Previous Topic: Oracle Pl/SQL Case Statement
Next Topic: find latest ddl statement happened in table
Goto Forum:
  


Current Time: Mon Sep 22 11:53:06 CDT 2014

Total time taken to generate the page: 0.15103 seconds