Skip to Main Content

Java Programming

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Getting compile error with oracle JDBC driver

User_OSSX7Aug 11 2021

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!

Comments

Processing

Post Details

Added on Aug 11 2021
0 comments
213 views