Number.sql
==========

drop table NumberTab;
drop type NumberType force;

CREATE TYPE NumberType AS OBJECT (
   num INTEGER,
   denom INTEGER,
   MAP MEMBER FUNCTION toReal RETURN REAL,
   MEMBER PROCEDURE normalize,
   STATIC FUNCTION gcd(x INTEGER,
                       y INTEGER) RETURN INTEGER,
   MEMBER FUNCTION plus ( x NumberType) RETURN NumberType
);
/
CREATE TYPE BODY NumberType AS

  MAP MEMBER FUNCTION toReal RETURN REAL IS
  -- convert rational number to real number
  BEGIN
    RETURN num / denom;
  END toReal;

  MEMBER PROCEDURE normalize IS
   g BINARY_INTEGER;
  BEGIN
   g := NumberType.gcd(num, denom);
   num := num / g;
   denom := denom / g;
  END normalize;

  STATIC FUNCTION gcd(x INTEGER,
                      y INTEGER) RETURN INTEGER IS
  -- find greatest common divisor of x and y
  ans BINARY_INTEGER;
  BEGIN
  IF x < y THEN
     ans := NumberType.gcd(y, x);
  ELSIF (x MOD y = 0) THEN
     ans := y;
  ELSE
     ans := NumberType.gcd(y, x MOD y);
  END IF;
  RETURN ans;
  END gcd;
  MEMBER FUNCTION plus (x NumberType) RETURN NumberType IS
  BEGIN
   return NumberType(num * x.denom + x.num * denom,
                   denom * x.denom);
  END plus;
END;
/

CREATE TABLE NumberTab of NumberType;
INSERT INTO NumberTab VALUES(2,3);

COMMIT;
EXIT;
/

$ sqlplus scott/tiger @Number

Number.in
=========
SQL NumberType AS NumberType

Number.properties
=================
jpub.methods=all
jpub.numbertypes=jdbc
jpub.lobtypes=jdbc
jpub.builtintypes=jdbc
jpub.usertypes=jdbc
jpub.compile=notranslate  ←  do not translate/compile the
                          sqlj file

$ jpub -u scott/tiger -input=Number.in
-props=Number.properties
SCOTT.NUMBERTYPE

   public class NumberType implements SQLData
   {
     public static final String _SQL_NAME = "SCOTT.NUMBERTYPE";
     public static final int _SQL_TYPECODE = OracleTypes.STRUCT;
     ...

     public int gcd (int X,int Y) throws java.sql.SQLException
     ...
     #sql [getConnectionContext()] __jPt_result =
                        { VALUES(SCOTT.NUMBERTYPE.GCD(:X,:Y)) };
      ...

      public NumberType plus ( NumberType X) throws
   java.sql.SQLException

      #sql [getConnectionContext()]
           { BEGIN :OUT __jPt_result := :__jPt_temp.PLUS(:X);
   END;};

      public NumberType normalize ()

       NumberType __jPt_temp = this;
       #sql [getConnectionContext()]
          { BEGIN :INOUT __jPt_temp.NORMALIZE(); END;};
       return __jPt_temp;

      public float toreal ()
       #sql [getConnectionContext()]
          {BEGIN :OUT __jPt_result := :__jPt_temp.TOREAL();
   END;};

MuNumber.java
=============

import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;


public class MyNumber
{

  public static void main(String[] args) throws
java.sql.SQLException
  {
    int n = 5;
    int d = 10;
    NumberType r = new NumberType(5, 10);

    int g = r.gcd(n, d);
    System.out.println("gcd: " + g);

    float f = r.toReal();
    System.out.println("real value: " + f);

    NumberType s = r.plus(r);
    System.out.println("sum: " + s);

    s.normalize();
    System.out.println("sum: " + s);
  }
}

XREFADTTab.sql
==============
drop table REFtab;
drop table ADTtab;
drop type ADTtyp;

create type ADTtyp as OBJECT (a1 number, a2 varchar2(20), a3
date)
/
create table ADTtab of ADTtyp
/

create table REFtab (id number, refcol REF ADTtyp)
/

insert into ADTtab values (ADTtyp(1, 'One', '01-JAN-2001'));
insert into ADTtab values (ADTtyp(2, 'Two', '02-JAN-2002'));
insert into ADTtab values (ADTtyp(3, 'Three', '03-JAN-
2003'));
insert into ADTtab values (ADTtyp(4, 'Four', '04-JAN-2004'));
insert into ADTtab values (ADTtyp(5, 'Five', '05-JAN-2005'));

insert into REFtab select 1, REF(R2) from ADTtab R2 where
R2.a1 = 1;
insert into REFtab select 2, REF(R2) from ADTtab R2 where
R2.a1 = 2;
insert into REFtab values (3, NULL);
insert into REFtab select 4, REF(R2) from ADTtab R2 where
R2.a1 = 4;
insert into REFtab select 5, REF(R2) from ADTtab R2 where
R2.a1 = 5;
commit;

$ sqlplus scott/tiger @XREFADTTab

$ jpub -user scott/tiger -sql=ADTtyp:Adttyp

SCOTT.ADTTYP

$ ls Adttyp*

Adttyp.class Adttyp.java  AdttypRef.class  AdttypRef.java

Refcur.sql
==========

set serveroutput on;
create or replace package refcur as
   type curstype is ref cursor return emp%rowtype;
   FUNCTION getcurfunc RETURN curstype;
   PROCEDURE getcurproc(a curstype);
end;
/
show errors

create or replace package BODY refcur as
   FUNCTION getcurfunc RETURN curstype IS
   curs curstype;
   BEGIN
      open curs for select * from emp where ename='SCOTT';
      return curs;
   END;

   PROCEDURE getcurproc(a curstype) IS
     name emp%rowtype;

   BEGIN
      LOOP
        FETCH a INTO  name ;
        EXIT WHEN a%NOTFOUND;
        dbms_output.put_line(name.ENAME);
      END LOOP;
    close a;
   END;

END;
/
show errors
exit;


$ sqlplus scott/tiger @Refcur

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 22
19:00:01 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
Production
With the Partitioning and Data Mining options


Package created.

No errors.

Package body created.

No errors.
Disconnected from Oracle Database 10g Enterprise Edition
Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
$

$ sqlplus scott/tiger
SQL> set serveroutput on

SQL> declare
  2  curs refcur.curstype;
  3  begin
  4  curs := refcur.getcurfunc();
  5  refcur.getcurproc(curs);
  6  end;
  7  /
SCOTT

PL/SQL procedure successfully completed.

$ jpub –user scott/tiger –compile=notranslate
–sql=REFCUR:Refcur

SCOTT.REFCUR
GetcurfuncRow

public java.sql.ResultSet getcurfunc () {...}

#sql [getConnectionContext()]
             __jPt_result_rs = {
VALUES(SCOTT.REFCUR.GETCURFUNC()) };

jpub -sql=REFCUR:RefcurBase:MyRefcur ...

public String[] readRefCursorArray(String arg1, Integer arg2)
{    java.sql.ResultSet rs = getcurfunc(arg1,arg2);
     //... create a String[] from rs and return it ... }

NTab.sql (not shown entirely)
========

DROP TABLE projects
/
drop table student
/
DROP TYPE moduletbl_t
/
DROP TYPE module_t
/
DROP TYPE student_t
/
CREATE TYPE student_t AS OBJECT (
  empno   NUMBER(4),
  ename   VARCHAR2(20),
  job     VARCHAR2(12),
  mgr     NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  deptno   NUMBER(2))
/
show errors
CREATE TYPE module_t  AS OBJECT (
  module_id  NUMBER(4),
  module_name VARCHAR2(20),
  module_owner REF student_t,
  module_start_date DATE,
  module_duration NUMBER )
/
show errors
create TYPE moduletbl_t AS TABLE OF module_t;
/
show errors
CREATE TABLE projects (
  id NUMBER(4),
  name VARCHAR(30),
  owner REF student_t,
  start_date DATE,
  duration NUMBER(3),
  modules  moduletbl_t  ) NESTED TABLE modules STORE AS modules_tab ;

CREATE TABLE student  OF student_t ;

INSERT INTO STUDENT VALUES(
STUDENT_T(7369,'BOB SMET','ANALYST',7902,'17-DEC-95',800,20)) ;
...
INSERT INTO student VALUES (
student_t(7934,'SUE MILLER','SR ANALYST',7782,'23-JAN-02',1300,10));

INSERT INTO projects VALUES ( 111, 'P111', null, '10-JAN-02',  300,
      moduletbl_t( module_t ( 1111 , 'Physics ', null, '01-JAN-99',
100),
module_t ( 1112, 'Advertizing', null, '05-FEB-04',20) ,
                    ...
module_t ( 1115, 'Politics', null,'12-MAY-03',34) ) ) ;

update projects set owner=(select ref(p) from student p where p.empno
= 7839) where id=111 ;
update the(select modules from projects a where a.id = 111)  set
module_owner =
 ( select ref(p) from student p where p.empno = 7844) where module_id
= 1111 ;
update the(select modules from projects a where a.id = 111)  set
module_owner =
 ( select ref(p) from student p where p.empno = 7844) where module_id
= 1111 ;
...

INSERT INTO projects VALUES ( 444, 'P444', null, '15-FEB-03', 555,
       moduletbl_t ( module_t ( 4441, 'Manufacturing', null, '01-MAR-
02', 120),
...
module_t ( 4447, 'Budgets', null, '10-MAR-01',45))) ;

update projects set owner=(select ref(p) from student p where p.empno
= 7698) where id=444 ;

update the ( select modules from projects where id = 444 ) set
module_owner =
( select ref(p) from student p where p.empno = 7369) where module_id =
4441 ;
...
commit;

exit;

$ jpub -user scott/tiger -compile=false
      -sql=MODULE_T:Module,MODULETBL_T:Modules,STUDENT_T:Student

SCOTT.MODULE_T
SCOTT.MODULETBL_T
SCOTT.STUDENT_T

Module.java
      public class Module implements ORAData, ORADataFactory
      {
        public static final String _SQL_NAME = "SCOTT.MODULE_T";
        public static final int _SQL_TYPECODE = OracleTypes.STRUCT;
        ...
        // getters and setters

Modules.java

public class Modules implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.MODULETBL_T";
  public static final int _SQL_TYPECODE = OracleTypes.ARRAY;
  ...
  public Module[] getArray(long index, int count) throws SQLException
  ...
  public void setArray(Module[] a) throws SQLException
  ...
  public Module getElement(long index) throws SQLException
  ...
  public void setElement(Module a, long index) throws SQLException

ModuleRef.java
      public class ModuleRef implements ORAData, ORADataFactory
      {
        public static final String _SQL_BASETYPE = "SCOTT.MODULE_T";
        public static final int _SQL_TYPECODE = OracleTypes.REF;
        ...


Student.java
       public class Student implements ORAData, ORADataFactory
       {
         public static final String _SQL_NAME = "SCOTT.STUDENT_T";
         public static final int _SQL_TYPECODE = OracleTypes.STRUCT;
         ...
         // getters and setters
 StudentRef.java
       public class StudentRef implements ORAData, ORADataFactory
       {
         public static final String _SQL_BASETYPE = "SCOTT.STUDENT_T";
          public static final int _SQL_TYPECODE = OracleTypes.REF;
       ...

public static String _SQL_NAME = "MYOPAQUE";
public static int _SQL_TYPECODE = OracleTypes.OPAQUE;
public static ORADataFactory getORADataFactory() { ... }

MYOPAQUE.sql
============

CREATE LIBRARY opqlib TRUSTED AS STATIC
/
CREATE OR REPLACE TYPE MYOPAQUE as OPAQUE FIXED (4)
USING library opqlib
(
        STATIC FUNCTION CONSTRUCT(str IN VARCHAR2) return MYOPAQUE,
        MEMBER FUNCTION VALUE return BINARY_INTEGER,
        MAP MEMBER FUNCTION MAPFUN return BINARY_INTEGER
)
/

CREATE OR REPLACE TYPE BODY MYOPAQUE as
STATIC FUNCTION CONSTRUCT(str IN VARCHAR2) return MYOPAQUE
...
MEMBER FUNCTION VALUE return BINARY_INTEGER
...
MAP MEMBER FUNCTION MAPFUN return BINARY_INTEGER
...
end;
/
$ jpub  -user=sys/<syspassword>   -sql=MYOPAQUE:MyOpaque
        -methods=true 

-addtypemap=MYOPAQUE:MyOpaque

xmltype.sql
===========
drop type xmlobj;

create type xmlobj as object (xmldoc sys.xmltype);
/
show error

exit

Step#1: create the sql type
$ sqlplus scott/tiger

...
SQL> set echo on
SQL> @xmltype
SQL> drop type xmlobj;

Type dropped.
SQL>
SQL> create type xmlobj as object (xmldoc sys.xmltype);
  2  /
Type created.

SQL> show error
No errors.
SQL>
SQL> exit

$ jpub –u scott/tiger –compile=false –sql=xmlobj:xmlobj_t

SCOTT.XMLOBJ

xmlobj_t.java xmlobj_tRef.java

xmlobj_t.java
=============
...
public class xmlobj_t implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.XMLOBJ";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;
  ...
  public static ORADataFactory getORADataFactory()
  ...

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  ...
  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws
SQLException
  ...
  protected ORAData create(xmlobj_t o, Datum d, int sqlType)
throws SQLException
  ...
  /* accessor methods */
  public oracle.xdb.XMLType getXmldoc() throws SQLException
  ...
  public void setXmldoc(oracle.xdb.XMLType xmldoc) throws
SQLException
  }

$ jpub -u scott/tiger -style=webservices10
                      -compile=false -sql=XMLOBJ:xmlobj_t

xmlobj_t.java  xmlobj_tBase.java  xmlobj_tUser.java
xmlobj_tUserRef.java

indexbytab.sql
==============
create or replace package pkgidx is
    type plsidxtyp is table of varchar2(20) index by
binary_integer;
    procedure procidx (p1 in out plsidxtyp);
    end;
/

$sqlplus scott/tiger @indexbytab

$ jpub –u scott/tiger –s PKGIDX:Pkgidx –plsqlpackage=wrap1
       –plsqlfile=wrap1.sql,drop1.sql –compile=notranslate
SCOTT.PKGIDX
SCOTT."PKGIDX.PLSIDXTYP"
J2T-138, NOTE: Wrote PL/SQL package wrap1 to file wrap1.sql.
Wrote the dropping script to file drop1.sql
$ 

PkgidxPlsidxtyp.java
====================
public class PkgidxPlsidxtyp implements ORAData,
ORADataFactory
{
  public static final String _SQL_NAME =
"SCOTT.PKGIDX_PLSIDXTYP";
  public static final int _SQL_TYPECODE = OracleTypes.ARRAY;
...

Pkgidx.sqlj
===========
public class Pkgidx {

/* connection management */

/* constructors */

//

 public void procidx (PkgidxPlsidxtyp P1[])
  throws java.sql.SQLException
  {
   try {
       #sql [getConnectionContext()]

       { CALL wrap1.PKGIDX$PROCIDX(:INOUT (P1[0])) };
   } catch(java.sql.SQLException _err) {...}

 ...

wrap1.sql
=========
-- Declare the SQL type for the PL/SQL type PKGIDX.PLSIDXTYP
CREATE OR REPLACE TYPE PKGIDX_PLSIDXTYP AS TABLE OF
VARCHAR2(20);
/
show errors
-- Declare package containing conversion functions between
SQL and PL/SQL types
CREATE OR REPLACE PACKAGE wrap1 AS
        -- Declare the conversion functions the PL/SQL type
PKGIDX.PLSIDXTYP
        FUNCTION PL2PKGIDX_PLSIDXTYP (aPlsqlItem
PKGIDX.PLSIDXTYP)
                 RETURN PKGIDX_PLSIDXTYP;
        FUNCTION PKGIDX_PLSIDXTYP2PL(aSqlItem
PKGIDX_PLSIDXTYP)
                 RETURN PKGIDX.PLSIDXTYP;
   PROCEDURE PKGIDX$PROCIDX (P1 IN OUT PKGIDX_PLSIDXTYP);
END wrap1;
/
show errors
CREATE OR REPLACE PACKAGE BODY wrap1 IS
        FUNCTION PL2PKGIDX_PLSIDXTYP(aPlsqlItem
PKGIDX.PLSIDXTYP)
        RETURN PKGIDX_PLSIDXTYP IS
        aSqlItem PKGIDX_PLSIDXTYP;
        BEGIN
                -- initialize the table
                aSqlItem := PKGIDX_PLSIDXTYP();
                aSqlItem.EXTEND(aPlsqlItem.COUNT);
              FOR I IN aPlsqlItem.FIRST..aPlsqlItem.LAST LOOP
               aSqlItem(I + 1 - aPlsqlItem.FIRST) :=
                    aPlsqlItem(I);
                END LOOP;
                RETURN aSqlItem;
        END PL2PKGIDX_PLSIDXTYP;
        FUNCTION PKGIDX_PLSIDXTYP2PL(aSqlItem
PKGIDX_PLSIDXTYP)
        RETURN PKGIDX.PLSIDXTYP IS
        aPlsqlItem PKGIDX.PLSIDXTYP;
        BEGIN
                FOR I IN 1..aSqlItem.COUNT LOOP
                        aPlsqlItem(I) := aSqlItem(I);
                END LOOP;
               RETURN aPlsqlItem;
        END PKGIDX_PLSIDXTYP2PL;

   PROCEDURE PKGIDX$PROCIDX (P1 IN OUT PKGIDX_PLSIDXTYP) IS
      P1_ SCOTT.PKGIDX.PLSIDXTYP;
   BEGIN
      P1_ := wrap1.PKGIDX_PLSIDXTYP2PL(P1);
      SCOTT.PKGIDX.PROCIDX(P1_);
      P1 := wrap1.PL2PKGIDX_PLSIDXTYP(P1_);
   END PKGIDX$PROCIDX;

END wrap1;
/
show errors
exit

jpub.addtypemap=
SCOTT.PKGIDX.PLSIDXTYP:PkgidxPlsidxtyp:PKGIDX_PLSIDXTYP:
WRAP1.PKGIDX_PLSIDXTYP2PL:WRAP1.PL2PKGIDX_PLSIDXTYP

create or replace package idx_tab_pkg as
type idx_tab1 is table of varchar2(111) index by binary_integer;
type idx_tab2 is table of number index by binary_integer;
type varray_tab is varray(100) of varchar2(20);
type nested_tab is table of varchar2(20);
type rec1 is record (a idx_tab1, b idx_tab2);
function echo_idx_tab1(a idx_tab1) return idx_tab1;
function echo_idx_tab2(a idx_tab2) return idx_tab2;
function echo_varray_tab(a varray_tab) return varray_tab;
function echo_nested_tab(a nested_tab) return nested_tab;
function echo_rec1(a rec1) return rec1;

procedure test_tab1(xxx idx_tab1);
procedure test_tab2(yyy idx_tab2);
end;
/
show errors

create or replace package body idx_tab_pkg is
function echo_idx_tab1(a idx_tab1) return idx_tab1 is
begin return a; end;
function echo_idx_tab2(a idx_tab2) return idx_tab2 is
begin return a; end;
function echo_varray_tab(a varray_tab) return varray_tab is
begin return a; end;
function echo_nested_tab(a nested_tab) return nested_tab is
begin return a; end;
function echo_rec1(a rec1) return rec1 is
begin return a; end;
procedure test_tab1(xxx idx_tab1) is
begin null; end;

procedure test_tab2(yyy idx_tab2) is
begin null; end;
end;
/
show errors

$jpub -user=scott/tiger -
sql=idx_tab_pkg:IndexbyTablePackage#IndexbyTableIntf -
plsqlindextable=32
SCOTT.IDX_TAB_PKG
SCOTT."IDX_TAB_PKG.VARRAY_TAB"
SCOTT."IDX_TAB_PKG.NESTED_TAB"
SCOTT."IDX_TAB_PKG.REC1"
SCOTT."IDX_TAB_PKG.IDX_TAB1"
SCOTT."IDX_TAB_PKG.IDX_TAB2"
J2T-138, NOTE: Wrote PL/SQL package JPUB_PLSQL_WRAPPER to
file plsql_wrapper.sql. Wrote the dropping script to file
plsql_dropper.sql

$ls *.java
IndexbyTableIntf.java IndexbytableintfNestedTab.java
IndexbytableintfRec1Ref.java Indexbytableintfrec1IdxTab1.java
IndexbyTablePackage.java IndexbytableintfRec1.java
IndexbytableintfVarrayTab.java Indexbytableintfrec1IdxTab2.java
$
IndexbyTableIntf.java
=====================
import java.sql.SQLException;
import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ConnectionContext;
import java.sql.Connection;

public interface IndexbyTableIntf{

  public IndexbytableintfVarrayTab echoVarrayTab (

    IndexbytableintfVarrayTab A) throws java.sql.SQLException;

  public void testTab2 (
    java.math.BigDecimal[] YYY) throws java.sql.SQLException;

  public void testTab1 (
    String[] XXX) throws java.sql.SQLException;

  public java.math.BigDecimal[] echoIdxTab2 (
    java.math.BigDecimal[] A) throws java.sql.SQLException;

  public IndexbytableintfNestedTab echoNestedTab (
    IndexbytableintfNestedTab A) throws java.sql.SQLException;

  public IndexbytableintfRec1 echoRec1 (
    IndexbytableintfRec1 A) throws java.sql.SQLException;

  public String[] echoIdxTab1 (
    String[] A) throws java.sql.SQLException;
}

$ jpub -user=SCOTT/TIGER -sql=AQNAME:javaName