3 Replies Latest reply on Jul 31, 2013 2:03 PM by BIPuser

    BUG: data set - query using function returning ref cursor (with bind variables). Help needed.




      I believe there's a bug in BI Publisher 11g ( when using a data set with a function returning a ref cursor. If someone can help me resolve this I'd be very grateful.


      Here are the steps to reproduce the bug:

      1. Create a function that takes 2 parameters and returns sys_refcursor:

      create or replace package pkg_rc is
        function rc(p1 in number, p2 in number) return sys_refcursor;
      create or replace package body pkg_rc is
        function rc(p1 in number, p2 in number) return sys_refcursor is
          v_rc sys_refcursor;
          open v_rc for select p1 p1, p2 p2 from dual;
          return v_rc;


      2. Log into the BI Publisher ( http://localhost:7001/xmlpserver/ ) and create a new Data Model. In this Data Model create two parameters, "p1" and "p2" and a SQL Query data set with the following query:

      select pkg_rc.rc(:p1, :p2) rc_data from dual


      3. So far so good, now you should be able to see the data in Data tab.


      4. Now go back to Diagram tab and click "Edit Data Set". After the data set dialog opens, click OK to confirm changes - you get the following JS error:

      Error: TypeError: value is null
      In File: http://localhost:7001/xmlpserver/js/xdmeditor/structure/element/dataelement.js?cacheBuster=20130303.1415
      Line: 716

      Now no more changes to this data set are possible.
      Sometimes another dialog pops up after clicking OK, asking you to eneter values for bind variables. After entering the values and clicking OK you get the same error as above.

      The error only occurs when there are bind variables in the SQL query. If you replace bind variables with hardcoded values the problem goes away.

      This behavious is not browser specific, I've tested it on Firefox 22 (Windows 7, Windows XP), Google Chrome 28 (Windows XP) and IE9 (Windows 7).

      Any help on how to resolve this issue would be greatly appreciated. I have a lot of data models defined with ref cursors and don't want to change every single one of them to use pipelined functions or similar.