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?