How to call Stored Procedure from BI Publisher?

Question

I am trying to call a stored procedure from BI Publisher, and need some help to achieve this.

Here is what I have: In my Data Model I have the following code:

DECLARE
  type refcursor is REF CURSOR;
  xdo_cursor refcursor;
BEGIN
  OPEN :xdo_cursor FOR
  SELECT IPVOWN.F1(5) FROM DUAL;
  COMMIT;
END;

Type of SQL = Non-standard SQL Running this code works in SQL Developer when you remove the ":" from xdo_cursor, but in BI ":" is required. I get the following error in the log file:

<txt>java.sql.SQLException: ORA-06550: line 2, column 22:
 PLS-00103: Encountered the symbol "." when expecting one of the following:

 * &amp; = - + ; &lt; / > at in is mod remainder not rem
 &lt;an exponent (**)> &lt;> or != or ~= >= &lt;= &lt;> and or like like2
  like4 likec between || multiset member submultiset

  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

thanks in advance.

Answer 1

Below are the steps/documentation on how to call a Function from BI Publisher:

1) Make sure you are using SYS_REFCURSOR.

2) Create your function like below in DB:

CREATE OR REPLACE FUNCTION FUNC1 (P1 VARCHAR2) RETURN SYS_REFCURSOR IS
XDO_CURSOR SYS_REFCURSOR;
BEGIN
            IF P1 = 'USA' THEN
              OPEN XDO_CURSOR FOR
              'SELECT  TO_CHAR(SYSDATE,''MM-DD-YYYY'') AS CURRENT_DATE, X.STATE FROM schemaName.XX1 X WHERE X.ID IN (100,200,400)';
             RETURN XDO_CURSOR;
            ELSE
              OPEN XDO_CURSOR FOR
              'SELECT  TO_CHAR(SYSDATE,''MM-DD-YYYY'') AS CURRENT_DATE, X.STATE FROM schemaName.XX1 X WHERE X.ID IN (300,500,600)';
              RETURN XDO_CURSOR;
            END IF ;
END FUNC1;

3) Log in as System and grant execute to the BI User

GRANT EXECUTE ON schemaName.FUNC1 TO BI_User;

In your Data Set in BI Publisher - You can do the following to call the function: Make sure the type of SQL is "Procedure Call"

  DECLARE
    type refcursor is REF CURSOR;
    xdo_cursor refcursor;
  BEGIN
    :xdo_cursor := SchemaName.func1(:P1);
  END;

For more info you can use the link below: https://community.oracle.com/thread/888365