Skip to content

Oracle Query from .Net using ref_cursor function retuns NULL for JOINED columns

December 13, 2010

Create two test tables and execute the two queries (#1 & #2) in Sql Developer. Then you will get exactly the same results. But when I defined a function returning a ref_cursor based on those two queries, my vb.net code retrieves two different results. I suspect that it’s a bug of ODT (or ODAC? ODP? whatever…).

Summarizing, ‘LEFT JOIN’ coupled with subquery, WITH clause, ref_cursor, function, ODP.NET gets wrong data (NULL) when exact same query using an old style comma separated table and WHERE clause to make a JOIN worked.


--Create test table #1
CREATE TABLE TestTable1
AS
SELECT 1 as PO FROM Dual
UNION
SELECT 2 as PO FROM Dual;

--Create test table #2
CREATE TABLE TestTable2
AS
SELECT 1 as PO, 0 as Seq FROM Dual
UNION
SELECT 1 as PO, 1 as Seq FROM Dual
UNION
SELECT 1 as PO, 2 as Seq FROM Dual
UNION
SELECT 2 as PO, 0 as Seq FROM Dual;

-- Query #1: Select with JOIN using WITH
WITH p1 as
(SELECT t1.PO, t2.Seq
FROM TestTable1 t1 LEFT JOIN TestTable2 t2 ON t1.PO=t2.PO
)
SELECT q1.*
FROM p1 q1
WHERE Seq>0 OR NOT EXISTS (SELECT 0 FROM p1 WHERE PO=q1.PO AND Seq>q1.Seq);

-- Query #2: Select with old style comma separated tables using WITH
WITH p1 as
(SELECT t1.PO, t2.Seq
FROM TestTable1 t1,TestTable2 t2
WHERE t1.PO=t2.PO
)
SELECT q1.*
FROM p1 q1
WHERE Seq>0 OR NOT EXISTS (SELECT 0 FROM p1 WHERE PO=q1.PO AND Seq>q1.Seq);

--Define a function returning ref-cursor
create or replace
FUNCTION SELECT_PRO_TEST
RETURN SYS_REFCURSOR IS
--When you retrieve data from .NET code, first query returns NULL for Seq column even though those two queries
-- returns exactly the same results in SqlDeveloper direct query.
--This seems like ODP.NET bug.

ref_cursor SYS_REFCURSOR;
BEGIN

OPEN ref_cursor FOR

--/* --Query #1
WITH p1 as
(SELECT t1.PO, t2.Seq
FROM TestTable1 t1 LEFT JOIN TestTable2 t2 ON t1.PO=t2.PO
)
SELECT q1.*
FROM p1 q1
WHERE Seq>0 OR NOT EXISTS (SELECT 0 FROM p1 WHERE PO=q1.PO AND Seq>q1.Seq);
--*/

/* --Query#2
WITH p1 as
(SELECT t1.PO, t2.Seq
FROM TestTable1 t1,TestTable2 t2
WHERE t1.PO=t2.PO
)
SELECT q1.*
FROM p1 q1
WHERE Seq>0 OR NOT EXISTS (SELECT 0 FROM p1 WHERE PO=q1.PO AND Seq>q1.Seq);
*/

RETURN ref_cursor;
END;

--This is .Net code
Public Shared Function GetDataProTest() As DataTable
Dim dt As DataTable = GetDataTableFromOracleRefCursor(My.Settings.CONNSTR_VISION, "SELECT_PRO_TEST")
Return dt
End Function

Public Shared Function GetDataTableFromOracleRefCursor(ByVal ConnectionStringName As String, ByVal FuncName As String,
Optional ByVal Param1 As OracleParameter = Nothing, Optional ByVal Param2 As OracleParameter = Nothing,
Optional ByVal Param3 As OracleParameter = Nothing, Optional ByVal Param4 As OracleParameter = Nothing)

'Imports Oracle.DataAccess.Client
'Imports Oracle.DataAccess.Types

''Parameter generation example in caller:-----------------------------------------
'Dim param1 As OracleParameter = New OracleParameter("EmployeeNumber", OracleDbType.Decimal, ParameterDirection.Input)
'param1.Value = EmployeeNumber
'-----------------------------------------------------------------------------

'Arguments check
If FuncName Is Nothing OrElse FuncName.Trim = "" Then
Throw New Exception("ObjectDataSources.GetDataTableFromOracleRefCursor():Arg 'FuncName' is nothing or empty")
End If
If ConnectionStringName Is Nothing OrElse ConnectionStringName.Trim = "" Then
Throw New Exception("ObjectDataSources.GetDataTableFromOracleRefCursor():Arg 'ConnectionStringName' is nothing or empty")
End If

Try
Dim ds As New DataSet

Using conn As New OracleConnection(Utility.GetConnectionString(ConnectionStringName))
If conn Is Nothing Then Return Nothing

Using cmd As New OracleCommand(FuncName, conn)
If cmd Is Nothing Then Return Nothing

cmd.CommandType = CommandType.StoredProcedure

'Return refcursor parameter must be defined first
Using p_refcursor As OracleParameter = New OracleParameter
p_refcursor.OracleDbType = OracleDbType.RefCursor
p_refcursor.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(p_refcursor)

If Param1 IsNot Nothing Then cmd.Parameters.Add(Param1)
If Param2 IsNot Nothing Then cmd.Parameters.Add(Param2)
If Param3 IsNot Nothing Then cmd.Parameters.Add(Param3)
If Param4 IsNot Nothing Then cmd.Parameters.Add(Param4)

Using da As OracleDataAdapter = New OracleDataAdapter(cmd)
da.AcceptChangesDuringFill = False
da.Fill(ds)
End Using
End Using
End Using
End Using

If ds.Tables.Count > 0 Then
Return ds.Tables(0) 'Sorting must be done in caller
Else
Return Nothing
End If

Catch ex As Exception
Utility.HandleException(ex)
Return Nothing
End Try
End Function

Environment: Dev machine OS=Windows 7, Visual Studio 2010, .Net Framework 3.5. Oracle 11g (AIX)
Oracle ODTwithODAC1110621

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: