Call Oracle Stored Function using CallableStatement through Oracle JDBC Pool Recource

The Oracle PL/SQL language allows you to write functions and procedure to centralize the business logic and store the code in the database. This business logic may be call in many different ways including from a Glassfish Server's JDBC Pool Connection Resource.

There are five steps involved in calling a PL/SQL Function from within a application:
  1. Create and prepare a JDBC CallableStatement object that contains a call to your PL/SQL function.
  2. Register the output parameter for your PL/SQL function.
  3. Provide all of the required parameter values to your PL/SQL function.
  4. Call the execute() method for your CallableStatement object, which then performs the call to your PL/SQL procedure.
  5. Read the returned value from your PL/SQL function.


In the following example we will will validate User Information by calling stored function.


import java.sql.CallableStatement;
import java.sql.Connection;
import javax.sql.DataSource;
import javax.naming.InitialContext;


public class LoginBean {
private String userid;
private String userpassword;

/*Construct a LoginBean object. This constructor will create an Oracle 
JDBC Pooled Resource database connection.*/
public LoginBean(){
try {
InitialContext ctx = new InitialContext();
ODS = (javax.sql.DataSource) ctx.lookup("jdbc/__Oracon") ;
CN = ODS.getConnection();
if (CN == null){
System.out.println("Error in Establishing Connection");
}
} catch (Exception e) {
System.out.println("Oops!" + e.getMessage());
}
}

/*Call PL/SQL Function to validate User Information*/
public void fndusr(){

try {
CallableStatement cs = CN.prepareCall("{? = call Loginchk(?,?)}");
cs.registerOutParameter(1, java.sql.Types.INTEGER);
cs.setString(2, 'Shaheer');
cs.setString(3, 'abc');
cs.execute();
int retvalue = cs.getInt(1);

if (retvalue > 0){
System.out.println ("User Found");
}
else{
System.out.println ("Invalid User name or password");
}

/*Close down Oracle connection*/
cn.close();
} catch (Exception e) {
Flag = false;
System.out.println ("Oops! " + e.getMessage());
}


/* Calling class */
public static void main(String[] args)
throws java.lang.InterruptedException {
LoginBean a = new LoginBean();
a.fndusr(); 
}
}

Oracle Stored Function

create or replace function Loginchk(usrid varchar, usrpass varchar) return number is
Results number;
begin
SELECT COUNT(*) INTO Results FROM USERS u
WHERE u.user_id = usrid
and u.usr_password = usrpass;
return(Results);
end Loginchk;

Comments

Popular posts from this blog

Create Tables in Oracle Database using TOAD

How to create Oracle stored Procedures using TOAD for Oracle

How to Create Packages in Oracle Database using TOAD for Oracle