Friday 14 June 2013

Running a PL/SQL Process from Column Link

I recently had the problem where I needed to run a pl/sql process after clicking a column link. To run this process I needed a value from the table. I then needed to navigate to a new page and set an item with a value calculated in the process. Below is my solution. If you have a simpler solution please leave me a comment.

First create an intermediate page where you will run your process.
- create a blank page (for this example we will call this process page)
- create a html region on this page
- create a hidden item to receive the value from the table.

- go to your page with the report (we will call this report page)
- create a column link to process page that passes the value from the report to your hidden item on process  page

- go back to process page
- create a Before Header process
- add code to calculate result, send the result value to an item on the target page (we will use 20 as target page and P20_RESULT as target item). Then redirect to target page

example code:

DECLARE
v_result number; --calculated value will go here
BEGIN
--Code goes here to calculate result

--set the value on the target page
APEX_UTIL.SET_SESSION_STATE('P20_RESULT',v_result);

-- redirect to target page
htp.init;
owa_util.redirect_url('f?p=&APP_ID.:20:&SESSION.::NO:::');
apex_application.stop_apex_engine;

END;

No comments:

Post a Comment