Java class file from PL/SQL procedure

Question

I have a following PL/SQL procedure:

CREATE OR REPLACE PROCEDURE getDogInfo
(Dog_ID IN NUMBER, Dog_name OUT VARCHAR) AS
BEGIN
  SELECT Dog_name INTO Name
  FROM Dog_family
  WHERE ID = Dog_ID;
END;

I need to make a java class file that does the same. I've been trying like this:

import java.sql.*;
import java.io.*;

public class Procedure {

  public static void getDogInfo (int Dog_ID, String Dog_name)
    throws SQLException
    { String sql =
      "SELECT Dog_name INTO Name FROM Dog_family WHERE ID = Dog_ID";
    try { Connection conn = DriverManager.getConnection("jdbc:default:connection:");
      PreparedStatement apstmt = conn.prepareStatement(sql);
      apstmt.setInt(1, Dog_ID);
      apstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
      ResultSet rset = apstmt.executeQuery();
      rset.close();
      apstmt.close(); //Connection close
      }
    catch (SQLException e) {System.err.println(e.getMessage());
    }
  }
}

What am I doing wrong? Can someone help me get this working? Thanks

score 0 · Answer 1 · edited Jan 30 '17 at 13:56

You should read about JDBC (and Java in general too).

The query should be :

SELECT Name 
FROM Dog_family 
WHERE ID = ?

(assuming Name is the column name you are selecting from the table - it wasn't clear whether Name or Dog_name was the column name).

Then after you execute the query and get a result set :

String name = null;

if (rset.next()) {
  name = rset.getInt (1);
}
...
return name;

Finally, your function should return a String. You can't pass the String as a parameter and update its value. String is immutable in Java.

One more thing - the line apstmt.registerOutParameter(2, java.sql.Types.VARCHAR); is not needed. registerOutParameter is only used with CallableStatement, which is a statement you use to execute a stored procedure.

score 0 · Answer 2 · answered Dec 13 '13 at 02:37

0

Have alook at this link showing you how to correctly use PreparedStatements.

You will find that the parameter should be ? not Dog_ID

Try

SELECT Name FROM Dog_family WHERE ID = ?

It will also show you how to iterate through your resultSet

answered Dec 13 '13 at 02:37

Scary Wombat

44,617
6
35
64

score 0 · Answer 3 · edited May 23 '17 at 11:45

Well, you do not tell us what the problem is, but I see several issue right away:

Your select statement should not have an INTO clause. That is a PL/SQL construct. You need to return the result of the query back as a result set.
Your input parameter, Dog_ID will not be used, because you have not named the parameter correctly in the SQL statement.
Java string parameters cannot be updated within the method, which I am assuming that is what you are attempting. You either need to return a string value from the method, or use a StringBuilder reference, or some other container to pass in. See this link There is no "out" parameter to register. Read up on result sets here

So, change your SQL statement to something like this(since you are using a positional parameter as opposed to a named parameter):

"SELECT Dog_name FROM Dog_family WHERE ID = ?"

Java class file from PL/SQL procedure

3 Answers3