example 1:
/*
* This sample shows how to call a PL/SQL stored procedure using the SQL92
* syntax. See also the other sample PLSQL.java.
*/
import java.sql.*;
import java.io.*;
class PLSQLExample
{
public static void main (String args [])
throws SQLException, IOException
{
// Load the driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String url = "jdbc:oracle:oci8:@";
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null)
url = url1;
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
}
// Connect to the database
Connection conn =
DriverManager.getConnection (url, "scott", "tiger");
// Create a statement
Statement stmt = conn.createStatement ();
// Create the stored function
stmt.execute ("create or replace function RAISESAL (name CHAR, raise NUMBER) return NUMBER is begin return raise + 100000; end;");
// Close the statement
stmt.close();
// Prepare to call the stored procedure RAISESAL.
// This sample uses the SQL92 syntax
CallableStatement cstmt = conn.prepareCall ("{? = call RAISESAL (?, ?)}");
// Declare that the first ? is a return value of type Int
cstmt.registerOutParameter (1, Types.INTEGER);
// We want to raise LESLIE's salary by 20,000
cstmt.setString (2, "LESLIE"); // The name argument is the second ?
cstmt.setInt (3, 20000); // The raise argument is the third ?
// Do the raise
cstmt.execute ();
// Get the new salary back
int new_salary = cstmt.getInt (1);
System.out.println ("The new salary is: " + new_salary);
// Close the statement
cstmt.close();
// Close the connection
conn.close();
}
}
example 2:
/*
* Created on 2004-10-12
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
/**
* @author Jackey
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
/*
* This sample can be used to check the JDBC installation.
* Just run it and provide the connect information. It will select
* "Hello World" from the database.
*/
// You need to import the java.sql package to use JDBC
import java.sql.*;
// We import java.io to be able to read from the command line
import java.io.*;
import oracle.jdbc.OracleTypes;
class JdbcCheckup
{
public static void main(String args[])
throws SQLException, IOException
{
// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Prompt the user for connect information
System.out.println("Please enter information to test connection to the database");
String user;
String password;
String database;
user = readEntry("user: ");
int slash_index = user.indexOf('/');
if (slash_index != -1)
{
password = user.substring(slash_index + 1);
user = user.substring(0, slash_index);
}
else
password = readEntry("password: ");
database = readEntry("database(a TNSNAME entry): ");
System.out.print("Connecting to the database...");
System.out.flush();
System.out.println("Connecting...");
Connection conn = DriverManager.getConnection
("jdbc:oracle:oci8:@" + database, user, password);
System.out.println("connected.");
// Create a statement
Statement stmt = conn.createStatement();
// Do the SQL "Hello World" thing
ResultSet rset = stmt.executeQuery("select 'Hello World' from dual");
while (rset.next())
System.out.println(rset.getString(1));
// close the result set, the statement and connect
rset.close();
stmt.close();
System.out.println("Your JDBC installation is correct.");
//
CallableStatement call = conn.prepareCall("{call Emp_dept_data.Open_cv(?,?)}");
// Find out all the SALES person
// call.registerOutParameter (1, OracleTypes.CURSOR);
call.registerOutParameter (1 , OracleTypes.CURSOR);
call.setInt(2, 1);
call.execute ();
ResultSet rs = (ResultSet)call.getObject (1);
// Dump the cursor
while (rs.next ())
System.out.println (rs.getString (1)+"\t"+ rs.getString(2)+"\t"+ rs.getString(3));
rs.close();
call.close();
conn.close();
}
// Utility function to read a line from standard input
static String readEntry(String prompt)
{
try
{
StringBuffer buffer = new StringBuffer();
System.out.print(prompt);
System.out.flush();
int c = System.in.read();
while (c != '\n' && c != -1)
{
buffer.append((char)c);
c = System.in.read();
}
return buffer.toString().trim();
}
catch(IOException e)
{
return "";
}
}
}
