Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Invoking BI Publisher Web services from BPEL Process to run a report

BI Publisher exposes four public Web services which can be used to perform a variety of activities including running a report.

The screenshot below is of a simple SOA composite which calls the runReport() method on the ReportService Web service to run a report with parameters. While Oracle’s official documentation is sufficient enough to make this a trivial exercise but there are still a couple of points to remember which is why this blog post is here in the first place.

composite

Here is the entire synchronous BPEL process.

BPEL Process

The AssignValues activity assigns the values required for running the report. I am hard-coding most of the values except for the reportAbsolutePath which I am passing as an input to BPEL process request. The reportAbsolutePath, of course, is the report path in the Catalog Folders in BI Publisher.

AssignInput

In the TransformInput activity, I am again hard-coding the parameters required for the BI Publisher report and mapping them to the variable that will be passed to  the Web service. But more importantly, this activity is required for two reasons:

  • The call to the BI Publisher Web service should not contain any empty tags otherwise it will throw errors. So, I am mapping only the non-empty elements to the target variable which will be passed during the call to the Web service.
  • One of the parameters for the runReport() method is the ReportRequest.  The ReportRequest data type in turn contains the item data type which stores the name-value pair for a report parameter. If multiple parameters need to be passed to a report, the item node  must be duplicated.

Transform

Transform2

The AssignSuccessMessage concatenates the elements in the response returned by the call to the ReportService and assigns it to the response message of the BPEL process.

AssignOutput

That is about it. As I mentioned earlier it is a pretty simple process and the only thing that you need to take care of is that you are not passing any empty tags in the Web service call.

Default parameter values in package subprograms

For a procedure (or function) in a package, you can assign default values only in the package specifications without assigning them in the package body:


SQL> CREATE OR REPLACE PACKAGE test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT 1);
END;
/

Package TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER) AS
BEGIN
NULL;
END;
END;
/

Package body TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.

The reverse is not allowed though:


SQL> CREATE OR REPLACE PACKAGE test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER);
END;
/

Package TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT 1) AS
BEGIN
NULL;
END;
END;
/

Package body TEST_DEFAULT_PKG compiled

Errors: check compiler log
SQL> SHOW ERRORS;
Errors for PACKAGE BODY HR.TEST_DEFAULT_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------------------
2/25 PLS-00593: default value of parameter "P_NUM" in body must match that of spec

Also, if the default value is specified in both the package specification and package body, they must match:


SQL> CREATE OR REPLACE PACKAGE test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT 1);
END;
/

Package TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT NULL) AS
BEGIN
NULL;
END;
END;
/

Package body TEST_DEFAULT_PKG compiled

Errors: check compiler log
SQL> SHOW ERRORS;
Errors for PACKAGE BODY HR.TEST_DEFAULT_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------------------
2/25 PLS-00593: default value of parameter "P_NUM" in body must match that of spec

 

Logical operators in RETURN clause of a function

Here is what I learnt today.

You can use logical operators in the RETURN clause of a function whose return type is BOOLEAN. So for example, you can write a function with the following structure:


create or replace function test_fn(p_param varchar2)
return boolean
as
l_value varchar2(10):= 'TEST';
begin
return false
or p_param = l_value
or length(p_param) = 5 ;
end;
/
show errors;

The logical truth table is used to evaluate the expression in the RETURN clause and hence this function will return TRUE if the value passed to it is either TEST or any five-characters long string. For all other cases it will return FALSE.

Of course, the same logic could have been coded in lots of different ways but the example was just to show that this was one of the ways.

Follow

Get every new post delivered to your Inbox.

Join 63 other followers