Oracle Forms 12c & BI Publisher

Daniel Kampf 9 Comments

Today I started to have a look at the possibilities of integrating the BI Publisher in Forms 12c.

2

Sadly I didn’t find anything regarding this topic in the online documentation – so I created a report object and had a look at the properties.

We found out, that there are several new properties in the report object. So I created a forms module with a simple button and tried to call a BI-Publisher Server. As expected, my first try was not successful – I got an “FRM-41223: BI Publisher integration error”. So I had a look at the online help under “SET_REPORT_OBJECT_PROPERTY” to see if there are any new properties – and yes there are quite a lot new BI-Properties:

  • BIP_SSL_CONNECTION: Indicates whether or not SSL is mandatory or optional.
  • BIP_SERVICE_LOCATION: The URL to the Oracle BI_Publisher server Web Services WSDL for ScheduleServices.
  • BIP_REPORT_PATH: The absolute path to the report to be run (on the BI_Publisher server).
  • BIP_REPORT_LOCALE: Preferred report locale.
  • BIP_REPORT_FORMAT: Desired report output format.
  • BIP_REPORT_TEMPLATE: A template registered with the report specified in BIP_REPORT_PATH. It is common practice to provide this property along with report name/path but BI-Publisher gives the option of setting a default value when creating/editing a report, therefore this is an optional property in Forms. Value is used and validated by BI-Publisher.
  • BIP_REPORT_PARAMETERS: A list of name/value pairs separated by a space.
  • BIP_USER: The BI-Publisher username required to access the Web Service. This value cannot be set in the Property Palette.
  • BIP_PASSWORD: The BI-Publisher user password required to access the Web Service. This value cannot be set in the Property Palette.
  • FTP_SERVER: FTP server name as configured in BI-Publisher.
  • FTP_SECURED: Determine if FTP will be secured (i.e. SFTP) or unsecure.
  • FTP_USER: FTP server username.
  • FTP_PASSWORD: FTP server user password.
  • FTP_FILE_NAME: Output file name to be stored on FTP server.
  • FAX_NUMBER: Fax number. Format as required by your server configuration.
  • FAX_SERVER: Name of Fax server as configured in BI-Publisher.
  • MAIL_TO: The mailto email address.
  • MAIL_FROM: The mailfrom email address.
  • MAIL_CC: The mailcc email address.
  • MAIL_BCC: The mailbcc email address.
  • MAIL_REPLYTO: The replyto email address.
  • MAIL_SUBJECT: The email subject string.
  • MAIL_BODY: The email body string.
  • MAIL_SERVER: Name of the email server (SMTP) configured in BI-Publsher.
  • LOCAL_FILE_NAME: Absolute path and filename of the output file.
  • PRINT_COPIES: Number of copies to print.
  • PRINT_ORIENTATION: Page orientation.
  • PRINT_RANGE: Page range to print.
  • PRINT_SIDE: Enable two-sided printing. This setting will be ignored if the target printer does not support two-sided printing
  • PRINT_TRAY: The tray number. If you do not specify this option, the default media of the target printer will be used. It will be ignored if the target printer doesn’t support the media option.
  • PRINTER_NAME: Printer name on the BI-Publisher host to send the documents to.
  • WEBDAV_AUTH_TYPE: The authentication type.
  • WEBDAV_USER: The WebDAV username.
  • WEBDAV_PASSWORD: The WebDAV user password.
  • WEBDAV_FILE_NAME: The remote filename.
  • WEBDAV_SERVER: The WebDAV server name.

After setting the properties BIP_USER & BIP_PASSWORD I was able to run the BI-Report:3

9 thoughts on “Oracle Forms 12c & BI Publisher”

  3. After setting BIP_USER & BIP_PASSWORD properties also i am getting frm-41223 BI publisher integration error. Any other setup required?

    Like

    Reply

    • Hi Hariharan,

      make sure that all the information in the Report on formsside are correct (service location, template name etc.). As far as I remember, the properties you see in the sreencshot were all I used. Also do you use ssl for the connection to the BI Server?

      Best regards
      Daniel

      Like

      Reply

  4. Dear Daniel,

    I have given Following Information

    Report on form side properties
    Name :REPORT2
    Report Object Type :OraBIP
    SSL Connection :Optional
    Service Location :http://172.16.1.197:9704/xmlpserver/services/v2/ScheduleService?wsdl
    Report Path :/Practice/Sathish/emp.xdo
    Report Locale :english
    Report Format :pdf
    Template Name :emp

    When-button-Pressed Trigger

    Following code Used to call BI Report

    DECLARE
    bi_username varchar2(20);
    bi_password varchar2(20);
    repid REPORT_OBJECT;
    v_rep varchar2(256);
    rep_status varchar2(256);

    BEGIN
    repid := find_report_object (‘REPORT2’);
    bi_username := ‘weblogic’;
    bi_password := ‘welcome123’;
    SET_REPORT_OBJECT_PROPERTY (repid, BIP_USER, bi_username);
    SET_REPORT_OBJECT_PROPERTY (repid, BIP_PASSWORD, bi_password);
    v_rep := run_report_object(repid);
    END;

    After Pressing button
    it raising Error Message
    frm-41223 BI publisher integration error.

    Like

    Reply

  5. Hi Hariharan,

    I am not sure, but your report locale value seems not to be correct. Do you use en-US? Than you should put that into the property.
    Also what I found from the 12c new features paper is the possibility to catch the java exception – maybe it gives you a better error message (I have not tried that out) check out this code:

    DECLARE
    — Because Forms calls BIP with Java, we want to trap any Java errors that may occur.
    ex ora_java.jobject;
    ex_msg varchar2(255);

    BEGIN
    — Identify the Report Object.
    repid := find_report_object (‘BI_EMP’);
    — Before reaching this point be sure to obtain the username and password for BI-Pub.
    3 | ORACLE FORMS 12C – NEW FEATURES
    — Pass in the username and password.
    SET_REPORT_OBJECT_PROPERTY (repid, BIP_USER, bi_username);
    SET_REPORT_OBJECT_PROPERTY (repid, BIP_PASSWORD, bi_password);
    — Ask BIP to run the report.
    v_rep := RUN_REPORT_OBJECT(repid);
    /* DO SOMETHING ABOUT CHECKING STATUS HERE
    The call to BIP is asynchronous. Therefore, it will be necessary to check on its status periodically if
    notifying the user is desired. The report_object_status built-in can be used for that check.
    Likely a timer would be used to periodically check the status. Using a loop is not recommended.
    Example: rep_status := report_object_status(v_rep);
    */
    EXCEPTION
    WHEN ora_java.exception_thrown THEN
    ex := Exception_.new(ora_java.last_exception);
    BEGIN
    ex_msg := Exception_.toString(ex);
    message(‘Java Exception occurred: ‘ || ex_msg);
    ora_java.clear_exception;
    EXCEPTION
    WHEN ora_java.java_error THEN
    message(‘Unable to call out to Java, ‘ || ora_java.last_error);
    END;
    END;

    For more information on how to implement BI-Reports in Forms have a look at the paper:
    http://www.oracle.com/technetwork/developer-tools/forms/documentation/oracleforms-12210-newfeatures-2906037.pdf

    Like

    Reply

      • Hi Kavitha,

        in most cases when I received this error I hadn’t configured the Report Path Property correctly. What is the path to the report & user that is executing the report on your BI server?

        Like

Leave a comment