Call Oracle Stored Function using CallableStatement through Oracle JDBC Pool Recource

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;