Marc On Oracle

OAUG Winner!

2010-08-17T22:14:00.003+10:00

So, I took the quiz (see the 20th Anniversary Trivia Challenge) put up by the Oracle Application User Group (OAUG) a couple of weeks ago, and suddenly I notice that I am the winner of a Nook eReader. That's good news, right?

Erm, it is a shame that the Barnes and Noble device does not allow you to purchase eBooks from an Australian IP address. That's bad news, right?

Well, they sent me an email saying that because of the difficulty in getting a Nook to Australia, they would instead give me a shopping gift card. Well, that is definitely good news, right?

Seems like my wife has already claimed and spent said gift voucher...

SQL Developer - Search Source Code - now including Views!

2010-03-16T10:56:00.002+11:00

I am attempting to promote Oracle's SQL Developer as a standard PL/SQL development tool, in order to reduce current spending on licences for Quest TOAD and SQL Navigator, within our organisation.

Most of my colleagues have managed to move across smoothly, with a few minor issues that will probably see us holding on to a number of TOAD licences for the heavy-users, while the majority should be able to work happily and productively with SQL Developer.

On of the main points of contention in the migration is the in-place editing of SQL results, but Oracle have acknowledged that request and should be fitting it into some future release.

A fellow developer wanted to know if the Search Source Code report in SQL Developer included Trigger and View source. So I did a little bit of checking around and saw that Trigger code *was* included, but View code was not. So, off I went to see if anyone had created and published an extension that did what we wanted. A quick look at SQL Developer Exchange brought back nothing, so I set about to create my own.

The tricky thing about Views (see all_views or user_views) is that the source is held as a LONG datatype, which doesn't play well with others. In this instance, I wanted to UNION the results from the standard Search Source Code output (which returns source lines as a VARCHAR2) with the View source, as well as finding the location of the Search criteria within the View source. Clearly a LONG and a VARCHAR2 will not UNION, nor can you SUBSTR or INSTR a LONG, so I had to implement a conversion of some sort.

According to Tom Kyte you can easily write a wrapper PL/SQL function to do that for you, but I am lazy (and didn't want to be placing conversion functions in multiple databases) so I began looking for a pre-existing routine that would do that for me. DBMS_METADATA to the rescue!

Using DBMS_METADATA.GET_DDL gave me the full creation source for the View object, which was just great. But it returned a CLOB, which was not so great. Luckily DBMS_LOB gives us a SUBSTR method which would suit my needs, for display purposes. So, using these DBMS built-ins together, I now had the means to create a user-defined script for including View source within the Search Source Code report.

And here is the final result, which I have placed up on SQL Developer Exchange: (Right-click and Save As)

NVL, NVL2 and CASE

2009-07-09T11:08:00.004+10:00

This blog is simply a note to myself as I attempted to use the NVL2 function in PL/SQL recently. Enjoy.

Oracle's NVL is a great little function that you can use in SQL or PL/SQL to determine if an item is NULL.

SQL eg.

SELECT e.ename, NVL(m.ename, 'I AM DA BOSS!') manager
 FROM emp e, emp m
WHERE e.mgr = m.empno (+)

PL/SQL eg

DECLARE
 l_empty VARCHAR2(10);
BEGIN
 dbms_output.put_line(NVL(l_empty, 'Forgot to init l_empty!'));
END;

And now (well, for a while, actually) Oracle have provided as with a new NVL2 function that allows us to translate a NULL value to another value completely.

SQL eg.

SELECT e.ename, NVL2(e.mgr, 'i am a peon', 'I AM DA BOSS!') who_am_i
 FROM emp e;

However, the NVL2 function is not available in PL/SQL! Bah, how annoying! Try it and you will see:

DECLARE
 l_empty VARCHAR2(10);
BEGIN
 dbms_output.put_line(NVL2(l_empty, 'Ahh, all safe..','Forgot to init l_empty!'));
END;

... gives an error of:

ERROR at line 4:
ORA-06550: line 4, column 25:
PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored

So, what can we do? Why, we can use the CASE statement in all instances!

SQL eg.

SELECT e.ename, CASE
                 WHEN m.ename IS NULL THEN 'I AM DA BOSS!'
                 WHEN m.ename IS NOT NULL THEN 'i am a peon'
               END manager
 FROM emp e, emp m
WHERE e.mgr = m.empno (+)

PL/SQL eg.

DECLARE
 l_empty VARCHAR2(10);
BEGIN
 dbms_output.put_line(CASE
                       WHEN l_empty IS NOT NULL THEN 'Ahh, all safe..'
                       WHEN l_empty IS NULL THEN 'Forgot to init l_empty!'
                     END);
END;

There is a lot more to type, but it may just be a little more readable.

SQLUnit - PL/SQL Unit Testing using a JUnit Framework.

2009-07-07T16:27:00.003+10:00

For those who come from a purely SQL and PL/SQL background and enter the world of Java programming, there are many sights and sounds to behold, and wonders to experience. Entering the world of Java programming may be the first time you are exposed to the concept of programming frameworks. Frameworks exist as a base supporting structure where you can slot elements of development while being comfortable in the fact that anyone who comes along in the future can pick up your code and 'know' where everything is, assuming they are familiar with the particular framework you used.

Many frameworks eventually become open-sourced as developers collaborate on the definition of the framework.

Code Frameworks examples

Spring
ADF

Build/Deployment Frameworks examples

Ant
Maven

Testing Frameworks example

Java - JUnit
PL/SQL - utPLSQL, PLUTO, SQLUnit

utPLSQL and PLUTO both require extra database objects to be installed/created. These are then used to call your PL/SQL to be tested.

SQLUnit can be run as a Java application, or incorporated into an ant script as part of an automated build.
Since it uses Java, the database connection uses JDBC. At the outset, this warns that there may be limitations on testing program units that require or output Oracle BOOLEAN variables or PL/SQL type structures. All other types should be usable.
(The following examples use an 11g database and the ojdbc5_g.jar jdbc database driver.)

Basic SELECT


<sqlunit>
<connection>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl</url>
<user>scott</user>
<password>tiger</password>
</connection>
<test name="#1 Simple SELECT - SELECT count(*) FROM emp" failure-message="Error with Simple SELECT">
<sql>
  <stmt>select count(*) from emp</stmt>
</sql>
<result>
  <resultset id="1">
    <row id="1">
      <col id="1" name="c1" type="NUMERIC">14</col>
    </row>
  </resultset>
</result>
</test>
</sqlunit>

Basic procedure call

Given procedure exists:

CREATE OR REPLACE PROCEDURE get_emp_name(p_empno IN NUMBER,
                                       p_empname OUT VARCHAR2)
AS
CURSOR emp_csr
IS
  SELECT ename
     FROM emp
   WHERE empno = p_empno;
BEGIN
OPEN emp_csr;
FETCH emp_csr INTO p_empname;
CLOSE emp_csr;
END get_emp_name;

Test case would be:


<sqlunit>
<connection>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl</url>
<user>scott</user>
<password>tiger</password>
</connection>
<test name="#2 Simple PROC call" failure-message="Error with Simple PROC call">
<call>
  <stmt>{call get_emp_name(?,?)}</stmt>
  <param id="1" name="p_empno" type="INTEGER" inout="in">7934</param>
  <param id="2" name="p_empname" type="VARCHAR" inout="out"></param>
</call>
<result>
  <outparam id="2" type="VARCHAR">MILLER</outparam>
</result>
</test>
</sqlunit>

Note that INTEGER is used for translating PL/SQL NUMBER types.

Basic function call
Given function exists:

CREATE OR REPLACE FUNCTION get_dept_name(p_deptno IN NUMBER)
RETURN VARCHAR2
AS
CURSOR dept_csr
IS
  SELECT dname
    FROM dept
   WHERE deptno = p_deptno;
l_dname dept.dname%TYPE;
BEGIN
OPEN dept_csr;
FETCH dept_csr INTO l_dname;
CLOSE dept_csr;
RETURN l_dname;
END get_dept_name;

Test case would be:

<sqlunit>
<connection>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl</url>
<user>scott</user>
<password>tiger</password>
</connection>
<test name="#3 Simple FUNCTION call" failure-message="Error with Simple FUNCTION call">
<call>
  <stmt>{? = call get_dept_name(?)}</stmt>
  <param id="1" name="p_out" type="VARCHAR" inout="out"></param>
  <param id="2" name="p_deptno" type="INTEGER" inout="in">10</param>
</call>
<result>
  <outparam id="1" name="p_out" type="VARCHAR">ACCOUNTING</outparam>
</result>
</test>
</sqlunit>

Note that the return value is always the first parameter when calling a function.

Basic Function returning Cursor
Given Function exists:

CREATE OR REPLACE FUNCTION get_all_emps
RETURN SYS_REFCURSOR
IS
my_csr SYS_REFCURSOR;
BEGIN
OPEN my_csr FOR
  SELECT *
     FROM emp
  ORDER BY empno;
RETURN my_csr;
END get_all_emps;

Test case would be:

<test name="#4 FUNCTION call returning CURSOR" failure-message="Error with FUNCTION call returning CURSOR">
<call>
  <stmt>{? = call get_all_emps}</stmt>
  <param id="1" name="p_out" type="CURSOR" inout="out"></param>
</call>
<result>
  <outparam id="1" name="p_out" type="CURSOR">
    <resultset id="1">
      <row id="1">
        <col id="1" name="EMPNO" type="NUMERIC">7369</col>
        <col id="2" name="ENAME" type="VARCHAR">SMITH</col>
        <col id="3" name="JOB" type="VARCHAR">CLERK</col>
        <col id="4" name="MGR" type="NUMERIC">7902</col>
        <col id="5" name="HIREDATE" type="TIMESTAMP">1980-12-17 00:00:00.000</col>
        <col id="6" name="SAL" type="NUMERIC">800</col>
        <col id="7" name="COMM" type="NUMERIC">NULL</col>
        <col id="8" name="DEPTNO" type="NUMERIC">20</col>
      </row>
      <row id="2">
        <col id="1" name="EMPNO" type="NUMERIC">7499</col>
        <col id="2" name="ENAME" type="VARCHAR">ALLEN</col>
        <col id="3" name="JOB" type="VARCHAR">SALESMAN</col>
        <col id="4" name="MGR" type="NUMERIC">7698</col>
        <col id="5" name="HIREDATE" type="TIMESTAMP">1981-02-20 00:00:00.000</col>
        <col id="6" name="SAL" type="NUMERIC">1600</col>
        <col id="7" name="COMM" type="NUMERIC">300</col>
        <col id="8" name="DEPTNO" type="NUMERIC">30</col>
      </row>
...
...
      <row id="14">
        <col id="1" name="EMPNO" type="NUMERIC">7934</col>
        <col id="2" name="ENAME" type="VARCHAR">MILLER</col>
        <col id="3" name="JOB" type="VARCHAR">CLERK</col>
        <col id="4" name="MGR" type="NUMERIC">7782</col>
        <col id="5" name="HIREDATE" type="TIMESTAMP">1982-01-23 00:00:00.000</col>
        <col id="6" name="SAL" type="NUMERIC">1300</col>
        <col id="7" name="COMM" type="NUMERIC">NULL</col>
        <col id="8" name="DEPTNO" type="NUMERIC">10</col>
      </row>
    </resultset>
  </outparam>
</result>
</test>

Function returning Simple Oracle UDT
Given Type exists:

CREATE OR REPLACE TYPE empRec AS OBJECT
(EMPNO  NUMBER(4),
ENAME  VARCHAR2(10),
JOB      VARCHAR2(9),
MGRNO NUMBER(4),
MGRNAME VARCHAR2(10),
SAL      NUMBER(7,2),
COMM   NUMBER(7,2),
DEPTNO NUMBER(2),
DNAME  VARCHAR2(14));

Given Function exists:

CREATE OR REPLACE FUNCTION get_emp_details(p_empNo IN NUMBER)
RETURN empRec
IS
CURSOR emp_csr
IS
  SELECT empRec(
               e.empno,
               e.ename,
               e.job,
               e.mgr,
               e2.ename,
               e.sal,
               e.comm,
               e.deptno,
               get_dept_name(e.deptno))
     FROM emp e, emp e2
   WHERE e.empno = p_empNo
       AND e.mgr = e2.empno (+);
l_emp empRec;
BEGIN
OPEN emp_csr;
FETCH emp_csr INTO l_emp;
CLOSE emp_csr;
RETURN l_emp;
END get_emp_details;

For this test case, we need to tell SQLUnit how to handle the new Oracle UDT, so we fire up JDeveloper (or JPublisher, I suppose). Create a connection to the database, using the schema that owns the UDT. Immense THANKS to the OracleNerd (www.oraclenerd.com) for pointing this out to me. I was stumbling around for a while, trying to figure out how to construct these Type Classes, and he came to the rescue with this easy method.

JDeveloper (I used 10.1.3.3) allows us to automatically generate Java classes for accessing our UDTs from Java. Perform this step for each of your UDTs, and package them together in a JAR. Place the JAR in the lib directory.

To reference the generated classes, the <connection> tag needs to be changed to include type-mapping tags to allow SQLUnit to determine which classes to use for which UDTs.
In this case, the test will look like this:


<sqlunit>
<connection>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>jdbc:oracle:thin:@localhost:1521:orcl</url>
<user>scott</user>
<password>tiger</password>
<typemap>
  <typedef typename="SCOTT.EMPREC"
    classname="net.sourceforge.sqlunit.types.Emprec" />
</typemap>
</connection>
<test name="#5 FUNCTION returning simple UDT" failure-message="Error with FUNCTION returning simple UDT">
<call>
  <stmt>{? = call get_emp_details(?)}</stmt>
  <param id="1" name="p_out" type="STRUCT" typename="SCOTT.EMPREC" inout="out"></param>
  <param id="2" name="p_empno" type="INTEGER" inout="in">7839</param>
</call>
<result>
  <outparam id="1" name="p_out" type="STRUCT">
    <struct>
      <field name="comm">NULL</field>
      <field name="deptno">10</field>
      <field name="dname">ACCOUNTING</field>
      <field name="empno">7839</field>
      <field name="ename">KING</field>
      <field name="job">PRESIDENT</field>
      <field name="mgrname">NULL</field>
      <field name="mgrno">NULL</field>
      <field name="sal">5000</field>
    </struct>
  </outparam>
</result>
</test>
</sqlunit>

Note that the struct (UDT) elements are order alphabetically. This is done to provide easier matching of the UDT data structure. Also note that the element names are in lower-case.

Function returning Table Oracle UDT
Given Type exists:
CREATE TYPE t_empRec AS TABLE OF empRec;

Given Function exists:

CREATE OR REPLACE FUNCTION get_dept_emp_details(p_deptno IN NUMBER)
RETURN t_empRec
IS
CURSOR emp_csr
IS
  SELECT empRec(
               e.empno,
               e.ename,
               e.job,
               e.mgr,
               e2.ename,
               e.sal,
               e.comm,
               e.deptno,
               get_dept_name(e.deptno))
     FROM emp e, emp e2
   WHERE e.mgr = e2.empno (+)
       AND e.deptno = p_deptno;
l_emp t_empRec;
BEGIN
OPEN emp_csr;
FETCH emp_csr BULK COLLECT INTO l_emp;
CLOSE emp_csr;
RETURN l_emp;
END get_dept_emp_details;

Generate the Java Class for the Table UDT, and insert it into the appropriate JAR.
Add a typemapping to the <connection> tag

    <typedef typename="SCOTT.T_EMPREC"
    classname="net.sourceforge.sqlunit.types.TEmprec" />

The test case will then be structured like this:

<test name="#6 FUNCTION returning table UDT" failure-message="Error with FUNCTION returning table UDT">
<call>
  <stmt>{? = call get_dept_emp_details}</stmt>
  <param id="1" name="p_out" type="STRUCT" typename="SCOTT.T_EMPREC" inout="out"></param>
  <param id="2" name="p_deptno" type="INTEGER" inout="in">10</param>
</call>
<result>
  <outparam id="1" name="p_out" type="STRUCT">
        <struct>
          <field name="comm">NULL</field>
          <field name="deptno">10</field>
          <field name="dname">ACCOUNTING</field>
          <field name="empno">7934</field>
          <field name="ename">MILLER</field>
          <field name="job">CLERK</field>
          <field name="mgrname">CLARK</field>
          <field name="mgrno">7782</field>
          <field name="sal">1300</field>
        </struct>
      </field>
      <field name="array">
        <struct>
          <field name="comm">NULL</field>
          <field name="deptno">10</field>
          <field name="dname">ACCOUNTING</field>
          <field name="empno">7782</field>
          <field name="ename">CLARK</field>
          <field name="job">MANAGER</field>
          <field name="mgrname">KING</field>
          <field name="mgrno">7839</field>
          <field name="sal">2450</field>
        </struct>
      </field>
      <field name="array">
        <struct>
          <field name="comm">NULL</field>
          <field name="deptno">10</field>
          <field name="dname">ACCOUNTING</field>
          <field name="empno">7839</field>
          <field name="ename">KING</field>
          <field name="job">PRESIDENT</field>
          <field name="mgrname">NULL</field>
          <field name="mgrno">NULL</field>
          <field name="sal">5000</field>
        </struct>
  </outparam>
</result>
</test>

Function returning Complex Oracle UDT
Given Type exists:

CREATE TYPE deptRec AS OBJECT
(DEPTNO    NUMBER(2),
DNAME     VARCHAR2(14),
EMPS      t_EmpRec,
LOC       VARCHAR2(13),
SALSUM    NUMBER(7,2));

Given Function exists:

CREATE OR REPLACE FUNCTION get_dept_details(p_deptno IN NUMBER)
RETURN deptRec
IS
CURSOR dept_csr
IS
  SELECT deptRec(
                 d.deptNo,
                 d.dname,
                 get_dept_emp_details(d.deptno),
                 d.loc,
                 sum(e.sal))
     FROM dept d, emp e
   WHERE d.deptno = p_deptno
       AND d.deptno = e.deptno (+)
    GROUP BY d.deptno, d.dname, d.loc;
l_dept deptRec;
BEGIN
OPEN dept_csr;
FETCH dept_csr INTO l_dept;
CLOSE dept_csr;
RETURN l_dept;
END get_dept_details;

Generate the Java Class for the dept UDT, and insert it into the appropriate JAR.
Add a typemapping to the <connection> tag

    <typedef typename="SCOTT.DEPTREC"
    classname="net.sourceforge.sqlunit.types.Deptrec" />

The test case will then be structured like this:

<test name="#7 FUNCTION returning complex UDT" failure-message="Error with FUNCTION returning complex UDT">
<call>
  <stmt>{? = call get_dept_details(?)}</stmt>
  <param id="1" name="p_out" type="STRUCT" typename="SCOTT.DEPTREC" inout="out"></param>
  <param id="2" name="p_deptno" type="INTEGER" inout="in">10</param>
</call>
<result>
  <outparam id="1" name="p_out" type="STRUCT">
    <struct>
      <field name="deptno">10</field>
      <field name="dname">ACCOUNTING</field>
      <field name="emps">
        <struct>
          <field name="array">
            <struct>
              <field name="comm">NULL</field>
              <field name="deptno">10</field>
              <field name="dname">ACCOUNTING</field>
              <field name="empno">7934</field>
              <field name="ename">MILLER</field>
              <field name="job">CLERK</field>
              <field name="mgrname">CLARK</field>
              <field name="mgrno">7782</field>
              <field name="sal">1300</field>
            </struct>
          </field>
          <field name="array">
            <struct>
              <field name="comm">NULL</field>
              <field name="deptno">10</field>
              <field name="dname">ACCOUNTING</field>
              <field name="empno">7782</field>
              <field name="ename">CLARK</field>
              <field name="job">MANAGER</field>
              <field name="mgrname">KING</field>
              <field name="mgrno">7839</field>
              <field name="sal">2450</field>
            </struct>
          </field>
          <field name="array">
            <struct>
              <field name="comm">NULL</field>
              <field name="deptno">10</field>
              <field name="dname">ACCOUNTING</field>
              <field name="empno">7839</field>
              <field name="ename">KING</field>
              <field name="job">PRESIDENT</field>
              <field name="mgrname">NULL</field>
              <field name="mgrno">NULL</field>
              <field name="sal">5000</field>
            </struct>
          </field>
        </struct>
      </field>
      <field name="loc">NEW YORK</field>
      <field name="salsum">8750</field>
    </struct>
  </outparam>
</result>
</test>

The Table of UDTs and the Complex UDT required a change of code in some of the Java classes in the SQLUnit project. Since it is open-source, the code can be downloaded and changed to suit your needs.
http://sourceforge.net/projects/sqlunit/

Change StructBean constructor method to:

public StructBean(final Object obj) {
  try {
      PropertyDescriptor[] props =
          PropertyUtils.getPropertyDescriptors(obj);
      Arrays.sort(props, new PropertyComparator());
      for (int i = 0; i < props.length; i++) {
          if (!"SQLTypeName".equals(props[i].getName())
                  && !"class".equals(props[i].getName())
                  && !"baseType".equals(props[i].getName())
                  && !"baseTypeName".equals(props[i].getName())
                  && !"descriptor".equals(props[i].getName())
                  && !"accessDirection".equals(props[i].getName())
                  && !"autoBuffering".equals(props[i].getName())
                  && !"autoIndexing".equals(props[i].getName())
                  && !"bytes".equals(props[i].getName())
                  && !"connection".equals(props[i].getName())) {
              String fieldName = props[i].getName();
              Method readMethod = props[i].getReadMethod();
              if (readMethod != null) {
                  Object fieldValue = readMethod.invoke(obj, new Object[0]);
                  FieldBean fBean;
                  if ("array".equals(props[i].getName())) {
                    Class<?> type = fieldValue.getClass();
                    if (type.isArray()) {
                     int length = Array.getLength(fieldValue);
                     for (int j = 0; j < length; j++) {
                     fBean = new FieldBean(fieldName, Array.get(fieldValue, j));
                     fields.add(fBean);
                     }
                    }
                  } else if (typeMap.containsValue(props[i].getPropertyType())) {
                      // Field is a nested struct
                    fBean = new FieldBean(fieldName, fieldValue);
                      fields.add(fBean);
                  } else {
                      // Field is a simple type
                      fBean = new FieldBean(fieldName,
                          (fieldValue == null) ? "NULL"
                              : fieldValue.toString());
                      fields.add(fBean);
                  }
              }
          }
      }
  } catch (Exception ex) {
      LOG.error("Could not read bean: " + obj.toString(), ex);
  }
}

Change OutParamHandler process method to:

...
...
} else if (elStruct != null) {
  if (!(op.getType().endsWith("STRUCT") || op.getType().endsWith("ARRAY"))) {
      throw new SQLUnitException(IErrorCodes.IS_A_STRUCT,
          new String[] {op.getId(), op.getType()});
  }
  IHandler structHandler =
      HandlerFactory.getInstance(elStruct.getName());
  StructBean sb =
      (StructBean) structHandler.process(elStruct);
  op.setValue(sb);
...
...

Change CallHandler setOutputParameters method to:

...
...
} else if (params[i].getType().endsWith("STRUCT") || params[i].getType().endsWith("ARRAY")) {
  // value is a user-defined type (UDT)
  StructBean sb = new StructBean(value);
  outParam.setValue(sb);
  if (outParamSymbol != null) {
      SymbolTable.setObject(outParamSymbol, sb);
  }
...
...

Overview
SQLUnit provides a neat way to include PL/SQL unit tests as part of an automated build process, since it can be incorporated into an ant script, or run in an isolated standalone mode. The fact that extended User Defined Types can be interrogated and matched with pre-defined XML output provides great flexibility.
For those who have attempted JDBC calls to an Oracle database - in particular to PL/SQL requiring parameters that are either BOOLEAN or of a PL/SQL type (defined in a package specification) - you will be aware of a limitation on accessing those data types. At the moment, there is no easy way to transmit or receive those types (not that I have figured out anyway).
Whilst PLUTO and utPLSQL can handle Booleans and PL/SQL types, I found the setup of test cases for those and complex UDT types a lot more cumbersome.

Allow Oracle Forms to receive messages from external sources

2008-11-05T09:06:00.003+11:00

Combining Oracle Forms with other technologies and allowing messages and data to communicate between them is one of the challenges facing the enterprise today. If you have a large investment in Oracle Forms and wish to continue using them in partnership with other applications, then you will most likely be looking at some sort of SOA-governed solution. However, there are other solutions that simply require database connectivity to allow transactions to flow between differing technologies. This outlines a few of these 'other' approaches. Note that these are not intended as recommendations, but simply provide alternatives that already exist with the current Oracle database and Forms toolset.

Approach #1: Custom Messaging Database Table

Create a table that will hold messages.
Create a package to populate and retrieve messages from that table.
In Forms, create a timer that periodically polls the table for new messages, or allow the user to dictate when to check for new messages (button-click, menu-option, etc).
On successful retrieval, act upon message as appropriate.

Approach #2: Advanced Queues
The Oracle AQ implementation gives great flexibility in the way messages can be sent and received.
See http://www.oracle-base.com/articles/9i/AdvancedQueuing9i.php for a quick run-down on how to use this approach.

Approach #3: Forms as a Socket Server
A variation on the chat server as documented in
http://sheikyerbouti.developpez.com/forms-pjc-bean/pages/chat.htm
and
http://sheikyerbouti.developpez.com/forms-pjc-bean/pages/socketserver.htm
this approach opens a specific port that allows direct communication between different users of a Forms application. This can be extended to allow other technologies to also send telnet-style messages to Forms.

Approach #4: DBMS_PIPE
Ok, here is the technique I spent the most time on.

Create a package that implements DBMS_PIPE for sending and receiving messages.
Note that package owner must have execute permissions granted for DBMS_PIPE.
Call send routine from any technology that can access the database: SQLPlus, Java, Forms, etc...
DBMS_PIPE code based on http://www.oracle-base.com/articles/misc/dbms_pipe.php
Create Java class that can run asynchronously to call receive method and fire event when message is received.
See http://sheikyerbouti.developpez.com/forms-pjc-bean/pages/asyncjob.htm
Class must implement Runnable.
Note that the Class requires its own database connection to check pipe.
Package the class into a Jar, and place a signed version on the application server for subsequent distribution with Forms application.
Add Java Bean onto your Form to implement the Java asynchronous method.
Add When-Custom-Item-Event to bean item on Form that captures and acts upon message received.
Either alert user to message, or use message contents to automatically navigate toa different screen.
formsweb.cfg must be setup to also distribute the packaged and signed Jar containing the asyncjob class, and classes12.jar (DB Connection for JInitiator 1.3 - or use ojdbc14.jar for Sun Java 1.4) in the archive_jini or archive setting

If there is enough demand, I will post up some sample code for approach #4, meanwhile I might try and see if I can get #2 (AQ) working...

Materialized Views, Logs and Context Indexes (Oracle 10.2.0.2).

2008-08-28T07:35:00.005+10:00

I have been playing around with Materialized Views (shouldn't that be 'Materialised Views'? I'm an Aussie!) over the past couple of days. I've been building a rather complex join of several base tables, to work towards allowing 2 major tables of data to have fast search capabilities.
So, the resulting MV construction syntax looks a little bit like this:

CREATE MATERIALIZED VIEW CLIENTNAMEADDR
...<storage>...
BUILD IMMEDIATE
USING INDEX
REFRESH FAST ON COMMIT
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS
SELECT cli.clientId,
cn.firstName ' ' cn.secondName ' ' cn.familyName ' '
addr.streetNum ' ' addr.streetName ' ' addr.streetType ' '
addr.suburb ' ' addr.state ' ' addr.postcode as clientFullText,
cli.rowid cliRowid, cn.rowid cnRowid, ca.rowid caRowid, addr addrRowid
FROM clients cli, client_Names cn, client_Addresses ca, addresses addr
WHERE cli.clientId = cn.clientId
AND cli.clientId = ca.clientId
AND ca.addressId = addr.addressId;

The theory is that this will allow me to create a Context index on the clientFullText field on the MV to allow end users to search on any combination of name and address within the database. In reality, for the query shown above, I don't really need to join the client table into the query, but for my purposes I have another AND clause and will be filtering some of them out.

There are a fair number of restrictions you need to keep in mind when creating a materialized view, most of which are especially important when your MV contains a complex query - and let's face it, why else would you be creating an MV if not to contain a query with a large number of joins and/or filters.

After searching around on the net I found a list of things to consider when creating a Materialized View:
* The defining query of the materialized view cannot contain any non-repeatable expressions (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on).
* The query cannot contain any references to RAW or LONG RAW datatypes or object REFs.
* If the defining query of the materialized view contains set operators (UNION, MINUS, and so on), rewrite will use them for full text match rewrite only.
* If the materialized view was registered as PREBUILT, the precision of the columns must match the precision of the corresponding SELECT expressions unless overridden by the WITH REDUCED PRECISION clause.
* If the materialized view contains the same table more than once, it is possible to do a general rewrite, provided the query has the same aliases for the duplicate tables as the materialized view.
* If a query has both local and remote tables, only local tables will be considered for potential rewrite.
* Neither the detail tables nor the materialized view can be owned by SYS.
* SELECT and GROUP BY lists, if present, must be the same in the query of the materialized view.
* Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.
* CONNECT BY clauses are not allowed.

If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks are:
* A materialized view log must be present for each detail table.
* The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition.
* If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause. However, if there are outer joins, the WHERE clause cannot have any selections. Further, if there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.
* If there are outer joins, unique constraints must exist on the join columns of the inner table. For example, if you are joining the fact table and a dimension table and the join is an outer join with the fact table being the outer table, there must exist unique constraints on the join columns of the dimension table.

If some of these restrictions are not met, you can create the materialized view as REFRESH FORCE to take advantage of fast refresh when it is possible. If one of the tables did not meet all of the criteria, but the other tables did, the materialized view would still be fast refreshable with respect to the other tables for which all the criteria are met.

In order to support REFRESH FAST on the materialized view (have the MV automatically refresh after each change to the base tables), an MV LOG table has to be created for each of the base tables of the MV and include the columns that are used in the MV, so:

CREATE MATERIALIZED VIEW LOG
ON client_Names
WITH ROWID, SEQUENCE(clientId,
firstName,
secondName,
familyName)
INCLUDING NEW VALUES;
/

CREATE MATERIALIZED VIEW LOG
ON clients
WITH ROWID, SEQUENCE(clientID);
/
CREATE MATERIALIZED VIEW LOG
ON client_Addresses
WITH ROWID, SEQUENCE(clientId,
addressId)
INCLUDING NEW VALUES;
/
CREATE MATERIALIZED VIEW LOG
ON addresses
WITH ROWID, SEQUENCE(addressId,
streetNum,
streetType,
streetName,
suburb,
state,
postcode)
INCLUDING NEW VALUES;
/

During the creation of the MV, I made use of a number of utilities made available as part of the Oracle rdbms. As SYS, I created the REWRITE_TABLE table ($ORACLE_HOME/rdbms/admin/utlxrw.sql) - as well as a public synonym to the same - and granted permissions to public. As SYS, I also ensured the DBMS_ADVISOR package was made made available to particular users as required. As a standard user who was creating the MV and LOG tables, I created the MV_CAPABILITIES_TABLE table ($ORACLE_HOME/rdbms/admin/utlxmv.sql). This is only really needed during the query tuning phase.

Ok, on to the Context index. Context Indexes are an excellent way to provide fast text searches on large data tables. Think of them as a LIKE operator on steriods. You can search a VARCHAR2 column for portions of text and it can also be configured to be score-based, where your search criteria can be allowed to be 'close to' the actual data.
First off, make sure your friendly DBA grants you EXECUTE permissions to the CTXSYS.CTX_DDL package. This may be needed if you want your index to sit in a non-default tablespace, or if you want to apply a 'stop-list' to the index (ie search words to ignore).
To create a stop-list, syntax follows:
BEGIN
CTX_DDL.CREATE_STOPLIST('CLI_NAMES_IGNORE', 'BASIC_STOPLIST');
CTX_DDL.ADD_STOPWORD('CLI_NAMES_IGNORE', 'PTY');
CTX_DDL.ADD_STOPWORD('CLI_NAMES_IGNORE', 'PTY.');
CTX_DDL.ADD_STOPWORD('CLI_NAMES_IGNORE', 'LTD');
CTX_DDL.ADD_STOPWORD('CLI_NAMES_IGNORE', 'LTD.');
CTX_DDL.ADD_STOPWORD('CLI_NAMES_IGNORE', 'THE');
CTX_DDL.ADD_STOPWORD('CLI_NAMES_IGNORE', 'OTHERS');
END;
/
To create a specific index storage syntax clause:
BEGIN
-- CTX_DDL.DROP_PREFERENCE('CLINAMEADDR_STORE_PREFS');
CTX_DDL.CREATE_PREFERENCE('CLINAMEADDR_STORE_PREFS', 'BASIC_STORAGE');
CTX_DDL.SET_ATTRIBUTE('CLINAMEADDR_STORE_PREFS', 'I_INDEX_CLAUSE', 'tablespace large_idx compress 2');
CTX_DDL.SET_ATTRIBUTE('CLINAMEADDR_STORE_PREFS', 'I_TABLE_CLAUSE', 'tablespace large_idx');
CTX_DDL.SET_ATTRIBUTE('CLINAMEADDR_STORE_PREFS', 'K_TABLE_CLAUSE', 'tablespace large_idx');
CTX_DDL.SET_ATTRIBUTE('CLINAMEADDR_STORE_PREFS', 'R_TABLE_CLAUSE', 'tablespace large_idx LOB(DATA) STORE AS (CACHE)');
CTX_DDL.SET_ATTRIBUTE('CLINAMEADDR_STORE_PREFS', 'N_TABLE_CLAUSE', 'tablespace large_idx');
END;
/

Then, to create the Context index, using the stop-list and index storage preferences from the above two steps:
CREATE INDEX CLINAMEADDR_CONTEXT1 ON CLINAMEADDR(CLIENTFULLTEXT)
INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS('STOPLIST CLI_NAMES_IGNORE STORAGE CLINAMEADDR_STORE_PREFS')
;

Once this is done, you can use the new context index:

SELECT clientID, clientFullText
FROM cliNameAddr
WHERE CATSEARCH(clientFullText,''REPLACE(:search_criteria,' ',' NEAR ')'',NULL) > 0;

Bear in mind that I have noticed errors when passing in :search_criteria containing double spaces. So, you might want to ensure that whitepace is kept to a minimum - maybe create a little pre-parser for the data contained in :search_criteria.

After a few days of using this just to search for data, I eventually tried to perform a standard update on one of the base tables. That's when I noticed the performance problems. The database in which I am experimenting with the MV and Context concepts contains the following (active) row counts:
CLIENT_NAMES 7.5 million
CLIENT_ADDRESSES 6.7 million
ADDRESSES 8.4 million
CLIENTS 6.9 million

On updating a single row in the CLIENT_NAMES table, I noticed a 15 to 20 second wait time before response came back from the commit operation.
So I experimented a little - I tried an update on 4700 rows and noticed it took about 130 seconds to perform the update, plus another 15 MINUTES to perform the commit.
I used TOAD to investigate where it was getting stuck and it was definitely a combination of applying changes to both the MV and the Context index.
Without full knowledge on how to improve this ghastly performance problem, I may have to either retire the MV and Context solution, or stick with a refresh of the MV ON DEMAND instead of ON COMMIT.

A colleague of mine also tried a more substantial update on 300,000 rows and eventually got kicked out with
some internal error - the basis of which was a 'snapshot too old' type of error. Oracle has admitted a bug exists in this circumstance and has issued a one-off patch (5530043), but recommends upgrading to 10.2.0.4 or 11g.
I am waiting for our DBAs to upgrade one of our databases to 10.2.0.4 to see if the fix addresses some of the performance problems I noticed during my experiments, although I am doubtful of a successful outcome.

AUSOUG Conference on-line registration

2008-05-13T07:39:00.002+10:00

Registration to attend the 2008 Australian Oracle User Group conference can now be done online. Simply visit http://www.ausoug.org.au/2020/registration.html for more information.

Using Oracle Data Access Components through VB .NET 2008

2008-04-18T15:52:00.004+10:00

Oracle has provided a nice set of plugins to allow easy access to Oracle database components within Visual Studio. I've had a bit of a play around with it over the past couple of weeks while our developers evaluate various technologies, so I've put together some different ways of using the functionality. All of these techniques can be gathered from various places around the internet, but I am mainly putting them here for my own reference so I have everything in one place.

If you have VS2008, you can download the Oracle plugins from Oracle's .NET Developer Center (http://www.oracle.com/technology/software/tech/windows/odpnet/index.html). The latest version (11.1.0.6.21) allows connection to Oracle database versions 9i, 10g and 11g.

Once that is installed, you will find that VS allows you to define a Data Source using Oracle ODP.NET drivers.
Once your Data Source is set up, and you have also defined the dataset you wish to use (consisting of tables and views - no packages/procedures yet, but more on that later), you can then simply drag and drop tables from the dataset onto Window Forms to automatically create databound grids, or drag individual fields to create databound controls (the types can be configured to suit).

On the first addition of a table (or field) onto a Form, a function is also created to automatically fill the control with all records from the table, and a call to this function is placed in the Load method of the Form. This is like performing an EXECUTE_QUERY in Oracle Forms with no DEFAULT_WHERE clause, so you will have to create your own filter function to allow a bit more of a parameterized mechanism. Master-Detail relationships are, however, taken care of automatically behind the scenes - as long as the appropriate foreign keys have been set up. For example, if you drag in the DEPT table to create a datagrid for its data, then drag in the embedded EMP table from within the DEPT dataset, the relationship is recognised and the display is synchronized when DEPT entries are cycled through during runtime.

To add a filtered query, open up the Dataset viewer and right-click on the appropriate table. Choose Add->Query... Then flick through the wizard until you get to the SQL statement. Add a WHERE clause - for example "WHERE DNAME LIKE :deptName" - and a proceed through to the end of the wizard, remembering to give the new filter method an appropriate name (eg FillByDeptName). You can then either replace the method used in the Load routine, or add a search criteria field and button to accept a query from the user, then use the new method from there.

Now, this is all well and good for straight direct table and view access, but what about Stored Procedure, Function and Package access? Indeed, some database developers are not given direct access to the tables, but must instead work through a layer of PL/SQL code to get to the data. So we need to be able to call database code.
First, lets look at how to call a simple SQL statement using Visual Basic.


    Private Sub getBonusInfo(ByVal ename As String)
        ' Define variables
        Dim oraconnection As OracleConnection
        Dim oracommand As OracleCommand
        Dim returnJob As String = ""
        Dim returnSal As Decimal
        Dim returnComm As Decimal

        'Define Oracle database connection
        oraconnection = New OracleConnection(My.Settings.STConnectionString)
        'Open the connection
        oraconnection.Open()
        Try
            'Create a new Oracle command
            oracommand = New OracleCommand()
            With oracommand
                'Associate the command to the Connection opened previously
                .Connection = oraconnection
                'Define the type of command
                .CommandType = CommandType.Text
                'Create the command text
                .CommandText = "SELECT job, sal, comm FROM bonus " + _
                               "WHERE ename = :ename"
                'Add an input parameter
                .Parameters.Add(New OracleParameter(":ename", _
                                       OracleDbType.Varchar2, _
                                    ParameterDirection.Input)).Value = ename
                'Execute the Statement
                Dim reader As OracleDataReader = .ExecuteReader()
                'Interrogate the response for individual returned data elements
                While (reader.Read())
                    returnJob = reader.GetOracleString(0)
                    returnSal = reader.GetOracleDecimal(1)
                    returnComm = reader.GetOracleDecimal(2)
                End While
            End With
            'Close the command
            oracommand.Dispose()
        Catch ex As OracleException
            MsgBox(ex)
        End Try
        'Use the return data as needed
        Me.txtSal.Text = returnSal
        Me.txtJob.Text = returnJob
        Me.txtComm.Text = returnComm

        'Close the database connection
        oraconnection.Close()
    End Sub

The code contains comments where appropriate, so it is fairly self-explanitory.
Note that there is no data in the BONUS table by default, so you would have to insert some rows to see this working.

Now, calling a database function requires you to know that the first Parameter is always going to be the return parameter.
For example, say we had the following function in the database:


CREATE OR REPLACE FUNCTION getEmpCount(p_deptno IN NUMBER)
RETURN NUMBER
IS 
 lvn_count NUMBER := 0;
BEGIN
  SELECT count(*)
    INTO lvn_count
    FROM emp
   WHERE deptno = p_deptno;
  RETURN lvn_count;
END getEmpCount;

Calling the Stored Procedure from VB would look like:


    Private Function getEmpCount(ByVal deptno As Integer) As Decimal
        ' Define variables
        Dim oraconnection As OracleConnection
        Dim oracommand As OracleCommand
        Dim retVal As OracleDecimal = New OracleDecimal
        ' Function return parameter has arbitrary name
        Dim returnParam As OracleParameter = _
          New OracleParameter("myReturnValue", OracleDbType.Decimal, 10)
        returnParam.Direction = ParameterDirection.ReturnValue

        'Define Oracle database connection
        oraconnection = New OracleConnection(My.Settings.STConnectionString)
        'Open the connection
        oraconnection.Open()
        Try
            'Create a new Oracle command
            oracommand = New OracleCommand()
            With oracommand
                'Associate the command to the Connection opened previously
                .Connection = oraconnection
                'Define the type of command
                .CommandType = CommandType.StoredProcedure
                'Create the command text
                .CommandText = "getEmpCount"
                'return parameter must be added first
                .Parameters.Add(returnParam)
                .Parameters.Add(New OracleParameter("P_DEPTNO", _
                                          OracleDbType.Decimal)).Value = deptno
                .ExecuteNonQuery()
            End With
            retVal = returnParam.Value
            oracommand.Dispose()
        Catch ex As OracleException
            MsgBox(ex.Message())
        End Try
        oraconnection.Close()
        Return retVal
    End Function

Ok, on to the interesting part - User Defined Types.
This method is new to the latest version of ODP.NET (11.1.0.6.21). Previously, User Defined Types could not be handled by VB.

Let's look at a scenario. Say, for whatever reason, we have the requirement to show a big dump of data combining data from more than one table. It would probably be easier to create a view and access it that way, but where would the fun be in that? Lets create a new User Defined Type!


CREATE TYPE comboRecord IS OBJECT(
EMPNO    NUMBER(4),
ENAME    VARCHAR2(10),
MGR      NUMBER(4),
MNAME    VARCHAR2(10),
HIREDATE   DATE,
SAL   NUMBER(7,2),
DEPTNO   NUMBER(2),
DNAME   VARCHAR2(14)
TOTALBONUS   NUMBER(8,2));
/
CREATE TYPE comboRecordTable is table of comboRecord;
/

And, a function to return a table of records:


CREATE OR REPLACE FUNCTION getFullEmpDetails(p_ename IN VARCHAR2)
RETURN comboRecordTable
IS
  CURSOR comboCsr
  IS
    SELECT e.empno, 
      e.ename, 
      e.mgr, 
      e2.ename as mname,
      e.hiredate,
      e.sal,
      e.deptno,
      d.dname,
      tb.totalbonus
      FROM emp e, 
        emp e2,
        dept d,
        (SELECT ename, job, sum(sal) + sum(comm) as totalbonus
           FROM bonus b
          GROUP BY ename, job) tb
     WHERE e.mgr = e2.empno(+)
       AND e.deptno = d.deptno
       AND tb.ename(+) = e.ename
       AND tb.job(+) = e.job
       AND e.ename LIKE '%'||UPPER(p_ename)||'%';

  l_combotab comboRecordTable := comboRecordTable();
  l_comborec comboRecord;
  l_count   NUMBER := 0;
BEGIN
  l_comborec := comboRecord(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
  FOR l_rec IN comboCsr
  LOOP
    l_comborec.EMPNO := l_rec.empno;
    l_comborec.ENAME := l_rec.ename;
    l_comborec.MGR := l_rec.mgr;
    l_comborec.MNAME := l_rec.mname;
    l_comborec.HIREDATE := l_rec.hiredate;
    l_comborec.SAL := l_rec.sal;
    l_comborec.DEPTNO := l_rec.deptno;
    l_comborec.DNAME := l_rec.dname;
    l_comborec.TOTALBONUS := l_rec.totalbonus;
    l_combotab.EXTEND(1);
    l_count := l_count + 1;
    l_combotab(l_count) := l_comborec;
  END LOOP;
  RETURN l_combotab;
END getFullEmpDetails;

Now, because this new function returns a complex User Defined Type (UDT),
ODAC/ODP used to have problems with it. Now, with the latest version, you can create VB Classes and map them to the UDT. First, create new file to handle the classes.

UPDATE NOTE: This code can be automatically generated! Connect to the database from within Visual Studio, navigate to the UDT; right-click and choose Generate Custom Class...


Imports Oracle.DataAccess.Types.OracleUdt
Public Class ComboRec
    Implements INullable, IOracleCustomType

    Private m_bIsNull As Boolean
    Private m_empNo As Decimal
    Private m_eName As String
    Private m_mgr As Decimal
    Private m_mName As String
    Private m_hireDate As Date
    Private m_sal As Decimal
    Private m_deptNo As Decimal
    Private m_dName As String
    Private m_totalBonus As Decimal

    <OracleObjectMapping("EMPNO")> _
    Public Property empNo() As Decimal
        Get
            Return m_empNo
        End Get
        Set(ByVal value As Decimal)
            m_empNo = value
        End Set
    End Property

    <OracleObjectMapping("ENAME")> _
    Public Property eName() As String
        Get
            Return m_eName
        End Get
        Set(ByVal value As String)
            m_eName = value
        End Set
    End Property

    <OracleObjectMapping("MGR")> _
    Public Property mgr() As Decimal
        Get
            Return m_mgr
        End Get
        Set(ByVal value As Decimal)
            m_mgr = value
        End Set
    End Property

    <OracleObjectMapping("MNAME")> _
    Public Property mName() As String
        Get
            Return m_mName
        End Get
        Set(ByVal value As String)
            m_mName = value
        End Set
    End Property

    <OracleObjectMapping("HIREDATE")> _
    Public Property hireDate() As Date
        Get
            Return m_hireDate
        End Get
        Set(ByVal value As Date)
            m_hireDate = value
        End Set
    End Property

    <OracleObjectMapping("SAL")> _
    Public Property sal() As Decimal
        Get
            Return m_sal
        End Get
        Set(ByVal value As Decimal)
            m_sal = value
        End Set
    End Property

    <OracleObjectMapping("DEPTNO")> _
    Public Property deptNo() As Decimal
        Get
            Return m_deptNo
        End Get
        Set(ByVal value As Decimal)
            m_deptNo = value
        End Set
    End Property

    <OracleObjectMapping("DNAME")> _
    Public Property dName() As String
        Get
            Return m_dName
        End Get
        Set(ByVal value As String)
            m_dName = value
        End Set
    End Property

    <OracleObjectMapping("TOTALBONUS")> _
    Public Property totalBonus() As Decimal
        Get
            Return m_totalBonus
        End Get
        Set(ByVal value As Decimal)
            m_totalBonus = value
        End Set
    End Property

    Public ReadOnly Property IsNull() As Boolean _
    Implements Oracle.DataAccess.Types.INullable.IsNull
        Get
            Return m_bIsNull
        End Get
    End Property

    Public Sub FromCustomObject( _
        ByVal con As Oracle.DataAccess.Client.OracleConnection, _
        ByVal pUdt As System.IntPtr) _
      Implements Oracle.DataAccess.Types.IOracleCustomType.FromCustomObject

        SetValue(con, pUdt, "EMPNO", empNo)
        SetValue(con, pUdt, "ENAME", eName)
        SetValue(con, pUdt, "MGR", mgr)
        SetValue(con, pUdt, "MNAME", mName)
        SetValue(con, pUdt, "HIREDATE", hireDate)
        SetValue(con, pUdt, "SAL", sal)
        SetValue(con, pUdt, "DEPTNO", deptNo)
        SetValue(con, pUdt, "DNAME", dName)
        SetValue(con, pUdt, "TOTALBONUS", totalBonus)
    End Sub

    Public Sub ToCustomObject( _
        ByVal con As Oracle.DataAccess.Client.OracleConnection, _
        ByVal pUdt As System.IntPtr) _
      Implements Oracle.DataAccess.Types.IOracleCustomType.ToCustomObject

        empNo = GetValue(con, pUdt, "EMPNO")
        eName = GetValue(con, pUdt, "ENAME")
        If Not IsDBNull(con, pUdt, "MGR") Then
            mgr = GetValue(con, pUdt, "MGR")
            mName = GetValue(con, pUdt, "MNAME")
        End If
        hireDate = GetValue(con, pUdt, "HIREDATE")
        sal = GetValue(con, pUdt, "SAL")
        deptNo = GetValue(con, pUdt, "DEPTNO")
        dName = GetValue(con, pUdt, "DNAME")
        If Not IsDBNull(con, pUdt, "TOTALBONUS") Then
            totalBonus = GetValue(con, pUdt, "TOTALBONUS")
        End If
    End Sub
End Class

<OracleCustomTypeMapping("SCOTT.COMBORECORD")> _
Public Class ComboRecordFactory
    Implements IOracleCustomTypeFactory

    Public Function CreateObject() _
    As Oracle.DataAccess.Types.IOracleCustomType _
    Implements Oracle.DataAccess.Types.IOracleCustomTypeFactory.CreateObject
        Return New ComboRec()
    End Function
End Class

<Oracle.DataAccess.Types.OracleCustomTypeMapping("SCOTT.COMBORECORDTABLE")> _
Public Class ComboRecordTable
    Implements IOracleArrayTypeFactory
   
    Public Function CreateArray(ByVal numElems As Integer) As System.Array _
    Implements Oracle.DataAccess.Types.IOracleArrayTypeFactory.CreateArray
        Dim s(numElems) As ComboRec
        Return s
    End Function
    Public Function CreateStatusArray(ByVal numElems As Integer) As System.Array _
    Implements Oracle.DataAccess.Types.IOracleArrayTypeFactory.CreateStatusArray
        Return Nothing
    End Function
End Class

Now, whenever your VB code attempts to pull in data from your UDT structures, it has something to translate them to.
For instance, we can capture the incoming data as an Array and use it to populate a DataGrid dynamically.


    Public Sub populateDynamicFullDetails(ByVal eName As String)
        Dim oraconnection As OracleConnection = _
          New OracleConnection(My.Settings.STConnectionString)
        Dim oracommand As OracleCommand = New OracleCommand()
        Dim empArr As System.Array = Nothing
        Try
            oraconnection.Open()
            With oracommand
                .Connection = oraconnection
                .CommandType = CommandType.Text
                .CommandText = "select getFullEmpDetails(:1) from dual"
                .Parameters.Add(New OracleParameter(":1", _
                  OracleDbType.Varchar2)).Value = eName
                Dim subReader As OracleDataReader = .ExecuteReader
                While (subReader.Read())
                    If Not subReader.IsDBNull(0) Then
                        empArr = DirectCast(subReader.GetValue(0), System.Array)
                    End If
                End While
            End With
            If Not oracommand Is Nothing Then
                oracommand.Dispose()
            End If
        Catch ex As Exception
            If Not oracommand Is Nothing Then
                oracommand.Dispose()
            End If
        Finally
            If Not oraconnection Is Nothing Then
                oraconnection.Dispose()
            End If
        End Try
        Me.dgrdDyno.DataSource = empArr

    End Sub

During the retrieval of the data into the array,
ODP will detect the data type being returned from the Oracle function (comboRecordTable) and attempt to find a mapped VB class that matches. Now that we have told it how to handle it, everything should work fine.

AUSOUG National Conference Series 2008

2008-04-03T09:48:00.001+11:00

The 2008 AUSOUG National Conference Series has been announced and is making a call for papers to be submitted.
The conference is going to be located in Perth and the Gold Coast this year, and the organisers are filling the event with as much content and expertise as possible.
With high profile draw-card names like Tom Kyte and Rich Niemiec, as well as another big name in DBA circles likely to join the list, there are a lot of reasons why this year's conference is going to be a great event.
Add to that the 25% discount to the Warner Village Theme Parks (Gold Coast conference only) and it sounds like there will be heaps to keep you entertained and informed. So, what are you waiting for?

Is the news all bad for Oracle Development?

2008-04-03T09:03:00.001+11:00

While Oracle ramps up with new versions of JDev and Apex, and secretely suggests that Forms is dead (though never publicly, of course), their development tools have yet to mature to the point where companies are willing to take the leap and join the fray in bashing out code in these new environments.

With the spotlight on mutli-tiered applications that separate business, application and UI logic, Oracle has been keenly concentrating their efforts on the business and application side of things, and is now attempting to catch up to the requirements of the UI layer with their new ADF Faces RC.

From other vendors - Microsoft, Sun, and Adobe - we can also see a state of flux as we are currently in an "in-between" phase where technologies seem to be shifting.
Seeing as Sun is looking to replace Swing with their new FX technology, we will see Swing apps fall by the wayside, but FX will probably take some time to reach the same maturity level before it gets to its full potential.

Meanwhile, Microsoft is introducing Silverlight to be its all-encompassing technology, and with the beast behind it, it will gain ground quickly. And Adobe is refining their Flex development environment along with Flash and AIR.

So, let us set out and discover what glorious lands abound in the outer ranges. The following guidelines have been set out:
1.) Oracle Forms is not going to be around (and supported) forever, so we need to evaluate a replacement tool to ensure any future UI development work is going to be done with as much future-proofing as possible.
2.) Said replacement tool must give as good or better UI design flexibility as Forms.
3.) Bonus points will be given for being "standards-based" and allow us to swap out the UI for another if need be. This will aid in creating multiple UIs for any given application area - Internal application, External (public) or Remote (business users visiting remote clients/locations) - given common back-end logic.
4.) Existing developer skills (PL/SQL) would be advantageous to harness.
5.) Development Tool must provide coding integration with client-side applications (MS Word, Excel, document-management system, cheque readers, barcode scanners, flatbed printers)

Anyone like to hazard an educated guess (or do some crystal ball gazing) as to how things are going to pan out?

The war debate continues...

2008-03-11T12:14:00.002+11:00

I managed to get my hands on a copy of an article put together by Chris Muir with contributions from some leading names in Oracle development tool circles. The article attempts to shed light on which development tool is the best: JDeveloper, Apex or Forms.

The article stands out as a nice centralised scoring system for basing a decision on which tool to use in different application environments, although there is no direct comparison outlining the disadvantages of using each of the tools.

Each of the tools contains a write-up of their capabilites with gradings given on a set of 9 explicit categories, and a score for each category is given out of 5.
I noticed that all of the advocates of the tools made a point that each of their respective products could provide a workable application in "just a few clicks", but that at some point a typical developer would have to get down and dirty with some real coding to apply some complex business rules.

So it seems to me that it all comes down to realising the actual capabilities of each tool, but the main driving factor would be the comfort the developer has in using the extension language associated with the tool - be that SQL, PL/SQL, Javascript or Java. Once you have enough experience with the tool and - more importantly - the extension language set, the capabilities of the tool open up more possibilities.

When this point is realised, you can then work out where to apply your development crew. Clearly, since you are going to be working with an Oracle database, you would expect your developers to already have SQL and PL/SQL skills. But, if you want them to produce amazing applications, you also want them to specialise in the extension language. Now, what do think is more likely - Javascript or Java?

What will eventually replace Forms?

2008-02-21T08:21:00.005+11:00

Someone called 'Anonymous' (that guy sure does get around) posed a question from my previous posting on Apex vs JDeveloper, which I thought could flesh out nicely into a new blog, so here it is:

Marc what do you think will eventually replace Forms Jdeveloper/ADF? Also, can you use Java/Jdev with Apex or is it basically PLSQL environment ?

Oracle has the vision of having JDeveloper and the ADF framework as its next "best thing". They are pumping their resources into re-working their eBusiness Suite from Forms to JDev/ADF, so with that investment comes a kind of reassurance that it will not be left by the wayside. They will continue to refine and mature the ADF technology, as well as ensuring that the Support structure is there for both eBusiness Suite users and those moving from Forms to JDeveloper.

I think that Oracle is in a critical technology transition period, where the ADF technology they are promoting has not yet reached a point where it is mature as Forms. It is critical because people/businesses are going to realise that this is the case and will start to evaluate their other options more openly.

ADF Faces doesn't come close to delivering the visually appealing UI that users demand for viewing their applications, although Faces RC is getting there. ADF Swing looks like it has the capability to be a contender to a Forms UI, but Swing itself seems to be falling out of favour amongst the Java community. Even Sun themselves are not expanding the Swing framework, but are instead developing and promoting Java FX as the replacement for Swing. So we can see that there is this never ending cycle of maturing a technology then putting it out to pasture in favour the new up-and-coming solution to everything. But I guess that's how the world turns and corporations make their money.

So, I'm not really sure what will eventually replace Forms. In terms of Oracle tools, then I would side with JDeveloper/ADF for your standard application that involves not just database operations, but interaction with third-party/client-side applications. There is more emphasis on Apex for database centric applications that can be purely web-based. It depends how the application is to be deployed and used.
For non-Oracle tools, it's worth looking at Adobe Flex and Microsoft's .NET (Oracle provides a nice .NET Data Access Components plugin for rapid database-app development) to see what they offer.

Onto the second question. Apex is basically a PLSQL environment, sitting on top of (within) an Oracle database. It relies heavily on Javascript for client-side operations and AJAX functionality. I'm not sure if you can (or would want to) use Java or JDeveloper with Apex, since it is really just an application running from within the database. HTML pages are generated and served from the database, so the only Java calls would be to those classes that have been imported into the database.
So, I would say No, Apex and JDeveloper are not meant for each other - a marriage such as that would not even last through the honeymoon period, there would be waaaay too much bickering and fighting going on.

Apex vs JDev - first impressions

2008-02-19T09:33:00.004+11:00

Following on from our JDeveloper/ADF workshop, we also reserved some time to compare the benefits of Apex (formerly HTMLDB). We brought in Penny Cookson from Sage Computing to give us a 3-day run-down. Now I feel that I can give a quick first-impressions comparison between the two development approaches. Here's a quick overview, in no particular order.

Apex seems to be very good at rapid development for simple every-day data and database actions.
Apex does not seem to be very good at calling and interacting with third-party applications (except through webservices).
Apex allows rich data displays, although it is restricted to browser-compatible constructs such as HTML, Javascript and Flash charts.
JDev requires a larger learning curve than Apex, but allows separation of layers (MVC), making it easier if a differing (UI) technology is to be introduced later on down the track.
Apex simply requires the database (which can be scaled), and does not require separate Application Servers for distribution.
Neither Apex nor JDev's ADF Faces would allow us to reproduce our current Forms application look-and-feel as it stands.
JDev's ADF Swing may come close to providing the same Forms application UI, but requires a larger amount of Java programming skills.
Oracle is using Apex on its subdomains Metalink and AskTom, and seems to be quite productive with the experience.
Oracle is using JDev/ADF for re-developing its eBusiness Suite.
(As an aside, Oracle also uses Adobe Flex on Metalink - and it looks good.)

To explain where I am coming from, and the users our application has to appease, our current Forms application was met with a furious uproar when we moved from Forms 6i to Forms 10g. The users were very keen to retain all visual aspects (even down to the specific shades of grey), and also expected to retain productivity using either keyboard or mouse. In most cases we were able to provide a one-to-one match, but we had to fight the code to allow interaction with some client-side third-party apps.

If we are to attempt to reproduce Oracle Forms usability with a new technology - be that Apex, JDeveloper, or something else entirely - it has to allow the users to be as productive (or more so) based on how they already operate using the current Forms application. But we also have to consider developer productivity, and whole range of other factors.

As it stands, it seems that Apex would only be suitable for reproducing a small subset of our security/maintenance screens, or for our internet-facing applications, but could probably not cope with the demand from our internal users. JDeveloper (ADF Faces) may eventually be a candidate for our internal application development, but still does not seem to be mature enough in regard to rich UI components (note that I have not had a chance to review the offerings of ADF Faces RC to any extent).

So, the search is still on for a development approach that achieves that fine balance between developer productivity and user satisfaction...

Flexing Oracle

2008-02-07T13:38:00.000+11:00

Attempting to create a Data Access Descriptor so that I can access pl/sql via a web browser, or other simple http access. This will come in handy if/when I need to access XML data when using another UI framework (such as Adobe Flex)
First, login as SYS into my 11g database and run the following:


BEGIN
  DBMS_EPG.create_dad (
    dad_name => 'dataAccess',
    path     => '/dataAccess/*');
END;
/

BEGIN
  DBMS_EPG.authorize_dad (
    dad_name => 'dataAccess',
    user     => '<databaseuser>');
END;
/

Then I create a simple test procedure in my schema


CREATE OR REPLACE get_test_xml_p
AS
  lvc_cust_id VARCHAR2(10) := '1';
BEGIN
  htp.p('<?xml version="1.0" ?>
        <CUSTOMERS>
          <CUSTOMER>
            <ID>'||
            lvc_cust_id||
            '</ID>
            <NAME>'||
            'John Doe|| 
            '</NAME>
          </CUSTOMER>
        </CUSTOMERS>');
END GET_TEST_XML_P;
/

I can alter that to grab dynamic data later. Meanwhile, test access to that procedure in my favourite browser.


http://<machinename>:<port>/dataAccess/<databaseuser>.get_test_xml_p

It brings the expected results, so let's go from there...
In Adobe Flex, I can then create a request to call the procedure:


...
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" width="682" height="426" creationComplete="getTestXML.send()" >
 <mx:HTTPService 
  id="getTestXML" 
  url="http://<machinename>:8080/dataAccess/<databaseuser>.get_test_xml_p" 
  useProxy="false" />
...
    <mx:TextInput width="100" id="custID" text="{getTestXML.lastResult.CUSTOMERS.CUSTOMER.ID}"/>
    <mx:TextInput id="custName" text="{getTestXML.lastResult.CUSTOMERS.CUSTOMER.NAME}"/>
...

And, hey presto, the data is returned into the expected fields...
Interesting points to note:
1. The XML nodes are case sensitive, so when you are retrieving data, make sure you code it correctly.
getTestXML.lastResult.CUSTOMERS.CUSTOMER.ID is not equal to getTestXML.lastResult.customers.customer.id
2. You will be prompted for an XDB login, which equates to the login. I am fairly sure this can be setup programmatically somewhere along the line (and can of course be added as a parameter to the Data Access Descriptor through EnterpriseManager. Putting the username and password into the URL does not seem to want to work.

ADF - Using Proxy Authentication

2008-01-29T09:30:00.000+11:00

An interesting article has appeared on JHeadstart's Blog that outlines the approach to use if you currently have an application that uses database login credentials to define authentication and access. Once you move to a web application, database connections are channeled through a connection pool that consists of one pre-defined login user.

Using proxy authentication allows you to maintain the user details of logged-in users. This article steps through the options available when this approach is needed.

JDeveloper Training

2008-01-29T08:24:00.000+11:00

We had Chris Muir come in to give us a 5-day JDeveloper/ADF run-down. Our class of 12 were all extremely impressed with the presentation and we have all come away with a vast amount of new knowledge and respect for the new techniques we can employ.

In regards to the viewpoint of some Forms developers, I can see there will be some points of confusion when undertaking development in JDeveloper using ADF Business Components.

For example, when creating Entity Objects we are basically abstracting the database columns as attributes of a java class.
Then, when we create a View Object, those attributes are used to define what is displayed. However, if we want to filter or join 2 entity objects in the view object, we have to return to the database field definition (basically add a WHERE clause to join them).

The reason for this is that a VO can be based directly on database tables and fields without referring to any EOs. So, that is something that people switching between Relational and Object-Oriented related thinking must bear in mind.

There is also the learning curve of Java programming, but I guess this is just something that has to be done. In Forms development, it is easy to put together a Forms module that provides easy access to any database table and provide Enter- and Execute-Query functionality without any PL/SQL coding whatsoever. The same is true for JDeveloper programming - it seems easy enough to put together a basic web application that will give access to any particular table and automatically provide the tools and code to allow a page with that same Enter- and Execute-Query-like functionality.

However, everyone knows that sooner or later (probably sooner), you are going to have to enforce some particularly complicated business rules and calculations that lead to complex navgation rules. As with Forms, where you will have to code up some PL/SQL either in the Form (or PLL) or within the database, the same holds true for JDeveloper. Somewhere along the line you will realize that the simple Enter-Execute methods are not enough, and you will need to place some Java code in a backing bean.

We will be attending an APEX course in a couple of weeks, and I'm sure the same concept will be shown again - all the simple stuff will be visually appealing, of course. But, as soon as some complex algorithms need to be referenced, we will be forced to use Javascript to twist the simple concepts into something that works the way the users want it.

Forms Lingo translated to JDeveloper using ADF (Part 1)

2008-01-18T08:09:00.000+11:00

Oracle Forms packages a lot of functionality up for us so that we don't have to worry too much about things like data binding.
Creating an application using ADF in JDeveloper requires us to do a lot of thinking that was previously done for us by Forms, but also attempts to provide us with some flexibility.

In Forms, we usually create a Forms Module by firstly determining the base table/view that we are going to use, then creating a block based on one or more of those tables/views.
In JDeveloper, this process is more involved, as its inherent flexibility allows you to define the tables/views you are going to use, as well as specifying whether you want to allow that data to be read-only or updateable. Specifying this accessibility level early on allows you to restrict the data access methods that are auto-generated later. The application construction methods used by JDeveloper aid in using a bottom-up approach to development. The developer is made to think about the transactions that are to be involved in the module, and apply specific coding style to the development of each screen depending on the final required functionality, as well as being given the flexibility of changing the entire approach if more functionality is later required.

In Forms, once a base table block is set up, you have control over the fields that are to be returned and populated from the query, as well as the physical layout characteristics of those fields. This concept is slightly separated in JDeveloper. At the point where you specify the query (or queries) that you are going to use within the app, you only have control over the suggested physical labelling of the returned fields at that time. The other physical characteristics are handled in the View component. Because ADF development promotes the separation of the visual aspects from the business logic components, its development is split up into what is referred to as MVC - the Model/View/Controller. The Model component takes care of the data model and its relationship structure(s). The View components take care of how the user interface is presented. The Controller components hold the events and actions that interaction with the View components have on the Model components, such as pressing button, or clicking a link.

MVC terminology can be translated into Forms components. The Model is the underlying database tables (or stored procedures or dynamic SQL statements, etc) on which you base your blocks. The View is the physical canvas layout of your Forms Items - all of the UI components such as items, tabs, buttons, trees, drop-down lists etc. The Controller is all of the triggers and program units that translate events and user actions into business logic - as well as the Forms built-ins that control the flow of triggers.

11g database installation and native webservices

2007-12-27T15:39:00.000+11:00

Downloaded and installed an 11g database on a spare Windows XP machine that was floating around, while our local DBA finishes up installing our real playground on a Solaris box.

The first thing I noticed was the size of the download - Whoa! 1.8 Gb for the 11g database setup, expanding out to 2.9 Gb for the real installed database. Compare this to the download size of a 10g setup which was a smidgeon under 700Mb, expanding out to 1.6 Gb for a final installed database. I don't know what is included in the new features to take up that much more space, but *insert expletive here*, it seems to have taken quite a leap.

Once the Windows installation completed (after re-starting a couple of times due to a very invasive piece of currently installed software on the machine that had taken over the JVM - thanks Mercury!), I ensured the standard SCOTT/TIGER schema was there (Ah, EMP and DEPT, will I never forget thee), and then used the Enterprise Manager to setup a new schema. Oh, and I notice that the EM address is now accessed over https - good to see, although I kept getting certificate errors - probably an IE setting gone awry. Yes, yes, I should be using Firefox, move on...

One of the driving forces behind getting the database up and running was to test out a new capability of 11g - native Web Services for database PL/SQL code.
Ok, so new schema gets the XDB permissions granted (XDB_WEBSERVICES, XDB_WEBSERVICES_OVER_HTTP, XDB_WEBSERVICES_WITH PUBLIC) which are required for various methods of accessing web services, I assume. Then a new package is created. Something simple first - GET_TEXT_F: a function that returns some text; should we use the tried and tested "Hello World", or try something new and exciting?


create or replace
function get_text_f return varchar2 is
begin
return ('Marc was here');
end;

Spectacular! So, in theory with all of that in place, we should be able to call that little function as a web service. First, determine the WSDL:


http://<myuser>:<mypassword>@<mymachine>:<myport>/orawsv/<MYSCHEMA>/GET_TEXT_F?wsdl

Have to ensure schema and function name are in UPPERCASE! Also had a bit of a stumble with the first run, because the port for webservices had not been set! To fix that, it can simply be set manually, although I'm not sure if you are restricted to certain ports (topic for investigation later...).


execute dbms_xdb.setHttpPort(8080);

Trying the wsdl retrieval again after that is set gives a nice auto-generated WSDL document based on the function.

Use SOAP UI (www.soapui.org) to do some quick tests of the simple function (you must also remember to enter your database credentials in the SOAP request attributes) before trying something a bit more meaty!

Complex data types.
OK, say I have a database table called customers, structured something like


CREATE TABLE customers (
ID NUMBER(10),
NAME VARCHAR2(50),
DOB DATE);

Fairly simple, yes? Could I expect a table ROWTYPE return to be automatically translated by the native 11g webservice to WSDL without any extra coding? Lets see....


create or replace function get_customer_f (p_customer_id in number) return customers%ROWTYPE is
lvt_customer_rec customers%ROWTYPE;
begin
select *
into lvt_customer_rec
from customers
where customer_id = p_customer_id;
return (lvt_customer_rec);
end;

Yes, I know, I don't have any exception handling, move on...
Meanwhile, lets test to see what the WSDL comes out as


http://<myuser>:<mypassword>@<mymachine>:<myport>/orawsv/<MYSCHEMA>/GET_CUSTOMER_F?wsdl

Bzzzzzzzzzzzz, you basically get a 'Error processing input' error.
What we need to do is transform the return data into a native database data type, which means you are going to have to create TYPEs based on what your functions are going to accept and return. So, in this instance, we would need to create a customer_type


CREATE OR REPLACE TYPE CUSTOMER_TYPE AS OBJECT (
ID number(10),
NAME varchar2(50)
)

then, we need to change our function to cope with the new type:


create or replace function get_customer_f (p_customer_id in number) return customer_type is
lvt_customer_type customer_type;
begin
lvt_customer_type := customer_type(null,null);
select id, name
into lvt_customer_type.id, lvt_customer_type.name
from customers
where customer_id = p_customer_id;
return (lvt_customer_type);
end;

Now, since the database can trace the CUSTOMER_TYPE back down to primitive database types, the WSDL can be auto-generated. I would like to see if Oracle could manage to make this just a little bit easier for us kids who don't want to go around making up database types left, right and center.
Time for a post to mix.oracle.com
That's all for now...

ADF Beginnings

2007-12-24T07:49:00.000+11:00

Ok, I have started the ascent into the learnings of Oracle's ADF technology stack.

A bit of background first: I am mainly an Oracle Forms/Reports and PL/SQL developer, and have been for the past 10+ years, but its been a bit of a static learning path, and I have only picked up a few new things here and there since I have been on basically the same project during all of that time. I started my first real-world project with Forms 4.5 (16-bit) and when it was in a production state, helped migrate it up to a 32-bit version. Then I moved interstate (from the Australian West Coast, to Sydney) since the project I originally worked on was sold as an application package to an interstate related organisation.

This move saw the same app being migrated and enhanced to a 6i/8i app, and I also had the chance to code a subset of it into a VB6/Access application that could be used during network/database downtime.

Then the application code was picked up by another interstate related organisation in Melbourne, so I packed my bags and arrived there on Boxing Day of 1999. Things started to get a little more interesting here as the team was interested in making a a more robust application with more emphasis on visual navigation, so among other things, I spent some time helping to put together a Hierarchical Tree control for navigation between Forms. This has worked out very well, and continues to be a great way for the users to jump between application areas.

At the end of 2005, we started the migration to 10g (both Forms and the Database), and successfully deployed the 're-stumped' application in early 2007. The migration gave us a few headaches - since our users had a high reliability on the visual and navigational aspects of the application, sorting out the JVM idiosynchrasies required close (intimate?) interaction with Oracle support. Neither JInitiator nor Sun Java 1.5 gave us 100% satisfaction, but JInitiator turned out to be the lesser of two evils, so we have gone the route for now.

I also spent some time creating a build and deployment tool (using Forms) that comes in two flavours: One that developers could use to easily transfer and compile Forms code to our development unix application server and allow them to run it in their own isolated environment; and another that the Build Administrator can use to extract modules from our Source Control application and transfer and compile it in a series of easy steps to our Test application server.

Meanwhile, we have started looking at expanding requirements and the need to ensure we are working on a supported development base. Since the wafting tendrils of the fear of Forms being de-supported seems to be looming, we have started looking at alternatives for a future front-end for our application. Management has also been throwing around the 'SOA' acronym, and we all know what that means! (If any does actually know what that means, please feel free to enlighten me...)

So, a couple of months ago (hey, I've been a busy man), I downloaded JDeveloper 10g (10.1.3.2 AND 10.1.3.3) and I've been using it for a bit of Java development that we have gradually been implementing on both the Application Server, and on the Client.
Now, I have downloaded JDev 11g (Tech Preview 2) and have noticed a few annoying bugs with it (Refactoring is a nightmare), as well as the complete absence of SOA and web servce functionality.

My intention now is to have a bash at ADF development as well as looking at APEX and whatever it has to offer.

*UPDATE* JDev 11g Tech Preview 3 has just been made available. Stay tuned for more excitement as the story unfolds...