Oracle - Curson In, Cursor Out In VB.NET

  • Thread starter Thread starter deneb
  • Start date Start date
D

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