Workers_OJVM.sql

REM
REM
REM ------------------------------------------------------------------------------
REM Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
REM
REM Portions Copyright 2006-2015, Kuassi Mensah. All rights reserved.
REM https://www.amazon.com/dp/1555583296
REM Adapated from existing JDBC demo.
REM
REM ------------------------------------------------------------------------------
REM DESCRIPTION
REM The following code sample runs directly in the database (using OJVM).
REM It retrieves a worker from a database, then updates its position and salary.
REM
create or replace and resolve java source named Workers as

import java.sql.*;
import oracle.jdbc.driver.*;

public class Workers 
{

 public static void main (String args []) throws SQLException {

 String name =null;
 String pos =null;
int sal;
int id;
 long t0,t1;
 Connection conn =null;
 Statement stmt =null;
 PreparedStatement pstmt =null;

 if ( args.length<1 ) {
System.err.println("Usage: Java Workers <wid> <new position> <new salary>");
System.exit(1);
 }

// Get parameters value
 id =Integer.parseInt(args[0]);
 pos = args[1];
 sal =Integer.parseInt(args[2]); 

/*
 * Where is your code running: in the database or outside?
*/
 if (System.getProperty("oracle.jserver.version") !=null)
 {
/*
 * You are in the database, already connected, use the default 
 * connection
*/
 conn =DriverManager.getConnection("jdbc:default:connection:");
System.out.println ("Running in OracleJVM, in the database!");
 }
 else
 {
/*
 * You are not in the database, you need to connect to 
 * the database
*/

DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); 
 conn =DriverManager.getConnection("jdbc:oracle:thin:", 
"testuser", "<your_db_password>");
System.out.println ("Running in JDK VM, outside the database!");
 }

/*
 * Auto commit is off by default in OJVM
 * Set auto commit off in client JDBC
*/
conn.setAutoCommit (false);

// Start timing
 t0=System.currentTimeMillis(); 

/*
 * find the name of the workers given his id number
*/

// create statement
 stmt =conn.createStatement();

// find the name of the worker 
 ResultSet rset =stmt.executeQuery(
"SELECT WNAME FROM workers WHERE wid = "+ id);

// retrieve and print the result (we are only expecting 1 row
 while (rset.next()) 
 {
 name =rset.getString(1);
 }

// return the name of the worker who has the given worker number
System.out.println ("Worker Name: "+ name);

/*
 * update the position and salary of the retrieved worker
*/

// prepare the update statement
 pstmt =conn.prepareStatement("UPDATE WORKERS SET WPOSITION = ?, "+
" WSALARY = ? WHERE WNAME = ?");

//set up bind valuesand execute the update
pstmt.setString(1, pos);
pstmt.setInt(2, sal);
pstmt.setString(3, name);
pstmt.execute();

// double-check (retrieve) the updated position and salary
 rset =stmt.executeQuery(
"SELECT WPOSITION, WSALARY FROM WORKERS WHERE WNAME = '"+
 name +"'");
 while (rset.next()) 
 {
 pos =rset.getString ("wposition");
 sal =rset.getInt ("wsalary");
 } 
System.out.println ("Worker: Id = "+ id +", Name = "+ name +
", Position = "+ pos +", Salary = "+ sal);

// Close the ResultSet
rset.close();

// Close the Statement
stmt.close();

// Stop timing
 t1=System.currentTimeMillis(); 
System.out.println ("====> Duration: "+(int)(t1-t0)+" Milliseconds");

// Close the connection
conn.close(); 
 }
 }

/
show errors;

create or replace procedure WorkerSP (wid INvarchar2,wpos IN
varchar2, wsal INvarchar2) as
language java name 'Workers.main(java.lang.String[])';
/
show errors;

set serveroutput on
call dbms_java.set_output(50000);
call WorkerSp('621', 'Senior VP', '650000');