• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to call java stored procedure

 
sorupa
Greenhorn
Posts: 4
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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
Posts: 13459
Android Eclipse IDE Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please note this is your third and final warning.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic