Calling an Oracle SQL function

Question

I'm new to the connectivity between Java and Oracle and I'm designing a login page.

I'm attempting to call an Oracle function from JAVA. Assume the Oracle function is called FUNCT_PERSON with 2 IN parameters: username and password and return the user id which is a NUMBER.

Inside the FUNCT_PERSON function we use a SELECT statement and return user id number if found, otherwise it will return 0.

Here is my code:

String sql = "{ ? = call FUNCT_PERSON(?,?) }";
CallableStatement statement = connection.prepareCall(sql);
statement.setString(2,username);
statement.setString(3,password);
statement.registerOutParameter(1, java.sql.Types.INTEGER);  

Boolean check = statement.execute();   

if (!check) {
 //proceed to another page
} else {
 //Go back to the login page
}

I don't know if what I'm doing is right or not, especially the Boolean check = statement.execute(); line, because I don't really know what the check is for..

If the function return a user ID, that means it exists in the database and I'll proceed to another page. If not, then it will redirect to the login page.

Your description makes sense, but I'm not sure what the question is...? — Joachim Isaksson
– Joachim Isaksson, Commented Feb 27, 2013 at 19:36

Luiggi Mendoza · Accepted Answer · 2013-02-27 19:42:56Z

9

You just need to recover the result of the operation and use it to validate the action to make:

String sql = "{ ? = call FUNCT_PERSON(?,?) }";
CallableStatement statement = connection.prepareCall(sql);
statement.setString(2,username);
statement.setString(3,password);
statement.registerOutParameter(1, java.sql.Types.INTEGER);  

statement.execute();   
//this is the main line
long id = statement.getLong(1);
if (id > 0) {
    //proceed to another page
} else {
    //Go back to the login page
}

Based on this tutorial

answered Feb 27, 2013 at 19:42

Luiggi Mendoza

85.9k16 gold badges159 silver badges354 bronze badges

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

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

From the JAVA Docs:

The execute method returns a boolean to indicate the form of the first result. You must call either the method getResultSet or getUpdateCount to retrieve the result; you must call getMoreResults to move to any subsequent result(s).

Returns:true if the first result is a ResultSet object; false if the first result is an update count or there is no result

So Your check is wrong. execute doesn't say if the operation was successful or not.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 27, 2013 at 19:39

Adel Boutros

10.3k8 gold badges58 silver badges91 bronze badges

2 Comments

Mr.Y Over a year ago

But normally, we store the resultSet into an array or cursor, am I correct? But my goal is simply getting the return value, which is a number as oppose to a "set" of numbers. So if check is true, then it means the select statement return some values, o/w check would be false am I correct?

Luiggi Mendoza Over a year ago

@Mr.Y that's explained in my answer. Note that the result of execute method just tells you that the statement was executed or not.

Collectives™ on Stack Overflow

Calling an Oracle SQL function

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related