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!