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.
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