call oracle stored function from java taking no parameter

Question

I have the following functions in oracle .

CREATE OR REPLACE function CUSTOMERAPP.procOneParameter
return  boolean
IS
BEGIN

  return true;

END;

Now I want to get the return value from this function . For this , I have the following code .

public class CallFunctionFromOracle {

   public static void main(String... arg) throws SQLException {
      Connection con = null;
      CallableStatement callableStmt = null;
      try {
           // registering Oracle driver class
           Class.forName("oracle.jdbc.driver.OracleDriver");

           // getting connection
           con = DriverManager.getConnection(
                    "jdbc:oracle:thin:@10.11.201.166:1521:xe", "customerapp", "customerapp");

           System.out.println("Connection established successfully!");


           callableStmt = con.prepareCall("{ ? = call procOneParameter()}");

           //Database function will return value as OUT parameter
           callableStmt.registerOutParameter(1, java.sql.Types.NUMERIC);

           //IN parameter -
           //set methods are used for setting IN parameter values of Stored procedure
//           callableStmt.setInt(2, 11);

           //Execute database Function,
           callableStmt.execute();

           // Then retrieve values returned by method using using get methods.
           System.out.println("salary = " + callableStmt.getBoolean(1));

           System.out.println("Function executed successfully, "
                    + "salary has been fetched from Employee table");

      } catch (ClassNotFoundException e) {
           e.printStackTrace();
      } catch (SQLException e) {
           e.printStackTrace();
      }
      finally{
           try {
               if(callableStmt!=null) callableStmt.close(); //close CallableStatement
               if(con!=null) con.close(); // close connection
           } catch (SQLException e) {
               e.printStackTrace();
           }
      }
  }

}

But running this code gives me the following error .

java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1007)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
    at callfunctionfromoracle.CallFunctionFromOracle.main(CallFunctionFromOracle.java:44)

How can I solve this error ?

My question is not possible duplicate of this questions . Here I am talking about "PLS-00382: expression is of wrong type" error while in that question the user talked about "Invalid column type error" error .

So my question is different from that question .

Possible duplicate of calling oracle PL/SQL function in java - Invalid column type error — OldProgrammer
– OldProgrammer, Commented May 2, 2016 at 11:16

vssk · Accepted Answer · 2016-05-02 12:16:51Z

-1

Change plsql block in prepareCall to:

callableStmt = con.prepareCall("begin ? := procOneParameter(); end;");

edited May 2, 2016 at 12:16

answered May 2, 2016 at 11:36

vssk

4734 silver badges7 bronze badges

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

2 Comments

osimer pothe Over a year ago

I am getting this error using your code . java.sql.SQLException: ORA-06550: line 1, column 11: PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; indicator ORA-06550: line 1, column 33: PLS-00103: Encountered the symbol "END"

OldProgrammer Over a year ago

That is not the issue. Please see duplicate answer posted in comments.

Collectives™ on Stack Overflow

call oracle stored function from java taking no parameter

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related