Tuesday, August 17, 2010

OAUG Winner!

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...

Tuesday, March 16, 2010

SQL Developer - Search Source Code - now including Views!

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)

Thursday, July 9, 2009

NVL, NVL2 and CASE

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.


Tuesday, July 7, 2009

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

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.

Wednesday, November 5, 2008

Allow Oracle Forms to receive messages from external sources

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...

Thursday, August 28, 2008

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

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.

Tuesday, May 13, 2008

AUSOUG Conference on-line registration

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.