Skip to main content

Integration Apex and Oracle Report Server



Integration of Oracle Report server with Oracle Apex

Assuming that you have installed Report server and Weblogic Server

Weblogic Screenshot.

 

NLS_LANG=AMERICAN_AMERICA.AL32UTF8(used in our case for Arabic reporting optional)
REPORT_PATH= <PATH OF RDF File  location In weblogic server> (Required to known the path of RDF file)
TWO_TASK= <ORACLE_SID>(Optional)
 
Create  RDF report in Report Builder with Layout
I create a simple report for testing with Query as
SELECT * from EMP
Design the Layout and compile and transferred to Weblog Server as EMP.RDF under the path in my case is REPORT_PATH  in the Setting of Weblogic Server.

Test  if you are able to access report by running in non secure way
Replace above BOLD letter with appropriate parameter in your environment
Once you are able to access the report you can encapsulate this in PL/SQL or pass as parameter


1.       Create table for BLOB file using ORACLE developer or TOAD
  CREATE TABLE tbl_attach_file
(
  attach_id          NUMBER PRIMARY KEY,
  attach_data        BLOB,
  attach_mimetype    VARCHAR2(255),
  attach_filename    VARCHAR2(255),

  attach_last_update DATE ,
  attach_charset     VARCHAR2(128),
  attach_user        VARCHAR(10)
);
CREATE SEQUENCE SQ_ATTACH_FILE start with 1;

CREATE OR REPLACE TRIGGER tr_attach_file_BI
   BEFORE INSERT
   ON tbl_attach_file
   FOR EACH ROW
BEGIN
   SELECT SQ_ATTACH_FILE.NEXTVAL
     INTO :NEW.attach_id
     FROM DUAL;
END;
APEX PAGE 1
1.       Create Apex Page to view or download the Report output in PDF format

I have created as Interactive report with default template and setting
and query SQL statement as
Select ATTACH_ID,ATTACH_DATA, dbms_lob.getlength(ATTACH_DATA) attachment from tbl_attach_file;
a)Click report Region name
b)Go to Report Attribute ->Click on column ATTACHMENT and my setting is
Number / Date Format  = BLOB
and set below

1)      Create Procedure
create or replace procedure runReport
is
vReportURL       varchar2(255);
vBlobRef         blob;
vRequest         Utl_Http.req;
vResponse        Utl_Http.resp;
vData            raw(32767);
begin
vReportURL := <URL for Report server access that you used to test as stated above>
/* use pipe for concatenating for eg:'http://HOST:PORT/reports/rwservlet?server=REPORT_SERVR_NAME'||'&'||'report=EMPS.rdf'||'&'||'destype=CACHE'||'&'||'desformat=PDF'||'&'||'userid=USERID/PASSWORD@SID’;
*/
insert into tbl_attach_file (attach_data)
values( empty_blob() )
returning attach_data into vBlobRef;
vRequest := Utl_Http.begin_request(vReportUrl);
Utl_Http.set_header(vRequest, 'User-Agent', 'Mozilla/4.0');
vResponse := Utl_Http.get_response(vRequest);
loop
begin
Utl_Http.read_raw(vResponse, vData);
Dbms_Lob.writeAppend
 ( lob_loc => vBlobRef
 , amount  => Utl_Raw.length(vData)
 , buffer  => vData
 );
exception when utl_http.end_of_body then 
exit;
end;
end loop;
Utl_Http.end_response(vResponse);
owa_util.mime_header('application/pdf',false);
htp.p('Content-length: ' || dbms_lob.getlength(vBlobRef));
owa_util.http_header_close;
wpg_docload.download_file(vBlobRef);

end runReport;
APEX PAGE 2
Create a page Region Report001 and create a button
Create a Dynamic Action submit_report and Execute PL/SQL CODE EDIT  call the Procedure as :

Begin 
runreport;
end;

CLICK submit_report and set as below
 

Click on EDIT and call the Procedure runReport

RUN PAGE 2 -> Click Button -> Go to PAGE 1
You can see the Link download to download the PDF file

Click on download Button to download the PDF file






Comments

Popular posts from this blog

Adding Syntax Highlighting for SQL and PLSQL code in blogger

If you like to add syntaxhiglighting to your blogger follow the below steps:

1 - Click "Edit HTML" under your template settings
2- Add the following code just above your </header> closing tag:

<!-- INCLUDE SyntaxHighlighter Components (css and javascript) --> <link href='http://alexgorbatchev.com/pub/sh/current/styles/shCore.css' rel='stylesheet' type='text/css'/> <link href='http://alexgorbatchev.com/pub/sh/current/styles/shThemeFadeToGrey.css' rel='stylesheet' type='text/css'/> <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shCore.js' type='text/javascript'/> <!-- END INCLUDE SyntaxHighlighter Components --> <!-- INCLUDE YOUR BRUSHES WHICH YOU WANT TO USE HERE --> <script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushSql.js' type='text/javascript'/> <!-- END INCLUDE BRUSHES --> <!-- INIT SyntaxHighlighter --&…

UAE Oracle APEX User Group .. third meetup

Hi... we are very excited to announce the third meetup (http://www.meetup.com/UAE-Apex-Meetup) and this time with the following interesting workshops:
1- Have you tried RESTful APIs with APEX? Hosting and Consuming RESTful APIs with Oracle Apex 5.0  (Session 01) Web Services enable applications to interact with one another over the web in a platform-neutral, language independent environment. In a typical Web Services scenario, a business application sends a request to a service at a given URL by using the protocol over HTTP. The service receives the request, processes it, and returns a response. Web Services are typically based on Simple Object Access Protocol (SOAP) or Representational State Transfer (REST) architectures. RESTful Web Services will be presented next meetup. --By Mohannad Amarneh
2-From Zero to Hero (Season 01 (ServiceDesk System), Episode 01 - (Data Modelling))
The first workshop towards building real application from scratch. In the first workshop (episode) we will s…