how to call stored function of IN and OUT parameter in Oracle Database using Java

Question

public String getObjectDetails(String buffer) {
        dbcon con = new dbcon();        
        ResultSet rs = null;
        String name_msg=null;
        String type_msg=null;
        int type_code=0;
        int name_code=0;
        try {
            rs = con.dbstate().createStatement().executeQuery("SELECT pkg_test1.get_object_type('"+buffer+"',"+type_code+",'"+type_msg+"'),pkg_test1.get_object_name('"+buffer+"',"+name_code+",'"+name_msg+"') from dual");
            if (rs.next()) {
                return rs.getString(1) + "," + rs.getString(2);
            }
        } catch (SQLException ex) {
            Logger.getLogger(username.class.getName()).log(Level.SEVERE, null, ex);
        }
        return "";
    }

And, I'm trying to get information from these two function declared inside PKG_TEST1 package

FUNCTION get_object_type(i_buffer IN VARCHAR2, o_errcode OUT NUMBER,
                           o_errmsg OUT VARCHAR2) RETURN VARCHAR2;

  FUNCTION get_object_name(i_buffer IN VARCHAR2, o_errcode OUT NUMBER,
                           o_errmsg OUT VARCHAR2) RETURN VARCHAR2;

But, I'm getting error

java.sql.SQLException: ORA-06572: Function GET_OBJECT_NAME has out arguments

Please help me to solve this problem.

Calling stored procedures is different from executing SQL, please see docs.oracle.com/javase/tutorial/jdbc/basics/… You must register out params. — home
– home, Commented Sep 17, 2013 at 5:49
@home but this is not procedure, this is function and as far as i know,they are different. — Ravi
– Ravi, Commented Sep 17, 2013 at 5:50
Why you are putting out parameters in function, I think thats bad style. You should have used stored procedure. Functions generally make sense when you have all IN parameters, then function do some calculation on IN parameters and return value. — lol
– lol, Commented Sep 17, 2013 at 5:53

Evgeniy Dorofeev · Accepted Answer · 2013-09-17 06:09:12Z

1

try this

    CallableStatement c = conn.prepareCall("{? = call get_object_type(?, ?)}");
    c.registerOutParameter(1, Types.VARCHAR);
    c.registerOutParameter(3, Types.VARCHAR);
    c.setString(2, buffer);
    c.execute();
    String res = c.getString(1);
    String err = c.getString(3);

answered Sep 17, 2013 at 6:09

Evgeniy Dorofeev

137k31 gold badges209 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ravi · Accepted Answer · 2013-12-15 06:02:37Z

0

This works for me, thanks @home to suggesting me good link.

String call = "{ ? = call pkg_test1.get_object_type(?,?,?)}";
CallableStatement cstmt = con.dbstate().prepareCall(call);
 cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR);
cstmt.setString(2, buffer);
cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER);
cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);
cstmt.executeQuery();
String type = cstmt.getString(1);

Well Explained Here : How to Call Strored Procedure and Function

edited Dec 15, 2013 at 6:02

answered Sep 17, 2013 at 6:16

Ravi

31.6k44 gold badges124 silver badges180 bronze badges

Collectives™ on Stack Overflow

how to call stored function of IN and OUT parameter in Oracle Database using Java

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related