Error - Calling Oracle function from Java

Question

I am trying to call a oracle function that takes one varchar input argument and returns a varchar. But it throws the following error. I have tried changing the type from varchar to nvarchar in Java but it did not help.

 java.sql.SQLException: ORA-06550: line 1, column 13:
 PLS-00306: wrong number or types of arguments in call to 'GET_DATA_TEST'

PLSQL function definition

 create or replace FUNCTION     Get_Data_test (p_acct callid)
  RETURN VARCHAR2 
  AS

Java function:

        String call = "{ ? = call Get_Data_test(?)}";
        CallableStatement cstmt = con.prepareCall(call);
        cstmt.setQueryTimeout(1800);
        cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR);
        cstmt.setString(2, "12345678");
        cstmt.executeQuery();
        name = cstmt.getString(1);
        System.out.println("hello: "+name);

Raúl · Accepted Answer · 2015-05-21 14:59:06Z

2

cstmt.setString(2, "12345678") is giving it impression that there are two in parameters. So change that to cstmt.setString(1, "12345678").

Also, it should come prior to cstmt.registerOutParameter, as we need to set input parameter first and then outparam. Check a tutorial on same.

EDIT - Looks like a problem with your input parameter type (p_acct callid). In function, check what is it's type. Chosse a type here in java accordingly.

edited May 21, 2015 at 14:59

answered May 21, 2015 at 14:46

Raúl

1,5504 gold badges24 silver badges37 bronze badges

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

2 Comments

sun_dare Over a year ago

I changed the order but still I get the same error. <br/> String call = "{ ? = call Get_Cdr_Data_test(?)}"; CallableStatement cstmt = con.prepareCall(call); cstmt.setQueryTimeout(1800); cstmt.setString(1, "477505655017033242764"); cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

Raúl Over a year ago

see - docs.oracle.com/cd/B10501_01/java.920/a96659/04_call.htm#15166 as count is same (1) for input and output - the issue will be most probably with TYPE of argument (I think of input type).

Beri · Accepted Answer · 2015-05-21 14:49:49Z

0

Try the following :

String sequenceQuery;

sequenceQuery = "{call Get_Data_test(?,?)}";  // where ?'s are the parameters

connection = DataSourceLocator.getInstance().getDataSource().getConnection();
callableStatement = connection.prepareCall(sequenceQuery);

callableStatement.registerOutParameter(1, OracleTypes.VARCHAR);

callableStatement.setString(2,"12345678");

callableStatement.execute();

edited May 21, 2015 at 14:49

Beri

11.6k4 gold badges38 silver badges59 bronze badges

answered May 21, 2015 at 14:47

Neil Lane

315 bronze badges

Collectives™ on Stack Overflow

Error - Calling Oracle function from Java

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related