OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » Passing date parameter into anomynous pl/sql block in APEX

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Passing date parameter into anomynous pl/sql block in APEX (Oracle Apex 3.0,1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 13 20:20:07 CDT 2025