Pass Date to DB Procedure from BI Publisher

Abhishek Mittal

Summary

Pass Date to DB Procedure from BI Publisher

Content

Hello Folks,

I am having an requirement where I need to pass 'DATE' value from BI Publisher to invoke a DB function.

I have created an DB function as below.

CREATE OR REPLACE FUNCTION FUNC1 (P1 DATE) RETURN SYS_REFCURSOR IS 

      XDO_CURSOR SYS_REFCURSOR; 

BEGIN   

      OPEN XDO_CURSOR FOR 

      'SELECT COL1, COL2, COL3 FROM SCHEMA.TABLENAME WHERE ACC_DATE = '||P1||''; 

      RETURN XDO_CURSOR; 

END FUNC1;

ACC_DATE is of DATE type in database table.

I have the coded the below in data-set of Data Model in Bi Publisher.

DECLARE 

   type refcursor is REF CURSOR; 

   xdo_cursor refcursor; 

BEGIN 

   :xdo_cursor := SchemaName.FUNC1 (:P1); 

END; 

P1 is mentioned is the parameter defined in data model to handle dynamic run-time values.

Please suggest.

Regards,

Abhishek