Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Passing date parameter into anomynous pl/sql block in APEX (Oracle Apex 3.0,1)
Passing date parameter into anomynous pl/sql block in APEX [message #508889] Tue, 24 May 2011 21:33 Go to next message
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 Go to previous messageGo to next message
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.
Re: Passing date parameter into anomynous pl/sql block in APEX [message #509084 is a reply to message #508899] Wed, 25 May 2011 22:13 Go to previous message
komasi75
Messages: 3
Registered: May 2011
Location: new zealand
Junior Member
Thanks for the advice. It's much appreciated. Yep pretty rookie move not realising I was trying to pass APEX items within a string.

I'm new to APEX, so was recycling code from another report.

Thanks again

[Updated on: Wed, 25 May 2011 22:14]

Report message to a moderator

Previous Topic: Differentiating apache calls vs program references
Next Topic: apex report error: ORA-01403: no data found
Goto Forum:
  


Current Time: Fri Aug 07 12:54:06 CDT 2020