We help IT Professionals succeed at work.

Oracle Reports 6i

byomite asked
Medium Priority
Last Modified: 2013-12-12
I would highly appreciate it if someone can advise me on how to call a stored procedure from reports 6i;
The procedure i am trying to call has 2 IN parameters and 6 OUT parameters.
 I tried using a ref cursor but I ran into problems with the return statement in the cursor.
esp. the stored procedure has to return data based on the dates calculated with add_months i.e return groups based on all months for the past 12 months from sysdate.
The problem is when the first date is passed in, the report stops, 'cos of the return statement in the refcur.
Watch Question

The following steps allow a report to call a procedure with a date...and retrieve the date of a month later

1. In SQL*Plus, create the procedure with:
create procedure p_report6i_proc (date_in  IN OUT date,
                                  date_out IN OUT date) IS
  date_out := add_months(date_in,1);

-- 2. In Reports61, create a formula column of type DATE with the following PL/SQL:
function CF_1Formula return Date is
  v_date_in  DATE := :report_column;
  v_date_out DATE;
  p_report6i_proc (v_date_in, v_date_out);
  return (v_date_out);
Author of the Year 2009

Hi byomite,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days.  I will suggest to:

    Accept Softchoice's answer.

byomite, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will followup.

Please do not accept this comment as an answer!

EXPERTS: Your input for closing recommendations are REQUESTED.
DanRollins -- EE database cleanup volunteer
Force accepted

** Mindphaser - Community Support Moderator **

Explore More ContentExplore courses, solutions, and other research materials related to this topic.