How to view Oracle Reports in Web?

[sivanu]

Hi,
I am new for web publishing Oracle Report. i have created a report in Oracle Reports 6i. I know that we have an applicaiton server and Oracle 9iAS has been installed in it. Also we have a web application written in JSPs and Java Beans and i need to call this report from that application when the user clicks a button. From the web browser how will i access my Oracle Report?. Cna some one help me out with the procedure to be followed so that i can view my report in web browser. I need not publish it in JSP. I need to display it as a pdf.

This is an urgent requirement. So, i request someone to help me asap with this p[roblem.

Thanks in advance

 

[HimanB]

Hi,
In order to achive this you need to have have a Directory on which you have Read/Write permissions on your Server.
The Physical path of this Directory will map to variable
L_Local_Path in the code below.
Then you have to give a Virtual name to it in your Middletier and this Path will map to L_Virtual_Path.
We have a Form ALSD9998 through which we can specify File name ,User specified report titles and Order by clause.
So you must also be having similar sort of Form wehre you will allow your user to specify File name etc.
In order to view this report you will have to Prepare the URL

Remember you cannot view the report in the Browser using this code.In order to view this report prepare the URL in this procdure itself as done by us in varaible Var.Url_Nm and then using WEB.SHOW_DOCUMENT execute this URL from some button on the screen.
Do not try to execute this URL from this Procedure itself as RUN_PRODUCT and WEB.SHOW_DOCUMENT cannot tell you if the execution of report is completed or not.
This can be acived using RECORD_REPORT_OBJECT but we have not used it as it has some more restrictions like maximum lenght of varibale cannot be more than 255,Record groups cannot be passed usin it etc.

Regards
Himanshu


/***
** Name : RECORD_REPORT
** Parameter : None
** Usage : Sends information to the Driver form,
** Called As : RECORD_REPORT
** Called from : Key_Print
** Calls : Template.Show_Error
***/
Procedure RECORD_REPORT IS
L_qry_where Varchar2 (2000); /* Where Clause */
L_qry_order_by Varchar2 (2000); /* Order_by Clause */
L_qry_startw Number (5) := 0; /* Start position of Where clause */
L_qry_endw Number (5) := 0; /* Store end position of where clause */
L_qry_lengthw Number (5) := 0; /* Store length of where clause */
L_qry_starto Number (5) := 0; /* Start position of Order_by clause */
L_fm_nm Paramlist; /* Parameter list id */
L_qs Varchar2 (10) := 'QS'; /* Parameter list name */
L_Rp_Nm Paramlist; /* Parameter list id */
L_qr Varchar2 (10) := 'QR'; /* Parameter list name */
L_Dynamic_sort Varchar2(1) := 'N'; /* Dynamic sort parameter */
L_Report_Name Varchar2(50);
report_id Report_Object;
report_message VARCHAR2(100);
vc_report_job_id VARCHAR2(100); /* unique id for each Report request */
vc_rep_status VARCHAR2(20); /* status of the Report job */
L_DESNAME VARCHAR2(200):=NULL;
L_url VARCHAR2(500):=NULL;
L_rep_string VARCHAR2(1000):=NULL;
L_File_Name VARCHAR(200) := NULL;
L_Local_Path VARCHAR(200) := NULL;
L_Virtual_Path VARCHAR(200) := NULL;

Begin
Begin
If :ALS_REPRINTING_LICENSES_BLK.Aict_Item_Type_Cd = '2009004' And
:ALS_REPRINTING_LICENSES_BLK.AOTT_OPP_TYPE_RESTRICTION = 'Y' Then
L_Report_Name := 'ALSR4410';
Else
L_Report_Name := ALS.ALS_PACKAGE.GET_PVAL('REPLACE REPORT NAME',:ALS_REPRINTING_LICENSES_BLK.Aict_Item_Type_Cd);
End If;
Exception
When Others Then
Message('E: Error while fetching values from ALS_MISC table for KEY1 =''REPLACE REPORT NAME'' and KEY2= '||''''||:ALS_REPRINTING_LICENSES_BLK.Aict_Item_Type_Cd||''''||'.',No_acknowledge);
Raise Form_Trigger_failure;
End;
Begin
L_Virtual_Path := Lower(ALS.ALS_PACKAGE.GET_PVAL('WEB REPORT PATH','VIRTUAL PATH'));
Exception
When Others Then
Message('E: Error while fetching values from ALS_MISC table for KEY1 =''WEB REPORT PATH'' and KEY2= '||'VIRTUAL PATH'||'.',No_acknowledge);
Raise Form_Trigger_failure;
End;
Begin
L_Local_Path := Lower(ALS.ALS_PACKAGE.GET_PVAL('WEB REPORT PATH','PHYSICAL PATH'));
Exception
When Others Then
Message('E: Error while fetching values from ALS_MISC table for KEY1 =''WEB REPORT PATH'' and KEY2= '||'PHYSICAL PATH'||'.',No_acknowledge);
Raise Form_Trigger_failure;
End;

/***
** Calling ALSD9998 for Dynamic Sort and to specify Report Format and Name if it to be generated as a FILE(PDF,TXT etc.)
***/
L_fm_nm := Get_Parameter_List(L_Qs);
If not id_null(L_Fm_Nm) Then
Destroy_parameter_list(L_Fm_Nm);
End if;
L_fm_nm := Create_Parameter_List(L_Qs);

Add_parameter(L_Fm_Nm, 'REPORT_NAME', Text_Parameter,L_Report_Name);
/***
** Set the value of L_Dynamic_sort as 'Y' for Dynamic sorting.
***/
L_Dynamic_sort := 'Y';
Add_parameter(L_Fm_Nm, 'DYNAMIC_SORT', Text_parameter, L_Dynamic_Sort);

Set_Application_Property(Cursor_Style,'DEFAULT');
Call_Form('ALSD9998', HIDE, NO_REPLACE, QUERY_ONLY, L_Fm_nm);
Template.Check_Failure;
Destroy_Parameter_List(L_Fm_Nm);
If :Global.Order_By1 is Not NULL Then
L_Qry_Order_By := :Global.Order_By1||:Global.Order_By2||:Global.Order_By3||:Global.Order_By4;
End If;

/***
** Calling Reports.
***/
If :Global.Print_Flag = 'Y' Then
L_Rp_nm := Get_Parameter_List(L_Qr);
If not id_null(L_Rp_Nm) Then
Destroy_parameter_list(L_Rp_Nm);
End if;
L_Rp_nm := Create_Parameter_List(L_Qr);


L_Qry_Where := 'WHERE '||Var.Where_Clause;
L_Qry_Where := Translate(L_Qry_Where,'''','~');
L_qry_where := translate(L_Qry_Where,' ','^');
L_Qry_Order_By := Translate(L_Qry_Order_By,' ','~');
:Global.Report_Title:=TranslateGlobal.Report_Title,'"','`');
:Global.Report_Title:=TranslateGlobal.Report_Title,'''','~');

Add_parameter(L_Rp_Nm, 'P_Query', Text_Parameter, L_Qry_Where);
Add_parameter(L_Rp_Nm, 'P_Ordby', Text_parameter, L_Qry_Order_By);
Add_parameter(L_Rp_Nm, 'Desname', Text_Parameter, :Global.Dest_Name);
Add_parameter(L_Rp_Nm, 'Desformat', Text_Parameter, 'PDF');
Add_parameter(L_Rp_Nm, 'P_Report_Title', Text_Parameter, :Global.Report_Title);
Add_parameter(L_Rp_Nm, 'Destype', Text_Parameter, :Global.Dest_Type);
Add_parameter(L_Rp_Nm, 'Orientation', Text_Parameter,:Global.Orientation);
Add_Parameter(L_Rp_Nm, 'PARAMFORM', TEXT_PARAMETER, 'NO');

Set_Application_Property(Cursor_Style,'DEFAULT');
If Get_Application_Property(User_Interface) = 'WEB' Then



/*** Prepare URL to run report on Web ***/
If :Global.Dest_Type<>'FILE' Then
L_rep_string := :Global.Server_Name||':'||:Global.Port||:Global.CGI_Dir||:Global.CGI_Exec||
'?server='||:Global.Report_Server||'&report='||L_Report_Name||'.rdf&userid='||
:Global.User_Name||'/'||:Global.User_Pwd||'@'||:Global.Database_Name||
'&P_Report_Name='||L_Report_Name||'&P_Query='||L_Qry_Where||'&P_Ordby='||L_Qry_Order_By||
'&Desformat='||:Global.Dest_Format||'&Orientation=LANDSCAPE&Destype='||:Global.Dest_Type||
'&P_Report_Title='||:Global.Report_Title||'&Desname='||:Global.dest_name;
Web.Show_Document(l_rep_string);
Else

/*** REMOVE IF THERE IS '.PDF' STRING FROM DEST NAME***/
IF INSTRGlobal.dest_name,'.PDF') > 0
THEN
:Global.dest_name := SUBSTRGlobal.dest_name,1,INSTRGlobal.dest_name,'.PDF')-1);
END IF;
/*** REMOVE IF THERE IS 'C:\TEMP\' STRING FROM DEST NAME***/
IF INSTRGlobal.dest_name,'C:\TEMP\') > 0
THEN
:Global.dest_name := LTRIM(RTRIM(REPLACEGlobal.dest_name,'C:\TEMP\','')));
END IF;


L_File_Name:=L_LOCAL_PATH||'\'||:Global.dest_name||'_'||USER||'.PDF';

L_rep_string := :Global.Server_Name||':'||:Global.Port||:Global.CGI_Dir||:Global.CGI_Exec||
'?server='||:Global.Report_Server||'&report='||L_Report_Name||'.rdf&userid='||
:Global.User_Name||'/'||:Global.User_Pwd||'@'||:Global.Database_Name||
'&P_Report_Name='||L_Report_Name||'&P_Query='||L_Qry_Where||'&P_Ordby='||L_Qry_Order_By||
'&Desformat='||:Global.Dest_Format||'&Orientation=LANDSCAPE&Destype='||:Global.Dest_Type||
'&P_Report_Title='||:Global.Report_Title||'&Desname='||L_File_Name;

Web.Show_Document(l_rep_string);



Var.Url_Nm := :Global.Server_Name||':'||:Global.Port||'/'||L_Virtual_Path||'/'||:Global.dest_name||'_'||USER||'.PDF';
IF GET_ITEM_PROPERTY('ALS_REPRINTING_LICENSES_BLK.RUN_REPORT',ENABLED) = 'FALSE' THEN
SET_ITEM_PROPERTY('ALS_REPRINTING_LICENSES_BLK.RUN_REPORT',ENABLED,PROPERTY_TRUE);
SET_ITEM_PROPERTY('ALS_REPRINTING_LICENSES_BLK.RUN_REPORT',NAVIGABLE,PROPERTY_TRUE);
END IF;

END IF;

Else
/* Modified by DS on 01/15/2003 to run report in asynchronous mode */
/* Modified by NC on 05/06/2003 to run report in synchronous mode , since report was failing in case Flag was Null , refer BUI 04/21/2003-04 */
Run_Product(REPORTS,L_Report_Name, synchronous, Runtime, Filesystem, L_Rp_Nm, NULL);
Clear_Message;
End If;
Destroy_Parameter_List(L_Rp_Nm);

GO_BLOCK('ALS_REPRINTING_LICENSES_BLK');
First_record;
Loop
If NvlARL_REP_PRINTED,'X') <> 'Y' Then
:ARL_REP_PRINTED := 'Y';
End If;
If :System.last_Record = 'TRUE' Then
Exit;
Else
Next_Record;
End If;
End loop;
Commit_Form;

If :Global.Dest_Type<>'FILE' Then
Message('I: Report successfully printed.',No_Acknowledge);
Else
Message('I: Report successfully printed. Press ''View Report'' to view the output. ',No_Acknowledge);
End If;
End If;

/***
** Enabling Menu Items
***/
Enable_Item('ACTION','CLEAR_ALL');
Enable_Item('EDIT','EDIT');
Enable_Item('QUERY','ENTER');
Enable_Item('QUERY','EXECUTE');
Enable_Item('QUERY','COUNT_HITS');
Enable_Item('BLOCK','CLEAR');
Enable_Item('RECORD','PREVIOUS');
Enable_Item('RECORD','NEXT');
Enable_Item('RECORD','SCROLL_UP');
Enable_Item('RECORD','SCROLL_DOWN');
Enable_Item('RECORD','CLEAR');
Enable_Item('FIELD','PREVIOUS');
Enable_Item('FIELD','NEXT');
Enable_Item('FIELD','CLEAR');
/***
** Exception Handling
***/
Exception
When Others Then
Template.Show_Error('T');
END;