Getting compile error with oracle JDBC driver

User_OSSX7

Hello everyone!

I have a user who uses the Jaspersoft product to generate reports from Oracle DB with a very simple stored procedure (a function). This report works with Tibco JDBC driver but got a compile error when using the Oracle JDBC driver.

ORA-06550: line 1, column 7: PLS-00221: 'CUSTOM_TRANS_DET_LITE_FCN_V3' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored

The stored procedure has been running for over a year, according to this user. He only got this error when switching to the native Oracle driver running the function. This user is using a JNDI connection to access the oracle DB.

Here is his stored procedure:

create or replace FUNCTION "CUSTOM_TRANS_DET_LITE_FCN_V3" (

  p_ou_code_in        VARCHAR2,

  p_prior_period       VARCHAR2,

  p_date_filter        VARCHAR2,

  p_date_start        DATE,

  p_date_end         DATE,

  p_state_name_in       VARCHAR2,

  p_ou_code_in2        VARCHAR2,

  p_country_name       VARCHAR2,

  p_transaction_type_in    VARCHAR2,

  p_transaction_type_name_in VARCHAR2,

  p_past_x_days        NUMBER DEFAULT -1

)

RETURN sys_refcursor IS

  trans_cursor        SYS_REFCURSOR;

  v_ou_code          VARCHAR2(40);

  v_ou_code2         VARCHAR2(32767);

  v_state_name        VARCHAR2(32767);

  v_sql_stmt         VARCHAR2(32767);

  date_column         VARCHAR2(40);

  v_transaction_type_name   VARCHAR2(32767);

  v_past_x_days        NUMBER;

  v_date_start        DATE;

  v_date_end         DATE;

BEGIN

  << Processing was here ??

   

  open trans_cursor for v_sql_stmt using v_date_start, v_date_end;

   

  return trans_cursor;

   

END;

/

--------------------------------------------------------

And this is how the user makes a call from JasperReports Java application:

call CUSTOM_TRANS_DET_LITE_FCN_V3(

$P!{LoggedInUserAttribute_AvailableOrgs}

, $P{PRIOR_PERIOD}

, $P{DATE_FILTER}

, $P{DATE_FILTER_START}

, $P{DATE_FILTER_END}

, $P{P_STATE_NAME2}

, $P{P_OU_CODE2}

, $P{P_COUNTRY_NAME}

, $P{P_TRANSACTION_TYPE}

, $P{P_TRANSACTION_TYPE_NAME2}

, $P{P_PAST_X_DAYS})

================================================

It has several collection parameters but I am not sure it is the issue here since the report calling the Oracle stored function is passing strings instead of arrays of strings to the procedure.

For example:

    <parameter name="P_OU_CODE" class="java.util.Collection"/>

    <parameter name="P_OU_CODE2" class="java.lang.String" isForPrompting="false">

        <defaultValueExpression><![CDATA[$P{P_OU_CODE}.toString()]]></defaultValueExpression>

    </parameter>

The report input gets a collection through parameter "P_OU_CODE" and passes to the function as a string through "P_OU_CODE2".

Could you experts spot anything that could cause the error only with Oracle JDBC driver? Thanks!