• Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

Computer Notes

Library
    • Computer Fundamental
    • Computer Memory
    • DBMS Tutorial
    • Operating System
    • Computer Networking
    • C Programming
    • C++ Programming
    • Java Programming
    • C# Programming
    • SQL Tutorial
    • Management Tutorial
    • Computer Graphics
    • Compiler Design
    • Style Sheet
    • JavaScript Tutorial
    • Html Tutorial
    • Wordpress Tutorial
    • Python Tutorial
    • PHP Tutorial
    • JSP Tutorial
    • AngularJS Tutorial
    • Data Structures
    • E Commerce Tutorial
    • Visual Basic
    • Structs2 Tutorial
    • Digital Electronics
    • Internet Terms
    • Servlet Tutorial
    • Software Engineering
    • Interviews Questions
    • Basic Terms
    • Troubleshooting
Menu

Header Right

Home » Java » Jdbc » How to Working with CallableStatement
Next →
← Prev

How to Working with CallableStatement

By Dinesh Thakur

CallableStatement of JDBC is derived from interface of PreparedStatement. In JDBC, CallableStatement is best statement because by using this statement we can execute SQL operation on database and we can also call stored procedure of database. The only difference between CallableStatement and PreparedStatement is that the latter is not useful to call a stored procedure but it is possible through the former.

CallableStatement in JDBC can be used for both executing the command on a database and also calling the stored procedure of database. So, we have two syntax to obtain CallableStatement.

 

Syntax-1

CallableStatement cs=cn.prepareCall(“sql command”);

 

Syntax-2

CallableStatement cs=con.prepareCall( {call procedurename(parameter) });

 

What is the difference between Procedure and Function in frontend programming?

• Procedure does not return any value.

Function returns only one value.

What is the difference between Procedure and Function in backend programming?

• Procedure can return zero or more value.

Function can return only one value.

Syntax for creating procedure in oracle database:

create or replace procedure Name(parameter)

is

declare variables;

begin

statement;

end;

• A procedure can contain any of three types of parameters

• IN parameter

• OUT parameter

• INOUT parameter

• By default the parameter is type IN.

IN: IN parameter is used to accept input values to a procedure.

OUT: Parameter is used to return output value from procedure.

INOUT: Parameter is used to take input value and also return output values.

• The following procedure is used for calculating experience of an employee.

Take Input as employee number and return output as employee name and is experienced.

The following is a JDBC program for calling the above procedure created in Oracle database server using Callable Statement of JDBC.

Step 1:Login to Oracle server

Step 2:SQL>ed demo

Step 3:Create procedure emp_experience

Syntax:

Create or replace procedure emp_experience(eno in number, empname out varchar2, experience out number)

is

doj date;

today date;

begin

Select sysdate into today from dual;

Select hiredate into doj from employee where empno=eno;

experience:=(today-doj)/365;

Select ename into empname from employee where empno=eno;

end;

Step 4: Save and exit

//compilation of procedure

Step 5:SQL>@demo

Step 6:Write Java code

import java.sql.*;

import java.util.*;

public class CallableTest1

{

       public static void main (String args [ ]) throws Exception

       {

               //load type-4 driver

               Class.forName(“oracle.Jdbc.driver.OracleDriver”);

               //get connecticin

               Connection cn=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:XE”,”system”,”manger”);

               //create Callable Statement object

               CallableStatement cs=cn.callableStatement(“insert into employee_info values(?,?,?) “);

               Scanner sc=new Scanner(System.in);

               System.out.println(“Enter the employee no”);

               int eid=sc.nextInt();

               //set id and name

               cs.setInt(1,eid);

               //registeter OUT parameter

               cs.registerOutParameter(2,Types.VARCHAR);

               cs.registerOutParameter(3, Types.INTEGER);

               //call the procedure

               cs.execute() ;

               //read empname

               String str=cs.getString(2);

               int k=cs.getInt(3);

               System.out.println(“empno”+eid) ;

               System.out.println(“emp narne”+str);

               System.out.println(“empexperience”+k+”year”) ;

               cs.close() ;

               cn.close() ;

      }

}

Output:

Enter employee number

7900

emp no 7900

emp name Dinesh

emp experience 31 years

In above example, while calling a procedure using CallableStatement:

• We register_out Parameter procedure call. The reason why OUTparameter is registered is that if you do not register then by default CallableStatement assumes all parameters as IN parameter.

• If the parameter is IN modeor IN type then Input values must be set before calling the procedure.

• If parameter is registered as OUT then CallableStatement object store the output values as returned by the OUT parameter.

• CallableStatement cannot understand Java Datatype or Database Datatype. So, we need to specify JDBC Type which acts as a mediator between java and database.

• Types.VARCHAR is mediator type between java String type and database varchar2.

• In JDBC all mediator data types are given in java.sql package.

                                                DATA TYPE

Java Type

JDBC Type

Database Type

Int

Types.INTEGER                     

Number

Double

Types.DOUBLE                      

Number

String

Types.VARCHAR                   

varchar2

Char

Types.CHAR                           

char(1)

InputStream  

Types.BLOB                            

Blob

In the following procedure empno is IN parameter and return Bonus is OUT parameter

SQL>ed demo2

Create or replace procedure emp_bonus(eno number,bonus out number)

is

temp number(9);

begin

select sal into temp from  emp where empno=eno;

if temp between  1 and 2000 then

bonus:=temp*0.15;

else if temp between 2001 and 5000 then

bonus:=temp*0.20;

else

bonus=temp*0.30;

end if;

end;

SQL>@demo2

procedure created

The following JDBC program is calling the above procedure using CallableStatement.

 

Example

 

import java.sql.*;

import java.util.*;

public class CallableTest2

{

              public static void main (String args [ ])throws Exception

              {

                      //load the type-4 driver

                      Class.forName(“oracle.jdbc.driver.OracleDriver”);

                      //get connection.

                      Connection cn=DriverManager.getConnection (“jdbc:orac1e:thin:@localhost:1521:XE”,”system”,”manager”);

                      //create callableStatement object

                      CallableStatement cs=cn.prepareCall (“{call emp_bonus(?,?) }”);

                      Scanner sc=new Scanner(System.in);

                      System.out.println(“Enter the employee no”);

                      int eno=sc.nextInt();

                      //set IN parameter

                      cs.setInt (1, eno);

                      //register out parameter

                      cs.registerOutParameter(2,Types.DOUBLE);

                      //call the procedure

                      cs.execute();

                      //read out parameter

                      double d=cs.getDouble (2);

                      System.out.println (“emp no”+eno);

                      System.out.println(“empbonus”+d);

                      cs.close();

                      cn.close();

               }

}

 

Output;

Enter employee no

7900

Emp no 7900

Emp bonus 1000.0.

 

Syntax to Create Function in Oracle Database Server

Create or replace function functionname (parameter)

Return returntype is

declare variables;

begin

statements;

end;

 

Note

• By default parameter of function is IN and IN is not allowed to specify IN parameter.

• For a function return type is mandatory.

• Within the body of function statement is mandatory.

The following function is created for finding the sum of salaries of employees working in a given department, by accepting depno as input.

sql>ed demo3

create or replace function sum_dept(dno number)

return is

temp number(q)

begin

select sum(sql) into temp from employee where depno=dno

end;

sql>@demo3

Function created

 

• The following JDBC program is calling the above function using CallableStatement.

 

Example

 

import java.sql.*;

import java.util.*;

public class CallableTest3

{

               public static void main(String args[ ]) throws Exception

              {

                   //load the type-4 driver

                   Class.forName(“oracle.jdbc.driver.OracleDriver”);

                   //get connection

                   Connection cn=DriverManager.getConnection (“jdbc:oracle:thin:@locdlhost:1521:XE”,”system”,”manager”);

                   //create CallableStatememt object

                   CallableStatement cs=cn.prepareCall (“{? =call sum_dept(?) }”);

                   // register OUT parameter

                   cs.registerOutParameter(1,Types.INTEGER);

                   Scanner sc=new Scanner (System.in) ;

                   System.out.println(“Enter deptno”);

                   int dno=sc.nextInt ();

                   //set IN parameter

                   cs.setInt(2,dno) ;

                   //call the procedure

                   cs.execute ( ) ;

                   //read OUT parameter

                   int k=cs.getInt (1) ;

                   System.out.println(“sal sum is ” +k) ;

                   cs.close() ;

                   cn.close() ;

               }

}

 

Output:

Enter depno

30

Sum of sal=2000

You’ll also like:

  1. How to Working With Prepared Statement
  2. How to Working With Resultset Interface
  3. Working with Applets
  4. How to Working with JDBC
  5. Working of an Interactive Graphics Display
Next →
← Prev
Like/Subscribe us for latest updates     

About Dinesh Thakur
Dinesh ThakurDinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps.

Dinesh Thakur is a Freelance Writer who helps different clients from all over the globe. Dinesh has written over 500+ blogs, 30+ eBooks, and 10000+ Posts for all types of clients.


For any type of query or something that you think is missing, please feel free to Contact us.


Primary Sidebar

SQL Tutorials

SQL Tutorials

  • SQL - Home
  • SQL - Select
  • SQL - Create
  • SQL - View
  • SQL - Sub Queries
  • SQL - Update
  • SQL - Delete
  • SQL - Order By
  • SQL - Select Distinct
  • SQL - Group By
  • SQL - Where Clause
  • SQL - Select Into
  • SQL - Insert Into
  • SQL - Sequence
  • SQL - Constraints
  • SQL - Alter
  • SQL - Date
  • SQL - Foreign Key
  • SQL - Like Operator
  • SQL - CHECK Constraint
  • SQL - Exists Operator
  • SQL - Drop Table
  • SQL - Alias Syntax
  • SQL - Primary Key
  • SQL - Not Null
  • SQL - Union Operator
  • SQL - Unique Constraint
  • SQL - Between Operator
  • SQL - Having Clause
  • SQL - Isnull() Function
  • SQL - IN Operator
  • SQL - Default Constraint
  • SQL - Minus Operator
  • SQL - Intersect Operator
  • SQL - Triggers
  • SQL - Cursors

Advanced SQL

  • SQL - Joins
  • SQL - Index
  • SQL - Self Join
  • SQL - Outer Join
  • SQL - Join Types
  • SQL - Cross Join
  • SQL - Left Outer Join
  • SQL - Right Join
  • SQL - Drop Index
  • SQL - Inner Join
  • SQL - Datediff() Function
  • SQL - NVL Function
  • SQL - Decode Function
  • SQL - Datepart() Function
  • SQL - Count Function
  • SQL - Getdate() Function
  • SQL - Cast() Function
  • SQL - Round() Function

Other Links

  • SQL - PDF Version

Footer

Basic Course

  • Computer Fundamental
  • Computer Networking
  • Operating System
  • Database System
  • Computer Graphics
  • Management System
  • Software Engineering
  • Digital Electronics
  • Electronic Commerce
  • Compiler Design
  • Troubleshooting

Programming

  • Java Programming
  • Structured Query (SQL)
  • C Programming
  • C++ Programming
  • Visual Basic
  • Data Structures
  • Struts 2
  • Java Servlet
  • C# Programming
  • Basic Terms
  • Interviews

World Wide Web

  • Internet
  • Java Script
  • HTML Language
  • Cascading Style Sheet
  • Java Server Pages
  • Wordpress
  • PHP
  • Python Tutorial
  • AngularJS
  • Troubleshooting

 About Us |  Contact Us |  FAQ

Dinesh Thakur is a Technology Columinist and founder of Computer Notes.

Copyright © 2025. All Rights Reserved.

APPLY FOR ONLINE JOB IN BIGGEST CRYPTO COMPANIES
APPLY NOW