Example program using IN and OUT parameters prepareCall ():
- prepareCall() method is used to returns CallableStatement object.
- We need to register out type parameter values using regiesterOutParameter() method.
- Types class is providing static variables to register the out variables.
- For example INTEGER, VARCHAR, FLOAT etc.
Create Student table:
SQL> create table student(num number(5), name varchar2(20));
Table created.
Inserting records into student table:
SQL> insert into student values(1001,'ram');
1 row created.
SQL> insert into student values(1002,'robert');
1 row created.
SQL> insert into student values(1003,'rahim');
1 row created.
SQL> commit;
Commit complete.
Checking records of student table:
SQL> select *from student;
NUM NAME
---------- --------------------
1001 ram
1002 robert
1003 rahim
Creating stored procedure using IN and OUT parameters to retrieve the name of a particular record num:
SQL> create or replace procedure getName(no in number, nm out varchar2)
is
begin
select name into nm from student where num=no;
end;
/
Procedure created.
Code:
| package online; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import java.sql.Types; import java.util.Scanner; public class CallableFetch { static Connection con; public static void main(String args[]) throws Exception { Scanner scan = new Scanner(System.in); try { Class.forName(“oracle.jdbc.driver.OracleDriver”); System.out.println(“Driver is ready”); con = DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”, “system”, “admin”); System.out.println(“Connection is ready”); String pro = “{call getName(?,?)}”; CallableStatement cs = con.prepareCall(pro); System.out.print(“Enter student ID : “); int id = scan.nextInt(); cs.setInt(1, id); cs.registerOutParameter(2, Types.VARCHAR); cs.execute(); String name=cs.getString(2); System.out.println(“Student name is : ” + name); } finally { if(con != null) { con.close(); System.out.println(“Connection closed”); } } } } |
Insert record into database table:
create table emps(num number(5), name varchar2(20), salary number(8));Created Stored Procedure to insert data:
SQL> create or replace procedure addNewEmp(
no in number,
name in varchar2,
sal in number)
is
begin
insert into emps values(no, name, sal);
end;
/
Procedure created.
Code:
| package online; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class CallableInsertRecord { static Connection con; public static void main(String args[]) throws Exception { try { Class.forName(“oracle.jdbc.driver.OracleDriver”); System.out.println(“Driver is ready”); con = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”, “system”, “admin”); System.out.println(“Connection is ready”); String pro = “{call addNewEmp(?,?,?)}”; CallableStatement cs = con.prepareCall(pro); cs.setInt(1, 1001); cs.setString(2, “divya”); cs.setFloat(3, 25000); cs.executeUpdate(); System.out.println(“Record is inserted into emp table!”); } finally { if(con != null) { con.close(); System.out.println(“Connection closed”); } } } } |