Error executing Oracle function from JSP page

[Wally Christoff]

I'm trying to call a stored procedure from a jsp page using the code below,
but I am currently getting the following error message:
"java.sql.SQLException: [BEA][Oracle JDBC Driver]The requested output parameter data is not available."
I am running weblogic 8.1 on my local machine and connecting to an Oracle 8i database.

Can someone tell me why I am getting this error? Thanks in advance for your help!

CallableStatement = conn.prepareCall("{ call ? := Func_Noodle_oqr_one(?,?)}");
st.registerOutParameter(1, OracleTypes.CURSOR);
st.setString(2,"");
st.setInt(3,neuId);
ResultSet rs = (ResultSet)st.getObject(1); // <- The error appears to be occuring at this point
rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" +
rs.getFloat(2) + "\t" +
rs.getDate(3).toString());
}

[Wally Christoff]

BTW, this is my Oracle function:

CREATE OR REPLACE function Func_Noodle_oqr_one(
parm_option varchar2,
parm_CSR_ID number
)
return oqr_first.ref_cursor
AS
return_curs oqr_first.ref_cursor;
BEGIN
open return_curs for
SELECT db_bp_id as bp_id
,db_bp_ic_standard_industry_id as sic
,db_iqr_sec_id as sec_id
,db_iqr_quote_id as quote_id
,db_bp_business_name as biz_name
,min(db_sii_standard_industry_id_de) as sic_desc
,min(db_iqr_creation_dt) as creation_dt
,decode(agency_name,null,db_afi_affiliate_name,agency_name) as afi_name
,db_afi_affiliate_type as afi_type
,db_nnt_type as nnt_type
,db_csr_name as account_owner
,ar_rating_desc as rating_desc
,ar_image_file as image_file
FROM tbl_insurance_quote_request
,tbl_business_profiles
,tbl_standard_industry_id
,tbl_security
,tbl_affiliates
,tbl_noodle_network_type
,tbl_agency_info
,tbl_agency_info_additional
,tbl_agency_rating
,TBL_CSR
,(select distinct db_iqr_quote_id as validQuoteID
from tbl_insurance_quote_request
,tbl_business_profiles
where db_iqr_quote_status = 0 and
db_iqr_quote_generated = 'N' and
db_bp_id = db_iqr_bp_id and
db_bp_account_owner_id = parm_csr_id
UNION
select distinct db_iqr_quote_id
from tbl_insurance_quote_request
,tbl_noodle_eng_activities
where db_iqr_quote_status = 0 and
db_iqr_quote_generated = 'N' and
nea_pk_activity_id = DB_IQR_FK_NEA_ID and
NEA_FK_CSR_ASSIGNED_TO_CSR_ID = parm_csr_id )
WHERE db_iqr_bp_id = db_bp_id and
db_iqr_sec_id = db_bp_sec_id and
db_iqr_sec_id = db_sec_id and
db_bp_ic_standard_industry_id = db_sii_standard_industry_id and
db_bp_agency_id = tbl_agency_info.agency_id(+) and
db_sec_affiliate_id = db_afi_affiliate_id and
lower(db_nnt_affiliate_type) = lower(db_afi_affiliate_type) and
db_sii_active_flag = 1 and
db_csr_id = db_bp_account_owner_idand
aia_agency_id(+) = agency_id and
aia_agency_rating = ar_pk_id(+) and
db_iqr_quote_generated = 'N' and
db_iqr_quote_id = validQuoteID
GROUP BY db_bp_id
,db_iqr_sec_id
,db_iqr_quote_id
,db_bp_business_name
,db_bp_zipcode
,db_bp_ic_standard_industry_id
,db_afi_affiliate_name
,agency_name
,db_afi_affiliate_type
,db_nnt_type
,db_csr_name
,ar_rating_desc
,ar_image_file
ORDER BY db_iqr_quote_id DESC
,upper(db_bp_business_name) DESC
,upper(db_csr_name) DESC
,upper(decode(agency_name,null,db_afi_affiliate_name,agency_name)) DESC
,upper(min(db_sii_standard_industry_id_de)) DESC
,min(db_iqr_creation_dt) DESC;
return return_curs;

/* Exception Handler */
EXCEPTION WHEN NO_DATA_FOUND then
open return_curs for select null from dual;
RETURN return_curs;
END;
/

[Shailesh Chandra]

Originally posted by Wally Christoff:


CallableStatement = conn.prepareCall("{ call ? := Func_Noodle_oqr_one(?,?)}");

Can you try without colon ":" and call your function like this

CallableStatement = conn.prepareCall("{ ? = call Func_Noodle_oqr_one(?,?)}");
[ October 23, 2004: Message edited by: Shailesh Chandra ]

[Wally Christoff]

Shailesh, yes I tried your suggestion to no avail... still getting the same error. Any other suggestions would be greatly appreciated.

[Shailesh Chandra]

Wally

I think it should work, any way I would like to see the modified code can you paste the code snippet for modified here.
[ October 25, 2004: Message edited by: Shailesh Chandra ]

[Wally Christoff]

Shailesh, here is my latest code:

CallableStatement st;
ResultSet rs = null;
st = conn.prepareCall("{ ? = call Func_Noodle_oqr_one(?,?)}");
st.registerOutParameter(1, OracleTypes.CURSOR);
st.setString(2,"");
st.setInt(3,neuId);
st.executeQuery(); <- also tried st.execute; and without this statement.
rs = (ResultSet)st.getObject(1); // <- error occuring at this point
//rs = st.executeQuery(); <- removed this statement (was setting rs twice)
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" +
rs.getFloat(2) + "\t" +
rs.getDate(3).toString());
}

[Shailesh Chandra]

It should work !!! I dont find any error or we both are missing something.

I suggest you comment after the code st.execute()

and then execute code. tell me if this gives no error.

also tell me error which you are getting.

 CallableStatement st;
ResultSet rs = null;
st = conn.prepareCall("{ ? = call Func_Noodle_oqr_one(?,?)}");
st.registerOutParameter(1, OracleTypes.CURSOR);
st.setString(2,"");
st.setInt(3,neuId); 
 
st.execute(); 
/*
rs = (ResultSet)st.getObject(1); // <- error occuring at this point
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" +
rs.getFloat(2) + "\t" +
rs.getDate(3).toString());
}  
*/

[ October 26, 2004: Message edited by: Shailesh Chandra ]

[Wally Christoff]

I was wrong about where the error is occuring in that last post...
It's actually occuring on the st.execute(); statement. This is the error message now: "java.sql.SQLException: [BEA][Oracle JDBC Driver][Oracle]ORA-00911: invalid character".

CallableStatement st;
ResultSet rs = null;
st = conn.prepareCall("{ ? = call Func_Noodle_oqr_one(?,?)}");
st.registerOutParameter(1, OracleTypes.CURSOR);
st.setString(2,"");
st.setInt(3,neuId); 
/* <-commenting out from here results in no error...
st.execute(); // <- error occuring at this point
rs = (ResultSet)st.getObject(1); 
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" +
rs.getFloat(2) + "\t" +
rs.getDate(3).toString());
} 
*/

If I comment out all of the code from before the st.execute(); statement, I don't get an error within this block of code (just an error further down the page because there is no data from the recordset).

[Wally Christoff]

Actually, I was wrong again about that error message (I'm trying so many different things, it's getting confusing)...

Using the PrepareCall statement you suggested,
st = conn.prepareCall("{ ? = call Func_Noodle_oqr_one(?,?)}");
I am getting this error:
"java.sql.SQLException: [BEA][Oracle JDBC Driver]Invalid parameter binding(s").

I had temporarily gone back to my previous PrepareCallStatement, st = conn.prepareCall("{ call ? := Func_Noodle_oqr_one(?)}");, which gave me the "java.sql.SQLException: [BEA][Oracle JDBC Driver][Oracle]ORA-00911: invalid character" error I mentioned in the last post.

Sorry about the mixup...

[Shailesh Chandra]

So Wally I prefer with my approcach

st = conn.prepareCall("{ ? = call Func_Noodle_oqr_one(?,?)}");

as I had worked with same code many times

call you function like this

st = conn.prepareCall("{ ? = call Func_Noodle_oqr_one("your value","your value")}");

and only registerOutParameter try if this work.

then remove one argument in code

st = conn.prepareCall("{ ? = call Func_Noodle_oqr_one(? ,"your value")}");

and use

st.setString(2,"");

and find the exact cause of error.

thats what I can think....
[ October 26, 2004: Message edited by: Shailesh Chandra ]

[nilesh Katakkar]

Wally, not sure if you have figured this out already. If so then you can ignore this.

Following is the example that I use in one of my projects. Changed SQL and names, but should be simple -

CREATE OR REPLACE PACKAGE Test
AS
TYPE refCursor IS REF CURSOR;

FUNCTION getAssets (instructionId NUMBER, status VARCHAR2)
RETURN refCursor;

END;
/
----------------------------------------------------------------------------
CREATE or REPLACE PACKAGE BODY Test
AS
FUNCTION getAssets (instructionId NUMBER,
status VARCHAR2)
RETURN refCursor
IS
assetCursor refCursor;
BEGIN
OPEN assetCursor FOR
SELECT
a.instruction_id,
a.instruction_type
a.asset_number,
l.lot_number,
l.amount
FROM asset a, asset_lot l
where a.instruction_id = instructionId
and a.asset_status = status;

RETURN assetCursor;
END;
----------------------------------------------------------------------------
END;
/

DataSource ds = (DataSource)ctx.lookup("Oracle_sid");
connection = ds.getConnection();
String storeProc = "{call ? = test.getAssets(?,?)}";
CallableStatement getAssets = connection.prepareCall(storeProc);
getAssets.setLong(2,43544);
getAssets.setString(3,"CLOSED");
getAssets.registerOutParameter(1,OracleType.CURSOR); // -10
getAssets.execute();
ResultSet rs = (ResultSet)getAssets.getObject(1);

In your example I dont think there's any problem in the JDBC code that you have in ya JSP. You can only return REF CURSOR from Oracle to java clients. Your return type cursor is specific, and can only be understood by PL/SQL.

cheers.