| Struggling with DBMS_JOB.SUBMIT syntax [message #608317] |
Tue, 18 February 2014 10:39  |
 |
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  |
ThomasG
Messages: 3211
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 |
|
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); |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
