Friday, November 25, 2011

How to Call Oracle PL SQL from Java

PL/SQL Stored Procedures


Oracle JDBC drivers support execution of PL/SQL stored procedures and anonymous blocks. They support both SQL92 escape syntax and Oracle PL/SQL block syntax. The following PL/SQL calls would work with any Oracle JDBC driver:
// SQL92 syntax
CallableStatement cs1 = conn.prepareCall
                       ( "{call proc (?,?)}" ) ; // stored proc
CallableStatement cs2 = conn.prepareCall
                       ( "{? = call func (?,?)}" ) ; // stored func
// Oracle PL/SQL block syntax
CallableStatement cs3 = conn.prepareCall
                       ( "begin proc (?,?); end;" ) ; // stored proc
CallableStatement cs4 = conn.prepareCall
                       ( "begin ? := func(?,?); end;" ) ; // stored func

As an example of using Oracle syntax, here is a PL/SQL code snippet that creates a stored function. The PL/SQL function gets a character sequence and concatenates a suffix to it:
create or replace function foo (val1 char)
return char as
begin
   return val1 || 'suffix';
end;
Your invocation call in your JDBC program should look like:
Connection conn = DriverManager.getConnection 
                  ("jdbc:oracle:oci:@", "scott", "tiger");

CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");
cs.registerOutParameter(1,Types.CHAR);
cs.setString(2, "aa");
cs.executeUpdate();
String result = cs.getString(1);

Calling PL SQL from Java


JDBC and SQLJ allow you to call PL/SQL stored functions and procedures. For example, suppose you want to call the following stored function, which returns the balance of a specified bank account:
FUNCTION balance (acct_id NUMBER) RETURN NUMBER IS
  acct_bal NUMBER;
BEGIN
  SELECT bal INTO acct_bal FROM accts
    WHERE acct_no = acct_id;
  RETURN acct_bal;
END;
From a JDBC program, your call to the function balance might look like this:
CallableStatement cstmt = conn.prepareCall("{? = CALL balance(?)}");
cstmt.registerOutParameter(1, Types.FLOAT);
cstmt.setInt(2, acctNo);
cstmt.executeUpdate();
float acctBal = cstmt.getFloat(1);

Calling a Stored Procedure in a Database

This example demonstrates how to call stored procedures with IN, OUT, and IN/OUT parameters.
CallableStatement cs;
    try {
      // Call a procedure with no parameters
        cs = connection.prepareCall("{call myproc}");
        cs.execute();
    
      // Call a procedure with one IN parameter
        cs = connection.prepareCall("{call myprocin(?)}");
    
        // Set the value for the IN parameter
        cs.setString(1, "a string");
    
        // Execute the stored procedure
        cs.execute();
    
      // Call a procedure with one OUT parameter
        cs = connection.prepareCall("{call myprocout(?)}");
    
        // Register the type of the OUT parameter
        cs.registerOutParameter(1, Types.VARCHAR);
    
        // Execute the stored procedure and retrieve the OUT value
        cs.execute();
        String outParam = cs.getString(1);     // OUT parameter
    
      // Call a procedure with one IN/OUT parameter
        cs = connection.prepareCall("{call myprocinout(?)}");
    
        // Register the type of the IN/OUT parameter
        cs.registerOutParameter(1, Types.VARCHAR);
    
        // Set the value for the IN/OUT parameter
        cs.setString(1, "a string");
    
        // Execute the stored procedure and retrieve the IN/OUT value
        cs.execute();
        outParam = cs.getString(1);            // OUT parameter
    } catch (SQLException e) {
    }

6 comments:

  1. I would like to say that this blog really convinced me to update my knowledge about the technology you talk about. Thanks, very good post.
    JAVA Training in Chennai
    JAVA Course in Chennai

    ReplyDelete
  2. Thanks for sharing this coding on how to Call Oracle PL SQL from Java. It is really helpful.
    Best JAVA Training in Chennai |
    JAVA Training | JAVA course in Chennai

    ReplyDelete
  3. when i train this structural query language it was very easy after learn this hints and points.i will be suggest this blog to all of one.

    i useful very much this.


    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery







    ReplyDelete