| Passing date parameter into anomynous pl/sql block in APEX [message #508889] |
Tue, 24 May 2011 21:33  |
 |
komasi75
Messages: 3
Registered: May 2011
Location: new zealand
|
Junior Member |
|
|
I have a report that groups 'user action' into 'action type' (ie. Add, Update, Delete) that is generated when a 'PROCESSES' which has a PL/SQL anonmynous block as it source, is kicked off.
I've sinced amended the PL/SQL package, that the APEX report calls, to include "Start Date" and "End Date" parameters. I can run this package using SQL PLUS;
SQL> begin
2 dp_monthly_test_pkg.kick_off_run(P_START_DATE => '01-JAN-2011', P_END_DATE => '10-APR-2011');
3 end;
4 /
PL/SQL procedure successfully completed.
In my APEX report I have added 2 'ITEMS' to cater for the new parameters (P46_START_DATE and P46_END_DATE), but when I include these parameters in the 'PROCESSES' (anomynous block), nothing happens;
DECLARE
v_job NUMBER;
BEGIN
DBMS_JOB.SUBMIT
( v_job, 'BEGIN
DP_MONTHLY_TEST_PKG.KICK_OFF_RUN(P_START_DATE => :P46_START_DATE,
P_END_DATE => :P46_END_DATE);
END;',SYSDATE);
COMMIT;
END;
Can anyone help please?
[Updated on: Tue, 24 May 2011 21:35] Report message to a moderator |
|
|
|
| Re: Passing date parameter into anomynous pl/sql block in APEX [message #508899 is a reply to message #508889] |
Wed, 25 May 2011 02:35  |
c_stenersen
Messages: 255
Registered: August 2007
|
Senior Member |
|
|
The problem isn't in your package. It's in your job creation. What you're giving in to the second parameter of the dbms_job.submit call is a string. The job will try to run what you give in, but it will be run separately from ApEx, so it won't know anything about ApEx page items or the values of them (or any other variables etc in the block) when creating the job. What you could do is put these dates directly into the text for this job. (But then you have to validate these two items to make sure they contain valid dates, or you are vulnerable for sql injection)
And in ApEx you don't need to commit. That is done for you unless some errors happen
But why do you need to run this as a job at all? You want to run the code right away, and you don't want it to repeat, am I correct? Then you just put your call to the package directly into the pl/sql anonymous block, and you can use the items as normal.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
