call procedure

Posted on 2006-05-18
Last Modified: 2010-03-31
How could i call procedure in oracle?

coudl you give me the sample code.

is it possible to call the procedure using preparetament
Question by:stmani2005
    LVL 30

    Expert Comment

    You have to use CallableStatement which extends PreparedStatement.
    LVL 30

    Assisted Solution

    LVL 3

    Accepted Solution

    This might help

    package test;

    // JDBC classes
    import java.sql.SQLException;
    import java.sql.PreparedStatement;
    import java.sql.Connection;
    import java.sql.ResultSet;

    //Oracle Extensions to JDBC

    import oracle.jdbc.driver.OracleDriver;

     * The file consists of a Java Method that is  used to demonstrate
     * following Java Stored Procedure. The Java method "getRoomDetails" gets
     * published to SQL and is stored in the Oracle database.
    public class RoomsInformationProcedure {

      * This method finds out the Room Details namely 'Number of Rooms Available'
      * and 'Standard Room Rates' for a given Hotel and Room Type
      * Note: 1) Input Parameters to this method are hotelId and roomType where as
      *          numRoomsAvailable and standardRoomRate are output parameters.
      *       2) Corresponding to IN OUT or OUT parameters of PL/SQL procedures,
      *          the Java Methods must have parameters that are a one-element array.

      public static void getRoomDetails(String hotelId, String roomType,
                        int[] numRoomsAvailable, float[] standardRoomRate) {

        Connection connection = null; // Database connection object
        try {
          // Get a Default Database Connection using Server Side JDBC Driver.
          // Note : This class will be loaded on the Database Server and hence use a
          // Server Side JDBC Driver to get default Connection to Database
          connection = new OracleDriver().defaultConnection();

          if (roomType.equals("ORCL"))
              roomType = "OTHR";

          // Query to find out Standard Room Rates for a given Hotel and Room Type
          PreparedStatement stmt = connection.prepareStatement
                                               ("SELECT standard_rate" +
                                  " FROM available_room_types  WHERE " +
                           " hot_id = TO_NUMBER(?) AND room_type = ? ");

          stmt.setString(1,hotelId);   // Bind the Hotel ID Input parameter
          stmt.setString(2,roomType);  // Bind the roomType Input parameter

          ResultSet rset = stmt.executeQuery(); // Execute the query, get Resultset
          // Loop through the Resultset and fetch the results
          while ( {
            standardRoomRate[0] = rset.getFloat(1); // Fetch Standard Room Rates

          // Close the Result Set and Statement objects

          // Query to find out total number of available rooms for a given Room Type
          // and Hotel ID

          stmt = connection.prepareStatement("SELECT TOTAL_"+ roomType +
                                             " FROM room_availability WHERE " +
                                             " hot_id = TO_NUMBER(?) AND  " +
                                             " booking_date = ( SELECT MAX(booking_date) " +
                                             " FROM room_availability  " +
                                             " WHERE hot_id = TO_NUMBER(?) )" );

          stmt.setString(1,hotelId); // Bind Input Hotel ID Parameter
          stmt.setString(2,hotelId); // Bind Input Hotel ID Parameter

          rset = stmt.executeQuery(); // Execute query and get ResultSet
          // Loop through the Resultset and fetch results
          while ( {

            numRoomsAvailable[0] = rset.getInt(1); // Get number of Rooms available

          // Close Resultset and Statement
        } catch (SQLException ex) { // Trap SQL Errors
        } finally {
             if (connection != null || !connection.isClosed())
                 connection.close();   // Close the database connection
             } catch(SQLException ex){


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Multiple issues with Java 14 37
    tenRun challenge 28 67
    Using object mapper in spring web application 8 39
    @SBGen Method 3 16
    Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
    Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
    Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
    This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now