Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
Library | Service | Contents | Index

## Advanced Samples

This section presents examples that demonstrate some of the relatively advanced features of SQLJ. The following samples are included:

- REF CURSOR--RefCursDemo.sqlj

- Multithreading--MultiThreadDemo.sqlj

- Interoperability with JDBC--JDBCInteropDemo.sqlj

- Multiple Connection Contexts--MultiSchemaDemo.sqlj

- Data Manipulation and Multiple Connection Contexts--QueryDemo.sqlj

- Subclassing Iterators--SubclassIterDemo.sqlj

- PL/SQL in SQLJ for Dynamic SQL--DynamicDemo.sqlj

These samples are located in the following directory:

    [Oracle Home]/sqlj/demo

### REF CURSOR--RefCursDemo.sqlj

This example shows the use of a `REF CURSOR` type in an anonymous block, a stored procedure, and a stored function.

The PL/SQL code used to create the procedure and function is also shown.

For information about `REF CURSOR` types, see "Support for Oracle REF CURSOR Types".

#### Definition of REF CURSOR Stored Procedure and Stored Function

This section contains the PL/SQL code that defines the following:

- a stored procedure that returns a `REF CURSOR` type as an `OUT` parameter

- a stored function that returns a `REF CURSOR` type as a result

      create or replace package SQLJRefCursDemo as
        type EmpCursor is ref cursor;
        procedure RefCursProc( name VARCHAR,
                               no NUMBER,
                               empcur OUT EmpCursor);

        function RefCursFunc (name VARCHAR, no NUMBER)  return EmpCursor;

      end SQLJRefCursDemo;
      /

      create or replace package body SQLJRefCursDemo is

        procedure RefCursProc( name VARCHAR,
                               no NUMBER,
                               empcur OUT EmpCursor)
           is begin
             insert into emp (ename, empno) values (name, no);
             open empcur for select ename, empno from emp
                             order by empno;
           end;

        function RefCursFunc (name VARCHAR, no NUMBER) return EmpCursor  is
          empcur EmpCursor;
          begin
             insert into emp (ename, empno) values (name, no);
             open empcur for select ename, empno from emp
                             order by empno;
             return empcur;
           end;
      end SQLJRefCursDemo;
      /
      exit
      /

#### REF CURSOR Sample Application Source Code

This application retrieves a `REF CURSOR` type from the following:

- an anonymous block

- a stored procedure (as an `OUT` parameter)

- a stored function (as a return value)

A `ROLLBACK` operation is executed before closing the connection, so that the database is not permanently altered.

    import java.sql.*;
    import oracle.sqlj.runtime.Oracle;

    public class RefCursDemo
    {
      #sql public static iterator EmpIter (String ename, int empno);

      public static void main (String argv[]) throws SQLException
      {
        String name;  int no;
        EmpIter emps = null;

        /* if you're using a non-Oracle JDBC Driver, add a call here to
           DriverManager.registerDriver() to register your Driver
        */

        /* Connect to the database */
        Oracle.connect(RefCursDemo.class, "connect.properties");

        try
        {
           name = "Joe Doe"; no = 8100;
           emps = refCursInAnonBlock(name, no);
           printEmps(emps);

           name = "Jane Doe"; no = 8200;
           emps = refCursInStoredProc(name, no);
           printEmps(emps);

           name = "Bill Smith"; no = 8300;
           emps = refCursInStoredFunc(name, no);
           printEmps(emps);
        }
        finally
        {
          #sql { ROLLBACK };
          Oracle.close();
        }
      }

      private static EmpIter refCursInAnonBlock(String name, int no)
        throws java.sql.SQLException {
        EmpIter emps = null;

        System.out.println("Using anonymous block for ref cursor..");
        #sql { begin
                 insert into emp (ename, empno) values (:name, :no);
                 open :out emps for select ename, empno from emp
                               order by empno;
               end;
             };
        return emps;
      }

      private static EmpIter refCursInStoredProc (String name, int no)
        throws java.sql.SQLException {
        EmpIter emps = null;
        System.out.println("Using stored procedure for ref cursor..");
        #sql { CALL SQLJREFCURSDEMO.REFCURSPROC (:IN name, :IN no, :OUT emps)
             };
        return emps;
      }

      private static EmpIter refCursInStoredFunc (String name, int no)
        throws java.sql.SQLException {
        EmpIter emps = null;
        System.out.println("Using stored function for ref cursor..");
        #sql emps = {  VALUES (SQLJREFCURSDEMO.REFCURSFUNC(:name, :no))
                    };
        return emps;
      }

      private static void printEmps(EmpIter emps)
        throws java.sql.SQLException {
        System.out.println("Employee list:");
        while (emps.next()) {
           System.out.println("\t Employee name: " + emps.ename() +
                              ", id : " + emps.empno());
        }
        System.out.println();
        emps.close();
      }
    }

### Multithreading--MultiThreadDemo.sqlj

The following is an example of a SQLJ application using multithreading. See "Multithreading in SQLJ" for information about multithreading considerations in SQLJ.

A `ROLLBACK` operation is executed before closing the connection, so that the database is not permanently altered.

    import java.sql.SQLException;
    import java.util.Random;
    import sqlj.runtime.ExecutionContext;
    import oracle.sqlj.runtime.Oracle;

    /**
      Each instance of MultiThreadDemo is a thread that gives all employees
      a raise of some ammount when run.  The main program creates two such
      instances and computes the net raise after both threads have completed.
      **/
    class MultiThreadDemo extends Thread
    {
      double raise;
      static Random randomizer = new Random();

      public static void main (String args[])
      {
        try {
          /* if you're using a non-Oracle JDBC Driver, add a call here to
             DriverManager.registerDriver() to register your Driver
          */

          // set the default connection to the URL, user, and password
          // specified in your connect.properties file
          Oracle.connect(MultiThreadDemo.class, "connect.properties");
          double avgStart = calcAvgSal();
          MultiThreadDemo t1 = new MultiThreadDemo(250.50);
          MultiThreadDemo t2 = new MultiThreadDemo(150.50);
          t1.start();
          t2.start();
          t1.join();
          t2.join();
          double avgEnd = calcAvgSal();
          System.out.println("average salary change: " + (avgEnd - avgStart));
        } catch (Exception e) {
          System.err.println("Error running the example: " + e);
        }

        try { #sql { ROLLBACK }; Oracle.close(); } catch (SQLException e) { }
      }

      static double calcAvgSal() throws SQLException
      {
        double avg;
        #sql { SELECT AVG(sal) INTO :avg FROM emp };
        return avg;
      }

      MultiThreadDemo(double raise)
      {
        this.raise = raise;
      }

      public void run()
      {
        // Since all threads will be using the same default connection
        // context, each run uses an explicit execution context instance to
        // avoid conflict during execution
        try {
          delay();
          ExecutionContext execCtx = new ExecutionContext();
          #sql [execCtx] { UPDATE EMP SET sal = sal + :raise };
          int updateCount = execCtx.getUpdateCount();
          System.out.println("Gave raise of " + raise + " to " +
                              updateCount + " employees");
        } catch (SQLException e) {
          System.err.println("error updating employees: " + e);
        }
      }

      // delay is used to introduce some randomness into the execution order
      private void delay()
      {
        try {
          sleep((long)Math.abs(randomizer.nextInt()/10000000));
        } catch (InterruptedException e) {}
      }
    }

### Interoperability with JDBC--JDBCInteropDemo.sqlj

The following example uses JDBC to perform a dynamic query, casts the JDBC result set to a SQLJ iterator, and uses the iterator to view the results. It demonstrates how SQLJ and JDBC can interoperate in the same program.

For information about SQLJ-JDBC interoperability, see "SQLJ and JDBC Interoperability".

    import java.sql.*;
    import sqlj.runtime.ref.DefaultContext;
    import oracle.sqlj.runtime.Oracle;

    public class JDBCInteropDemo
    {
      // in this example, we use an iterator that is inner class
      #sql public static iterator Employees ( String ename, double sal ) ;

      public static void main(String[] args) throws SQLException
      {
        if (args.length != 1) {
          System.out.println("usage: JDBCInteropDemo <whereClause>");
          System.exit(1);
        }

        /* if you're using a non-Oracle JDBC Driver, add a call here to
           DriverManager.registerDriver() to register your Driver
        */

        // set the default connection to the URL, user, and password
        // specified in your connect.properties file
        Oracle.connect(JDBCInteropDemo.class, "connect.properties");

        try
        {
           Connection conn = DefaultContext.getDefaultContext().getConnection();

           // create a JDBCStatement object to execute a dynamic query
           Statement stmt = conn.createStatement();
           String query = "SELECT ename, sal FROM emp WHERE ";
           query += args[0];

           // use the result set returned by executing the query to create
           // a new strongly-typed SQLJ iterator
           ResultSet rs = stmt.executeQuery(query);
           Employees emps;
           #sql emps = { CAST :rs };

           while (emps.next()) {
             System.out.println(emps.ename() + " earns " + emps.sal());
           }
           emps.close();
           stmt.close();
        }
        finally
        {
          Oracle.close();
        }
      }
    }

### Multiple Connection Contexts--MultiSchemaDemo.sqlj

The following is an example of a SQLJ application using multiple connection contexts. It implicitly uses an instance of the `DefaultContext` class for operations that use one set of SQL objects, and uses an instance of the declared connection context class `DeptContext` for operations that use another set of SQL objects.

This example uses the static `Oracle.connect()` method to establish a default connection, then constructs an additional connection by using the static `Oracle.getConnection()` method to pass another `DefaultContext` instance to the `DeptContext` constructor. As previously mentioned, this is just one of several ways you can construct a SQLJ connection context instance. This example is repeated in "Connection Contexts". You can refer to that section for information about multiple and non-default connection contexts.

    import java.sql.SQLException;
    import oracle.sqlj.runtime.Oracle;

    // declare a new context class for obtaining departments
    #sql context DeptContext;

    #sql iterator Employees (String ename, int deptno);

    class MultiSchemaDemo
    {
      public static void main(String[] args) throws SQLException
      {
        /* if you're using a non-Oracle JDBC Driver, add a call here to
           DriverManager.registerDriver() to register your Driver
        */

        // set the default connection to the URL, user, and password
        // specified in your connect.properties file
        Oracle.connect(MultiSchemaDemo.class, "connect.properties");

        try
        {
           // create a context for querying department info using
           // a second connection
           DeptContext deptCtx =
             new DeptContext(Oracle.getConnection(MultiSchemaDemo.class,
                                                 "connect.properties"));

           new MultiSchemaDemo().printEmployees(deptCtx);
           deptCtx.close();
        }
        finally
        {
          Oracle.close();
        }
      }

      // performs a join on deptno field of two tables accessed from
      // different connections.
      void printEmployees(DeptContext deptCtx) throws SQLException
      {
        // obtain the employees from the default context
        Employees emps;
        #sql emps = { SELECT ename, deptno FROM emp };

        // for each employee, obtain the department name
        // using the dept table connection context
        while (emps.next()) {
          String dname;
          int deptno = emps.deptno();
          #sql [deptCtx] {
            SELECT dname INTO :dname FROM dept WHERE deptno = :deptno
          };
          System.out.println("employee: " +emps.ename() +
                             ", department: " + dname);
        }
        emps.close();
      }
    }

### Data Manipulation and Multiple Connection Contexts--QueryDemo.sqlj

This demo demonstrates programming constructs that you can use to fetch rows of data using SQLJ and also shows the use of multiple connection contexts.

This sample uses the stored procedure `GET_SAL`, defined as follows:

    -- SQL script for the QueryDemo

    CREATE OR REPLACE FUNCTION get_sal(name VARCHAR) RETURN NUMBER IS
      sal NUMBER;
    BEGIN
      SELECT sal INTO sal FROM emp WHERE ENAME = name;
      RETURN sal;
    END get_sal;
    /

    EXIT
    /

Sample application source code follows.

A `ROLLBACK` operation is executed before closing the connection, so that the database is not permanently altered.

    // Source code for the QueryDemo

    import java.sql.SQLException;
    import oracle.sqlj.runtime.Oracle;
    import sqlj.runtime.ref.DefaultContext;

    #sql context QueryDemoCtx ;

    #sql iterator SalByName (double sal, String ename) ;

    #sql iterator SalByPos (double, String ) ;

    /**
      This sample program demonstrates the various constructs that may be
      used to fetch a row of data using SQLJ.  It also demonstrates the
      use of explicit and default connection contexts.
      **/
    public class QueryDemo
    {
      public static void main(String[] args) throws SQLException
      {
        if (args.length != 2) {
          System.out.println("usage: QueryDemo ename newSal");
          System.exit(1);
        }

        /* if you're using a non-Oracle JDBC Driver, add a call here to
           DriverManager.registerDriver() to register your Driver
        */

        // set the default connection to the URL, user, and password
        // specified in your connect.properties file
        Oracle.connect(QueryDemo.class, "connect.properties");

        try
        {
           QueryDemoCtx ctx =
             new QueryDemoCtx(DefaultContext.getDefaultContext().getConnection());
           String ename = args[0];
           int newSal = Integer.parseInt(args[1]);

           System.out.println("before update:");
           getSalByName(ename, ctx);
           getSalByPos(ename);

           updateSal(ename, newSal, ctx);

           System.out.println("after update:");
           getSalByCall(ename, ctx);
           getSalByInto(ename);
           ctx.close(ctx.KEEP_CONNECTION);
        }
        finally
        {
          #sql { ROLLBACK };
          Oracle.close();
        }
      }

      public static void getSalByName(String ename, QueryDemoCtx ctx)
        throws SQLException
      {
        SalByName iter = null;
        #sql [ctx] iter = { SELECT ename, sal FROM emp WHERE ename = :ename };
        while (iter.next()) {
          printSal(iter.ename(), iter.sal());
        }
        iter.close();
      }

      public static void getSalByPos(String ename) throws SQLException
      {
        SalByPos iter = null;
        double sal = 0;
        #sql iter = { SELECT sal, ename FROM emp WHERE ename = :ename };
        while (true) {
          #sql { FETCH :iter INTO :sal, :ename };
          if (iter.endFetch()) break;
          printSal(ename, sal);
        }
        iter.close();
      }

      public static void updateSal(String ename, int newSal, QueryDemoCtx ctx)
        throws SQLException
      {
        #sql [ctx] { UPDATE emp SET sal = :newSal WHERE ename = :ename };
      }

      public static void getSalByCall(String ename, QueryDemoCtx ctx)
        throws SQLException
      {
        double sal = 0;
        #sql [ctx] sal = { VALUES(get_sal(:ename)) };
        printSal(ename, sal);
      }

      public static void getSalByInto(String ename)
        throws SQLException
      {
        double sal = 0;
        #sql { SELECT sal INTO :sal FROM emp WHERE ename = :ename };
        printSal(ename, sal);
      }

      public static void printSal(String ename, double sal)
      {
        System.out.println("salary of " + ename + " is " + sal);
      }
    }

### Subclassing Iterators--SubclassIterDemo.sqlj

This sample shows the usefulness of subclassing an iterator class, in this case to add behavior that writes all the rows of a query result into a Java vector.

See "Subclassing Iterator Classes" for a general discussion.

    // ----------------- Begin of file SubclassIterDemo.sqlj ----------------------
    //
    // Invoke the SQLJ translator with the following command:
    //    sqlj SubclassIterDemo.sqlj
    // Then run as
    //    java SubclassIterDemo

    /* Import useful classes.
    **
    ** Note that java.sql.Date (and not java.util.Date) is being used.
    */

    import java.util.Vector;
    import java.util.Enumeration;
    import java.sql.SQLException;

    import sqlj.runtime.profile.RTResultSet;
    import oracle.sqlj.runtime.Oracle;

    public class SubclassIterDemo
    {
      // Declare an iterator
      #sql public static iterator EmpIter(int empno, String ename);

      // Declare Emp objects
      public static class Emp
      {
        public Emp(EmpIter iter) throws SQLException
        { m_name=iter.ename(); m_id=iter.empno(); }

        public String getName() { return m_name; }
        public int getId()      { return m_id; }

        public String toString() { return "EMP "+getName()+" has ID "+getId(); }

        private String m_name;
        private int m_id;
      }

      // Declare an iterator subclass. In this example we add behavior to add
      // all rows of the query as a Vector.

      public static class EmpColl extends EmpIter
      {
         // We _must_ provide a constructor for sqlj.runtime.RTResultSet
         // This constructor is called in the assignment of EmpColl to a query.
         public EmpColl(RTResultSet rs) throws SQLException
         { super(rs); }

         // Materialize the result as a vector
         public Vector getEmpVector() throws SQLException
         { if (m_v==null) populate(); return m_v; }

         private Vector m_v;
         private void populate() throws SQLException
         {
           m_v = new Vector();
           while (super.next())
           { m_v.addElement(new Emp(this)); }
           super.close();
         }
      }

      public static void main( String args[] )
      {
        try
        {
          SubclassIterDemo app = new SubclassIterDemo();
          app.runExample();
        }
        catch( SQLException exception )
        {
          System.err.println( "Error running the example: " + exception );
        }
        finally
        {
          try { Oracle.close(); } catch (SQLException e) { }
        }
      }

      /* Initialize database connection.
      **
      */

      SubclassIterDemo() throws SQLException
      {
        Oracle.connect(getClass(), "connect.properties");
      }

      void runExample() throws SQLException
      {
        System.out.println();
        System.out.println( "Running the example." );
        System.out.println();

        EmpColl ec;
        #sql ec = { select ename, empno from emp };

        Enumeration enum = ec.getEmpVector().elements();
        while (enum.hasMoreElements())
        {
          System.out.println(enum.nextElement());
        }
      }

    }

### PL/SQL in SQLJ for Dynamic SQL--DynamicDemo.sqlj

This section shows how to use PL/SQL blocks to implement dynamic SQL in a SQLJ application.

A `ROLLBACK` operation is executed before closing the connection, so that the database is not permanently altered.

    import java.sql.*;
    import sqlj.runtime.ref.DefaultContext;
    import oracle.sqlj.runtime.Oracle;

    public class DynamicDemo
    {

      public static void main(String[] args) throws SQLException
      {
        // set the default connection to the URL, user, and password
        // specified in your connect.properties file
        Oracle.connect(DynamicDemo.class, "connect.properties");

        try
        {
           String table_name = "emp";

           // dynamic DDL

           String index_name = "sal_index";
           String index_col = "sal";
           dynamicDrop(index_name);
           dynamicCreate(table_name, index_name, index_col);

           // dynamic DML

           String ename;
           int empno;  double sal;

           ename = "julie"; empno = 8455;  sal = 3500;
           dynamicInsert(table_name, ename, empno, sal);

           dynamicDelete("empno", "8455");

           empno = 7788;  sal = 6000.00;
           staticUpdateReturning(empno, sal);  // try static first :)

           empno = 7788; sal = 7000.00;
           dynamicUpdateReturning(empno, sal);

           // dynamic 1-row query
           dynamicSelectOne(table_name);

           // dynamic multi-row query
           dynamicSelectMany(" sal > 2000.00");
           dynamicSelectMany(null);
        }
        finally
        {
           #sql { ROLLBACK };
           Oracle.close();
        }
      }

      private static void dynamicDrop (String index_name) throws SQLException {

         System.out.println("Dropping index " + index_name);
         #sql {  begin
                    execute immediate 'drop index ' || :index_name;
                 end;
              };
      }

       private static void dynamicCreate(String table_name,
                                         String index_name, String index_col )
               throws SQLException {

         System.out.println("Creating index " + index_name + " for table "
                            + table_name + " on column " + index_col);

         String ddl = "create index " + index_name + " on " + table_name +
                      "(" + index_col + ")";
         #sql {  begin
                    execute immediate :ddl;
                 end;
              };
       }

      private static void dynamicInsert(String which_table, String ename,
                                        int empno, double sal)
              throws SQLException {

        System.out.println("dynamic insert on table " + which_table
                           + " of employee " + ename);
        #sql { begin
                     execute immediate
                    'insert into ' || :which_table ||
                    '(ename, empno, sal) values( :1, :2, :3)'
                 -- note: PL/SQL rule is table | col name cannot be
                 --       a bind argument in USING
                   -- also, binds are by position except in dynamic PL/SQL blocks
                 using :ename, :empno, :sal;
               end;
             };
      }

      private static void dynamicDelete(String which_col, String what_val)
              throws SQLException {

        System.out.println("dynamic delete of " + which_col +
                        " = " + what_val + " or " + which_col + " is null" );

        String s = "delete from emp where " + which_col  + " = " + what_val
                                    + " or " + which_col + " is null";
        #sql { begin
                 execute immediate :s;
               end;
         };
      }

      private static void staticUpdateReturning (int empno, double newSal)
              throws SQLException {

        System.out.println("static update-returning for empno " + empno);
       String ename;
       #sql { begin
                update emp set sal = :newSal
                where empno = :empno
                returning ename into :OUT ename;   -- :OUT is for SQLJ bind
              end;
            };
       System.out.println("Updated the salary of employee " + ename);

      }

      private static void dynamicUpdateReturning (int empno, double newSal )
              throws SQLException {

       System.out.println("dynamic update-returning for empno " + empno);
       String ename;

       #sql { begin
                execute immediate
                'update emp set sal = :1  where empno = :2 ' ||
                'returning ename into :3'
                using :newSal, :empno, OUT :OUT ename ;
                  -- note weird repeated OUT, one for PL/SQL bind, one for SQLJ
              end;
            };
       System.out.println("Updated the salary of employee " + ename);

      }

      private static void dynamicSelectOne(String which_table)
              throws SQLException  {

        System.out.println("dynamic 1-row query on table " + which_table);

        int countRows;

        #sql { begin
                  execute immediate
                  'select count(*) from ' || :which_table
                  into :OUT countRows;  -- :OUT is for SQLJ bind
               end;
             };

        System.out.println("Number of rows in table " + which_table +
                           " is " + countRows);
      }

      // a nested iterator class
      #sql public static iterator Employees ( String ename, double sal ) ;

      private static void dynamicSelectMany(String what_cond)
              throws SQLException {

        System.out.println("dynamic multi-row query on table emp");

        Employees empIter;

        // table/column names cannot be bind args in dynamic PL/SQL, so
        // build up query as Java string

        String query = "select ename, sal from emp " +
                     (((what_cond == null) || (what_cond.equals(""))) ? "" :
                     (" where " + what_cond)) +
                      "order by ename";

        #sql {
             begin
                open :OUT empIter for -- opening ref cursor with dynamic query
               :query;
                -- can have USING clause here if needed
             end;
        };

         while (empIter.next()) {
            System.out.println("Employee " + empIter.ename() +
                               " has salary " + empIter.sal() );
         }
         empIter.close();

      };

    }

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index