call procedure

How could i call procedure in oracle?


coudl you give me the sample code.

is it possible to call the procedure using preparetament
LVL 9
Manikandan ThiagarajanSenior consultantAsked:
Who is Participating?
 
KantiCommented:
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 (rset.next()) {
        standardRoomRate[0] = rset.getFloat(1); // Fetch Standard Room Rates
      }

      // Close the Result Set and Statement objects
      rset.close();
      stmt.close();

      // 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 (rset.next()) {

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

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

           ex.printStackTrace();
         }
    }
  }
}
0
 
Mayank SAssociate Director - Product EngineeringCommented:
You have to use CallableStatement which extends PreparedStatement.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.