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
Greenhorn
Joined: Oct 22, 2004
Posts: 7
posted
0
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; /
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
Greenhorn
Joined: Oct 22, 2004
Posts: 7
posted
0
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()); }
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.
[ October 26, 2004: Message edited by: Shailesh Chandra ]
Wally Christoff
Greenhorn
Joined: Oct 22, 2004
Posts: 7
posted
0
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".
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
Greenhorn
Joined: Oct 22, 2004
Posts: 7
posted
0
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.
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
Ranch Hand
Joined: Oct 27, 2004
Posts: 35
posted
0
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;
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.
0
