Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

call procedure

Posted on 2006-05-18
3
Medium Priority
?
242 Views
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
0
Comment
  • 2
3 Comments
 
LVL 30

Expert Comment

by:Mayank S
ID: 16707980
You have to use CallableStatement which extends PreparedStatement.
0
 
LVL 30

Assisted Solution

by:Mayank S
Mayank S earned 100 total points
ID: 16707985
0
 
LVL 3

Accepted Solution

by:
Kanti earned 200 total points
ID: 16710539
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
This video teaches viewers about errors in exception handling.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month13 days, 7 hours left to enroll

581 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