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