Oracle - Curson In, Cursor Out In VB.NET

[deneb]

I read this article.
http://www.oracle.com/technology/oramag/oracle/06-jan/o16odpnet.html
It's about using Cursor In and Cursor Out in a .NET application that
accesses Oracle Database 10g Release 2.

I have some problems in my application that is supposed to use Cursor
as In parameter.
My application accesses Oracle Database 9.2.0.4.0.

First I installed ODP .NET for Oracle 9.2.0.7.0.

And Then I made two test Procedure on my test DB.

One----------- for Cursor Out
CREATE OR REPLACE PROCEDURE USP_USERSELECT
(P1 IN varchar2, P2 IN varchar2,
USER_CUR OUT SYS_REFCURSOR) --TYPES.CURSORTYPE)
IS
BEGIN
OPEN USER_CUR FOR
select EMPNO,ENAME,SAL from emp t;
--where ENAME > P1 and job=P2;

--USP_USERINSERT(USER_CUR);
END USP_USERSELECT;

Two----------- for Cursor In
CREATE OR REPLACE PROCEDURE USP_USERINSERT
(P1 IN SYS_REFCURSOR) --TYPES.CURSORTYPE)
IS
V_EMPNO number(4);
V_ENAME varchar2(10);
V_SAL number(7,2);
--i number(2);
BEGIN
FOR i IN 1..5 LOOP
BEGIN
FETCH P1 INTO V_EMPNO, V_ENAME, V_SAL;
EXIT WHEN P1%NOTFOUND;
INSERT INTO temp1 VALUES(V_EMPNO, V_ENAME, V_SAL);
COMMIT;
END;
END LOOP;
CLOSE P1;
END USP_USERINSERT;

Finally I wrote my VB.NET Code to test Cursor In and Cursor Out.

Imports Oracle.DataAccess

Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Code gernerated by Windows Form Designer"
.....................
codes are omitted
.....................
#End Region

Public Conn As Client.OracleConnection

Public Sub Connection_DB()
Try
Dim myConnString As String = "User
Id=scott;Password=tiger;Data Source=ORA_LINUX;"

Conn = New Client.OracleConnection(myConnString)
Conn.Open()
Catch ex As Client.OracleException
MsgBox(ex.Message)
Application.Exit()
Catch ex As Exception
MsgBox(ex.Message)
Application.Exit()
End Try
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Connection_DB()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim OleRead As Client.OracleDataReader
Dim OleAdap As New Client.OracleDataAdapter
Dim OleCmd As New Client.OracleCommand
Dim dataset As New DataSet
Dim dataset2 As New DataSet

OleCmd.Connection = Conn

OleAdap.SelectCommand = OleCmd
'OleCmd.CommandType = CommandType.Text
'OleCmd.CommandText = "select EMPNO,ENAME,SAL from emp t"

'Try
' OleAdap.Fill(dataset2, "Test2")
' DataGrid2.DataSource = dataset2.Tables(0)
'Catch ex As Exception
' MsgBox(ex.Message)
'End Try

OleCmd.CommandType = CommandType.StoredProcedure
OleCmd.CommandText = "USP_USERSELECT"
OleCmd.Parameters.Clear()

Dim p As Client.OracleParameter
Dim p_cur As Client.OracleParameter

p = New Client.OracleParameter("P1", "B")
p.Direction = ParameterDirection.Input
OleCmd.Parameters.Add(p)

p = New Client.OracleParameter("P2", "MANAGER")
p.Direction = ParameterDirection.Input
OleCmd.Parameters.Add(p)

'p = New Client.OracleParameter("USER_CUR",
Client.OracleDbType.RefCursor)
'p.Direction = ParameterDirection.Output
'OleCmd.Parameters.Add(p)

p_cur = New Client.OracleParameter("P3",
Client.OracleDbType.RefCursor)
p_cur.Direction = ParameterDirection.Output
p_cur.Value = DBNull.Value
OleCmd.Parameters.Add(p_cur)

Try
OleCmd.ExecuteNonQuery()
'OleAdap.Fill(dataset, "Test")
'DataGrid1.DataSource = dataset.Tables(0)

OleRead = p_cur.Value.GetDataReader()
Console.WriteLine("Field count: " & OleRead.FieldCount)
Catch ex As Exception
Console.WriteLine(ex.Message)
'MsgBox(ex.Message)
End Try

OleCmd.CommandType = CommandType.StoredProcedure
OleCmd.CommandText = "USP_USERINSERT"
OleCmd.Parameters.Clear()

Try
Dim p_in As Client.OracleParameter
p_in = New Client.OracleParameter("P_IN",
Client.OracleDbType.RefCursor)
p_in.Direction = ParameterDirection.Input
p_in.Value = p_cur.Value
OleCmd.Parameters.Add(p_in)

OleCmd.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
'MsgBox(ex.Message)
End Try
End Sub
End Class

Test Process is following..

1.Call procedure USP_USERSELECT, Get Cursor as Output Parameter Into
"p_cur".
[To this point, it works right.]

2.Call procedure USP_USERINSERT with Cursor In Parameter - p_in that
its value has p_cur.Value. [ as shown in document that I first linked ]


3.What I expect is TEMP1 Table has some data of Cursor IN because
INSERT Statement in USP_USERINSERT procedure.

But INSERT doesn't happen.
What's wrong with my application or process?

I get this Error Message from the last Try.. Catch Block.

ORA-01001: Invalid Cursor
ORA-06512: "SCOTT.USP_USERINSERT", at line 19
ORA-06512: at line 1

and after a few minitues, I finally get "Object reference not set to an
instance of an object." error.

Please Help Me..

 

[Guest]

deneb,
That article only applies to Oracle Database 10g. Since you're using the 9i
version of an Oracle database, you won't be able to use the Cursor In.

-- Andrew Mauer

 

[deneb]

Thank you for your advice