CODEWITHSUNDEEP
javasqloracle-databasenetbeansplsql
user2919688
user2919688

Reputation: 5

How to get a result out of a pl/sql function in Netbeans

I have a function (not a procedure) in my Oracle database. This function looks like:

CREATE OR REPLACE FUNCTION GETTOTAL(v_user_id IN NUMBER)
RETURN NUMBER
AS
    v_result number := 0;
BEGIN
    SELECT SUM(DAY1+DAY2) INTO v_result FROM TABLE WHERE USER_ID = v_user_id;
RETURN v_result;
END;

Now in my program in java Netbeans i need that result to use that inside my program.

I have tried the following:

callStatement = con.prepareCall("SELECT GETTOTAL(1) FROM DUAL;");
callStatement.execute();
resultaat = callStatement.getDouble(1);
callStatement.close();

I have also tried to use a CALL. But nothing seems to work. Also i've tried looking for the problem on the web, but it seems only procedures are explained and not functions... So i hope i can find an awnser here.

Upvotes: 0

Views: 4172

Answers (3)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

Check my example:

CREATE TABLE my_test_tab (
  user_id NUMBER,
  day1 NUMBER,
  day2 NUMBER
);

INSERT INTO my_test_tab VALUES (1, 5, 10);
INSERT INTO my_test_tab VALUES (1, 1, 2);

COMMIT;

CREATE OR REPLACE FUNCTION GETTOTAL(v_user_id IN NUMBER)
RETURN NUMBER
AS
    v_result number := 0;
BEGIN
    SELECT SUM(DAY1+DAY2) INTO v_result FROM my_test_tab WHERE USER_ID = v_user_id;
RETURN v_result;
END;
/

In Java, you create a CallableStatement and you have to registerOutParameter for the function's returned value, check the code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.CallableStatement;

public class Main2 {
  public static void main(String[] args) throws Exception {
    Connection conn = getOracleConnection();
    System.out.println("Got Connection.");

    CallableStatement callStmt = null;

    try {
      callStmt = conn.prepareCall("{? = call gettotal(?)}");
      callStmt.setInt(2, 1);
      callStmt.registerOutParameter(1, java.sql.Types.NUMERIC);
      callStmt.execute();

      System.out.println(callStmt.getInt(1));
    } finally {
      callStmt.close();
      conn.close();
     }
  }

  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@HOST_ADDRESS:1521:orcl";
    String username = "USERNAME";
    String password = "PASSWORD";

    Class.forName(driver); // load Oracle driver

    java.util.Properties info = new java.util.Properties();  
    info.put ("user", "hr");  
    info.put ("password", "oracle");  
    Connection conn = DriverManager.getConnection(url, info);

    return conn;
  }
}

Upvotes: 1

Moh-Aw
Moh-Aw

Reputation: 3018

try this:

String getDBUSERByUserIdSql = "{call GETTOTAL(?, ?)}";
callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);
callableStatement.setInt(1, 10);
callableStatement.registerOutParameter(2, Types.INTEGER);
callableStatement.execute();

int retVal = callableStatement.getInt(2);

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

You need to use the 

String getDBUSERByUserIdSql = "{call GETTOTAL(?)}";
callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);
callableStatement.setInt(1, 10);

Upvotes: 0

Related Questions