Calling Oracle 10g Reports from Forms

Following steps will describe a simple method of calling reports from form on application server 10g.

Create a Report

create a simple employee report in oracle 10g report (e.g employee.rdf) with a parameter (e.g p_empno).

Create a Form

create a simple parameter form in oracle 10g forms and do the following steps.

  1. create a field (i.e v_empno) on form for passing parameter value.
  2. create a report in oracle forms using object navigator under reports menu. give it a name i.e testrep
  3. create following functions/procedures in form\'s program unit

i) ENCODE function to pass formatted url string to server

FUNCTION ENCODE (URL_PARAMS_IN Varchar2) RETURN VARCHAR2 IS
v_url VARCHAR2(2000) := URL_PARAMS_IN; -- Url string
	v_url_temp VARCHAR2(4000) :=''; -- Temp URL string
	v_a VARCHAR2(10); -- conversion variable
	v_b VARCHAR2(10); -- conversion variable
	c CHAR;
	i NUMBER(10);
BEGIN
	FOR i IN 1..LENGTH(v_url) LOOP
	c:= substr(v_url,i,1);
	IF c in (';', '/','?',':','@','+','$',',',' ') THEN
		v_a := ltrim(to_char(trunc(ascii(substr(v_url,i,1))/16)));
		IF v_a = '10' THEN v_a := 'A';
		ELSIF v_a = '11' THEN v_a := 'B';
		ELSIF v_a = '12' THEN v_a := 'C';
		ELSIF v_a = '13' THEN v_a := 'D';
		ELSIF v_a = '14' THEN v_a := 'E';
		ELSIF v_a = '15' THEN v_a := 'F';
		END IF;

		v_b := ltrim(to_char(mod(ascii(substr(v_url,i,1)),16)));
		

		IF v_b = '10' THEN v_b := 'A';
		ELSIF v_b = '11' THEN v_b := 'B';
		ELSIF v_b = '12' THEN v_b := 'C';
		ELSIF v_b = '13' THEN v_b := 'D';
		ELSIF v_b = '14' THEN v_b := 'E';
		ELSIF v_b = '15' THEN v_b := 'F';
		END IF;
		v_url_temp := v_url_temp||'%'||v_a||v_b;
	ELSE
		v_url_temp :=v_url_temp||c;
	END IF;
	END LOOP;
return v_url_temp;
END;

ii) RUN_REPORT_OBJECT_PROC procedure to set the report server object property and pass the parameter to web browser.

PROCEDURE RUN_REPORT_OBJECT_PROC (
				report_id REPORT_OBJECT,
				report_server_name VARCHAR2,
				report_format VARCHAR2,
				report_destype_name NUMBER,
				report_file_name VARCHAR2,
				reports_servlet VARCHAR2,
				report_paramlist ParamList) 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
	
	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);


	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);

	hidden_action := reports_servlet||'?_hidden_server='||report_server_name || encode(hidden_action);

	SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_OTHER,'pfaction='||hidden_action);

	/* To run Reports */
	report_message := run_report_object(report_id,report_paramlist);
	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
		null;
	END IF;
END;

iii) CALL_REPORT_OBJECT procedure to call the desire report.

PROCEDURE Call_Report_Object (p_repname Varchar2, p_repformat Varchar2, p_heading Varchar2) IS

 report_id	REPORT_OBJECT;
 pl_id 		PARAMLIST;
 pl_name 	VARCHAR2(10) := 'blank';
 
 v_repname	VARCHAR2(100);
 v_repserver	VARCHAR2(200);
 v_repserverip	VARCHAR2(200);	

BEGIN
 pl_id := Get_Parameter_List(pl_name);
 IF Id_Null(pl_id) THEN   
     pl_id := Create_Parameter_List(pl_name);
		
 /* Report Default Paramter */
 add_parameter(pl_id,'PARAMFORM',text_parameter,'NO');
 add_parameter(pl_id,'MAXIMIZE',text_parameter,'YES');
 /* Report Specific Paramter */
 add_parameter(pl_id,'P_HEADING',text_parameter,p_heading);
 add_parameter(pl_id,'P_EMPNO',text_parameter,:v_empno);
  	
END IF; 
	
v_repname     := p_repname;  -- absolute path of your app server drive e.g c:\myapp\employee.rdf
v_repserver   := 'rep_MYSERVER_FRHome1'; -- name of your report server
v_repserverip := 'http://100.10.1.0:7778/reports/rwservlet'; -- IP with port of your application server report serverlet
/* Find the id of the Reports Object in Forms */
report_id:=FIND_REPORT_OBJECT('testrep'); /* testrep you create in form object navigator*/
	
RUN_REPORT_OBJECT_PROC( report_id,v_repserver,p_repformat,CACHE,v_repname,v_repserverip, pl_id);
	Destroy_Parameter_List(pl_id); 
exception
	when others then 
	message('Default Message: '||sqlerrm);
	
END;

4) create a button on form for calling your employee.rdf report. Write the following code on WHEN-BUTTON-PRESS trigger

CALL_REPORT_OBJECT ('c:\myapp\eployee.rdf','PDF','Employee Records');

Run your form in browser in call the report from button