Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes How to call java stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to call java stored procedure" Watch "How to call java stored procedure" New topic
Author

How to call java stored procedure

sorupa
Greenhorn

Joined: Dec 07, 2001
Posts: 4
I have written the stored procedure as follows using SYS_REFCURSOR.

CREATE OR REPLACE PROCEDURE fin_info (p_recordset1 OUT SYS_REFCURSOR,FINNO IN VARCHAR2)


AS
BEGIN

OPEN p_recordset1 FOR
SELECT NC_NAME ,RACE,DOB,GENDER,NAT,CONTACT_NUMBER,ENTRY_STATUS,ADDR_AS_IN_ID,CORRES_BLDG_NAME,
CORRES_BLK_HSE_NO,CORRES_STREET_NAME,CORRES_FLOOR_NO,CORRES_UNIT_NO,CORRES_POSTAL_CODE FROM
EIDS.TBL_EIS_NC WHERE FIN=FINNO ORDER BY CREATED_DT DESC ;

LOOP
FETCH p_recordset1 INTO NC_NAME, RACE,DOB,GENDER,NAT,CONTACT_NUMBER,ENTRY_STATUS,ADDR_AS_IN_ID,CORRES_BLDG_NAME,
CORRES_BLK_HSE_NO,CORRES_STREET_NAME,CORRES_FLOOR_NO,CORRES_UNIT_NO,CORRES_POSTAL_CODE;
EXIT WHEN p_recordset1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(NC_NAME || ' ' || RACE);
END LOOP;
CLOSE p_recordset1;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered (EIDS-FIN Information)- '||SQLCODE||' -ERROR- '||SQLERRM);
END fin_info;
/

grant execute on fin_info to tha_role;


call this procedure from the java program as follows.


cstmt = conn.prepareCall("{ ?=call THA.FIN_INFO(?) }");
System.out.println("FIN No...."+FINNo);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.setString(2,FINNo);
cstmt.execute();
System.out.println("after Procedure..........");
//rs = (ResultSet)cstmt.getObject(1);
//while (rs.next ())
// System.out.println( rs.getString (1) );
cstmt.close();

getting the following error.


Oracle.DBException: java.sql.SQLException: [BEA][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 18:
PLS-00306: wrong number or types of arguments in call to 'FIN_INFO'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

pls help me to resolve the issues.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

"subasorupa",
Welcome to the JavaRanch.

We're a friendly group, but we do require members to have valid display names.

Display names must be two words: your first name, a space, then your last name. Fictitious names are not allowed.

Please edit your profile and correct your display name since accounts with invalid display names get deleted.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Please note this is your third and final warning.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to call java stored procedure