Java calls the Oracle stored procedure and returns the result set

Create or replace Package Types
As
Type ref_cursor is ref cursor;
End;
/

Create Table stock_prices (
Ric varchar (6) primary key,
Price number (7,2 ),
Updated Date );
/

Create or replace function sp_get_stocks (v_price in number)
Return types. ref_cursor
As
Stock_cursor types. ref_cursor;
Begin
Open stock_cursor
Select Ric, price, updated from stock_prices where price <v_price;

Return stock_cursor;
End;

2. Use sqlplus for testing

SQL> var results refcursor
SQL> Exec: Results: = sp_get_stocks (20.0)
SQL> print results

3. Call from Java

Import java. SQL .*;
Import java. Io .*;
Import oracle. JDBC. Driver .*;

Public class jdbcdemo {

/**
* Compile-time flag for deciding which query to use
*/
Private Boolean useoraclequery = true;

/**
* Class Name of oracle JDBC driver
*/
Private string driver = "oracle. JDBC. Driver. oracledriver ";

/**
* Initial URL Fragment
*/
Private string url = "JDBC: oracle: thin :@";

/**
* Standard Oracle listener Port
*/
Private string port = "1521 ";

/**
* Oracle style of calling a stored procedure
*/
Private string oraclequery = "begin? : = Sp_get_stocks (?); End ;";

/**
* JDBC style of calling a stored procedure
*/
Private string genericquery = "{call? : = Sp_get_stocks (?) }";

/**
* Connection to database
*/
Private connection conn = NULL;

/**
* Constructor. loads the JDBC driver and establishes a connection
*
* @ Param host the DB is on
* @ Param dB the Database Name
* @ Param user's name
* @ Param password user's password
*/
Public jdbcdemo (string host, string dB, string user, string password)
Throws classnotfoundexception, sqlexception {

// Construct the URL
Url = URL + host + ":" + port + ":" + dB;

// Load the Oracle driver and establish a connection
Try {
Class. forname (driver );
Conn = drivermanager. getconnection (URL, user, password );
}
Catch (classnotfoundexception ex ){
System. Out. println ("failed to find driver class:" + driver );
Throw ex;
}
Catch (sqlexception ex ){
System. Out. println ("failed to establish a connection to:" + URL );
Throw ex;
}
}

/**
* Execute the Stored Procedure
*
* @ Param Price parameter for Stored Procedure
*/
Private void execute (float price)
Throws sqlexception {

String query = useoraclequery? Oraclequery: genericquery;
System. Out. println ("query:" + query + "N ");
Callablestatement stmt = conn. preparecall (query );

// Register the type of the out param-an oracle specific type
Stmt. registeroutparameter (1, oracletypes. cursor );

// Set the In Param
Stmt. setfloat (2, price );

// Execute and retrieve the result set
Stmt.exe cute ();
Resultset rs = (resultset) stmt. GetObject (1 );

// Print the results
While (Rs. Next ()){
System. Out. println (Rs. getstring (1) + "T" +
Rs. getfloat (2) + "T" +
Rs. getdate (3). tostring ());
}

Rs. Close ();
Stmt. Close ();
}

/**
* Cleanup the connection
*/
Private void cleanup () throws sqlexception {

If (Conn! = NULL)
Conn. Close ();
}

/**
* Prints usage Statement on stdout
*/
Static private void usage (){

System. Out. println ("Java com. enterprisedt. Demo. Oracle. jdbcdemo" +
"Host dB User Password price ");
}

/**
* Runs the class
*/
Public static void main (string [] ARGs) throws exception {

If (ARGs. length! = 5 ){
Jdbcdemo. usage ();
System. Exit (1 );
}
Else {
Try {
// Assign the ARGs to sensible variables for clarity
String host = ARGs [0];
String DB = ARGs [1];
String user = ARGs [2];
String Password = ARGs [3];
Float price = float. valueof (ARGs [4]). floatvalue ();

// And execute the stored proc
Jdbcdemo JDBC = new jdbcdemo (host, DB, user, password );
Jdbc.exe cute (price );
JDBC. Cleanup ();
}
Catch (classnotfoundexception ex ){
System. Out. println ("demo failed ");
}
Catch (sqlexception ex ){
System. Out. println ("demo failed:" + ex. getmessage ());
}
}
}
}