// --------------------------------------------------------------------
// Example SQLJ program to execute a database procedure/ function with
// known IN OUT arguments.
//                                              Frank Naude - Oct 2000
// --------------------------------------------------------------------

import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;

public class SQLJTst4 {

  public static void main(String[] args) {
    Connection c = null;
    String URL = "jdbc:oracle:thin:@oracle10:1521:acme";
    String userid = "acme";
    String passwd = "acme";

    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      c = DriverManager.getConnection(URL, userid, passwd);
      System.out.println("Successfully conencted to Oracle...");

      // You need to set a Context for SQLJ, otherwise -
      //    SQLException: found null connection context
      DefaultContext.setDefaultContext( new DefaultContext(c));

      String lowerVal = "hi";
      String upperVal;

      // Note :in is used for input parameters, :out for output parms

      // Call procedures with CALL
      #sql { CALL dbms_output.put_line(:in lowerVal) };

      // Call functions with VALUES
      #sql upperVal = { VALUES( upper(:in lowerVal) ) };
      System.out.println("Upper case value is : " + upperVal);

      // Close the database connection
      #sql { rollback work };
      c.close();
      System.out.println("Disconected...");

    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}