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.

Friday, April 18, 2008

Using Oracle Data Access Components through VB .NET 2008

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.

Thursday, April 3, 2008

AUSOUG National Conference Series 2008

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?

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?

Tuesday, March 11, 2008

The war debate continues...

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?

Thursday, February 21, 2008

What will eventually replace Forms?

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.

Tuesday, February 19, 2008

Apex vs JDev - first impressions

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

Thursday, February 7, 2008

Flexing Oracle

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.

Tuesday, January 29, 2008

ADF - Using Proxy Authentication

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

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.

Friday, January 18, 2008

Forms Lingo translated to JDeveloper using ADF (Part 1)

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.