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.