Oracle Reports 12c and DESTYPE=blobdestination

Last week a colleague of mine contacted me to support him for a problem to configure and use Oracle Reports 12c (12.2.1.2.0 and 12.2.1.3.0) with DESTYPE=blobdestination, which means instead of displaying a rendered Report like PDF or XLS or HTML in the Browser to store the generated Report in a LOB into an underlying table in an Oracle Database.

At first you will need to download the BLOBDestination11g.jar (even when its compiled for Oracle Reports 11g, you can use it with Oracle Reports 12c) from My Oracle Support Note 11514155.1 https://support.oracle.com/epmos/faces/DocContentDisplay?id=1151455.1

Transfer the BLOBDestination11g.jar to your Server hosting your Oracle Forms & Reports 12c (e.g. /tmp) and copy it as BLOBDestination.jar to $ORACLE_HOME/reports/jlib

# Copy BLOBDestination11g.jar to $ORACLE_HOME/reports/jlib
cd /tmp
cp BLOBDestination11g.jar $ORACLE_HOME/reports/jlib/BLOBDestination.jar

As next we need to create a table which should receive our generated Reports PDF document.

# connect to your application schema and create following table
sqlplus test/Oracle12c@frrepo
SQL> create table executed_reports ( 
 ID            NUMBER NOT NULL
,CREATED_DATE TIMESTAMP DEFAULT SYSDATE
,CREATED_BY   VARCHAR2(20) DEFAULT USER
,REPORT_DOC   BLOB
,FILE_NAME    VARCHAR2(255));

The next part is depending if you are using an Oracle Reports In-Process Server or a Standalone Server.

Configuration for an In-Process Server

Connect to your Server which is hosting your Oracle Forms & Reports 12c environment and navigate to your $DOMAIN_HOME. There you will have to modify your eventually already existing setUserOverrides.sh or create a new setUserOverrides.sh in $DOMAIN_HOME/bin and modify the rwserver.conf configuration file for your In-Process Server

# add following block or 
# create a new setUserOverrides.sh with following block
# and align the Name of the Managed Server for Reports
# and align the ORACLE_HOME_PATH to your ORACLE_HOME

if [ "${SERVER_NAME}" = "<Name_ManagedServer_Reports>" ]; then
   export POST_CLASSPATH=<ORACLE_HOME_PATH>/reports/jlib/BLOBDestination.jar:$POST_CLASSPATH
fi

# e.g.

if [ "${SERVER_NAME}" = "MS_REPORTS" ]; then
   export POST_CLASSPATH=/u00/app/oracle/product/fmw-fr-12.2.1.2.0/reports/jlib/BLOBDestination.jar:$POST_CLASSPATH
fi

Then you will have to modify the rwserver.conf for your In-Process Server and add a new destination type. The rwserver.conf you can find under $DOMAIN_HOME/config/fmwconfig/components/<Name_ManagedServer_Reports>/applications/reports_12.2.1/configuration

# find the line with 
# <destination destype="WebDav" class="oracle.reports.plugin.destination.webdav.DesWebDAV"/>
# and add following line with the new destination type=BLOBDestination

<destination destype="WebDav" class="oracle.reports.plugin.destination.webdav.DesWebDAV"/>
<destination destype="BLOBDestination" class="oracle.reports.plugin.destination.blob.BLOBDestination"/>

Now restart your Managed Server for Reports and you are ready.

To test, just execute a Reports Call directly in your Browser:

http://<Your_Server>:<Port_of_ManagedServer_Reports>/reports/rwservlet?report=<Your_Test_Report>.rdf&userid=<App_User>/<Password>@<TNS_ALIAS>&DESTYPE=blobdestination&DESFORMAT=PDF&DESNAME=http://<App_User>:<Pasword>@<Your_Server>:<Listener_Port>/<ORACLE_SID>/<target_table>/<BLOB_Column>/<ID_Column>/<ID_Value>/<FileName_Column>

e.g.:

http://forms12:9002/reports/rwservlet?report=test.rdf&userid=test/Oracle12c@frrepo&DESTYPE=blobdestination&DESFORMAT=PDF&DESNAME=http://test:Oracle12c@forms12:1521/FRREPO/executed_reports/report_doc/ID/1/file_name

After that you will see in your target table for the Reports a row with your generated Reports PDF Document.

Configuration for a Standalone Reports Server

Connect to your Server which is hosting your Oracle Forms & Reports 12c environment and navigate to your $DOMAIN_HOME/reports/bin. There you will have to modify the reports.sh and modify the rwserver.conf configuration file for your In-Process Server

# Search for REPORTS_CLASSPATH in reports.sh
# and add the BLOBDestination.jar

REPORTS_CLASSPATH=${ORACLE_HOME}/reports/jlib/BLOBDestination.jar:${ORACLE_HOME}/reports/jlib/rwbuilder.jar:${ORACLE_HOME}/reports/jlib/rwrun.jar:${ORACLE_HOME}/jlib/zrclient.jar; export REPORTS_CLASSPATH

Then you will have to modify the rwserver.conf for your Standalone Reports Server and add a new destination type. The rwserver.conf you can find under $DOMAIN_HOME/config/fmwconfig/components/ReportsServerComponent/<Reports_Server_Name>

# find the line with 
# <destination destype="WebDav" class="oracle.reports.plugin.destination.webdav.DesWebDAV"/>
# and add following line with the new destination type=BLOBDestination

<destination destype="WebDav" class="oracle.reports.plugin.destination.webdav.DesWebDAV"/>
<destination destype="BLOBDestination" class="oracle.reports.plugin.destination.blob.BLOBDestination"/>

After that restart your Standalone Reports Server and thats all.

To test, just execute a Reports Call directly in your Browser:

http://<Your_Server>:<Port_of_ManagedServer_Reports>/reports/rwservlet?report=<Your_Test_Report>.rdf&userid=<App_User>/<Password>@<TNS_ALIAS>&DESTYPE=blobdestination&DESFORMAT=PDF&DESNAME=http://<App_User>:<Pasword>@<Your_Server>:<Listener_Port>/<ORACLE_SID>/<target_table>/<BLOB_Column>/<ID_Column>/<ID_Value>/<FileName_Column>

e.g.:

http://forms12:9002/reports/rwservlet?report=test.rdf&userid=test/Oracle12c@frrepo&DESTYPE=blobdestination&DESFORMAT=PDF&DESNAME=http://test:Oracle12c@forms12:1521/FRREPO/executed_reports/report_doc/ID/1/file_name

After that you will see in your target table for the Reports a row with your generated Reports PDF Document.