PL/SQL Oracle 11g Record Groups

Question

Upgrading from 6i to Oracle 11g Fusion Middleware. How can a record group be passed from a form to the report? We were using DATA_PARAMETER but I understand that that is no longer an option.

Duckworth

Assuming you are talking about a record group in Oracle Forms, wouldn't it just be easier to copy the sql used to generate this group into the Reports parameter. Then you could pass any parameters for the record group from forms using bing variables - see docs.oracle.com/cd/E23943_01/bi.1111/b32122/… – Trier

it looks like just adding a bind variable to a query in Oracle reports e.g. :parm1 will prompt for a parameter to be created and added to the parameter form – Trier

Answer 1

Here's an example code to invoke a report refer to the for loop. It's kind of an example breaking your parameter and passing it to run_report_object.

https://www.oracle.com/technetwork/developer-tools/forms/frmrepparamform-128021.pdf

PROCEDURE RUN_REPORT_OBJECT_PROC(
report_id REPORT_OBJECT,
report_server_name VARCHAR2,
report_format VARCHAR2,
report_destype_name NUMBER,
report_file_name VARCHAR2,
report_otherparam VARCHAR2,
reports_servlet VARCHAR2) IS
report_message VARCHAR2(100) :=’’;
rep_status VARCHAR2(100) :=’’;
vjob_id VARCHAR2(4000) :=’’;
hidden_action VARCHAR2(2000) :=’’;
v_report_other VARCHAR2(4000) :=’’;
i number (5);
c char;
c_old char;
c_new char;
BEGIN
 VHWWLQJ 5HSRUWV UXQWLPH SDUDPHWHUV
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE,
SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME,
report_file_name);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER,
report_server_name);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE,
report_destype_name);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT,
report_format);
 FUHDWLQJ VWULQJ IRU SIDFWLRQ SDUDPHWHU
hidden_action := hidden_action ||’&report=’||
GET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME);
hidden_action := hidden_action||’&destype=’||
GET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE);
hidden_action := hidden_action||’&desformat=’||
GET_REPORT_OBJECT_PROPERTY (report_id,REPORT_DESFORMAT);
hidden_action := hidden_action ||’&userid=’
||get_application_property(username)||’/’||
get_application_property(password)||’@’||
get_application_property(connect_string);
c_old :=’@’;
FOR i IN 1..LENGTH(report_otherparam) LOOP
c_new:= substr(report_otherparam,i,1);
IF (c_new =’ ’) THEN
c:=’&’;
ELSE
c:= c_new;
END IF;
-- eliminate multiple blanks
IF (c_old =’ ’ and c_new = ’ ’) THEN
null;
ELSE
v_report_other := v_report_other||c;
END IF;

c_old := c_new;

hidden_action := hidden_action ||’&’|| v_report_other;
 

hidden_action := reports_servlet||’?_hidden_server=’||report_server_name
|| encode(hidden_action);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_OTHER,’pfaction=’||
hidden_action||’ ’||report_otherparam);
-- run Reports
report_message := run_report_object(report_id);
rep_status := report_object_status(report_message);
IF rep_status=’FINISHED’ THEN
vjob_id :=substr(report_message,length(report_server_name)+2,length
(report_message));
WEB.SHOW_DOCUMENT(reports_servlet||’/getjobid’||vjob_id||’?server=’||
report_server_name,’ _blank’);
ELSE
--handle errors
null;
END IF;
END;

Anthelmintic