• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

How to call java stored procedure

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please note this is your third and final warning.
 
I don't like that guy. The tiny ad agrees with me.
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic