Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

oracle ref cursor called in vb.net

rikb53rikb53 Posts: 64Member
guys, im missing one little thing here and i just thought someone might be able to tell where that is.
im using an Input parameter, to retrieve a Ref Cursor from an Oracle stored procedure, and trying to display the information in a DataGrid object.
i'll include the ref cursor, but i know that's correct and its' pulling data. i can call this stored proc from oracle consistently.
and even when i debug in vb.net i can see its' pulling data by looking at the count property.
and it changes the datagrid to the columns that it's pulling, so i'm right there at the finish line but, nothing is populating and i know its got to be simple
please if someone could help, i'd really appreciate it.

here's the package spec for the ref cursor:::::


CREATE OR REPLACE PACKAGE cursor_pk
AS TYPE t_cursor IS REF CURSOR;
PROCEDURE get_orgs(empl IN OUT org_history.empl_id%type
,io_cursor OUT t_cursor);
end cursor_pk;


AND NOW THE PACKAGE BODY:::::::::::

CREATE OR REPLACE PACKAGE BODY cursor_pk
AS

PROCEDURE get_orgs(empl IN OUT org_history.empl_id%type
,io_cursor OUT t_cursor)
is
v_cursor t_cursor;
begin
open v_cursor for
select empl_id,org_lvl_1,org_lvl_2,org_lvl_3,org_lvl_4
from org_history
where empl_id = empl and balance_date = (select MAX(balance_date)
from org_history
where empl_id = empl);

io_cursor := v_cursor;
end get_orgs;
end cursor_pk;


and now the code in VB::::::::.
Sub btnGo_Click(THE REST YOU KNOW - ITS AUTOMATIC))

Dim empl As String
Dim x As Exception
Dim ds As New DataSet
empl = UCase(txtempl.Text)
Dim oraclecon As New OracleConnection("Server=trng;Uid=rik;Pwd=rikb53")
oraclecon.Open()
ds.Clear()
ds.EnforceConstraints = False
Dim myCmd As New OracleCommand
myCmd.Connection = oraclecon
myCmd.CommandText = "cursor_pk.get_orgs"
myCmd.CommandType = CommandType.StoredProcedure
myCmd.Parameters.Add(New OracleParameter("empl", OracleClient.OracleType.VarChar)).Value = 123
myCmd.Parameters.Add(New OracleParameter("io_cursor", OracleClient.OracleType.Cursor)).Direction = ParameterDirection.Output
Dim myDa As New OracleDataAdapter(myCmd)
Try
myDa.Fill(ds)
If ds.Tables.Count = 0 Then
MessageBox.Show("nothing found")
ElseIf ds.Tables.Count = 1 Then
Else
End If
Catch ex As Exception
MessageBox.Show(x.Message.ToString)
End Try
DataGrid1.DataSource = ds.Tables(0)
oraclecon.Close()

END SUB;
like i said it's getting data just fine and the count property is 1 as it should be.

any help is appreciated
thanks as usual
rik

Comments

  • infidelinfidel Posts: 2,900Member
    [b][red]This message was edited by infidel at 2004-7-23 10:23:35[/red][/b][hr]
    : guys, im missing one little thing here and i just thought someone might be able to tell where that is.
    : im using an Input parameter, to retrieve a Ref Cursor from an Oracle stored procedure, and trying to display the information in a DataGrid object.
    : i'll include the ref cursor, but i know that's correct and its' pulling data. i can call this stored proc from oracle consistently.
    : and even when i debug in vb.net i can see its' pulling data by looking at the count property.
    : and it changes the datagrid to the columns that it's pulling, so i'm right there at the finish line but, nothing is populating and i know its got to be simple
    : please if someone could help, i'd really appreciate it.
    :
    : here's the package spec for the ref cursor:::::
    :
    :
    : CREATE OR REPLACE PACKAGE cursor_pk
    : AS TYPE t_cursor IS REF CURSOR;
    : PROCEDURE get_orgs(empl IN OUT org_history.empl_id%type
    : ,io_cursor OUT t_cursor);
    : end cursor_pk;
    :
    :
    : AND NOW THE PACKAGE BODY:::::::::::
    :
    : CREATE OR REPLACE PACKAGE BODY cursor_pk
    : AS
    :
    : PROCEDURE get_orgs(empl IN OUT org_history.empl_id%type
    : ,io_cursor OUT t_cursor)
    : is
    : v_cursor t_cursor;
    : begin
    : open v_cursor for
    : select empl_id,org_lvl_1,org_lvl_2,org_lvl_3,org_lvl_4
    : from org_history
    : where empl_id = empl and balance_date = (select MAX(balance_date)
    : from org_history
    : where empl_id = empl);
    :
    : io_cursor := v_cursor;
    : end get_orgs;
    : end cursor_pk;
    :
    :
    : and now the code in VB::::::::.
    : Sub btnGo_Click(THE REST YOU KNOW - ITS AUTOMATIC))
    :
    : Dim empl As String
    : Dim x As Exception
    : Dim ds As New DataSet
    : empl = UCase(txtempl.Text)
    : Dim oraclecon As New OracleConnection("Server=trng;Uid=rik;Pwd=rikb53")
    : oraclecon.Open()
    : ds.Clear()
    : ds.EnforceConstraints = False
    : Dim myCmd As New OracleCommand
    : myCmd.Connection = oraclecon
    : myCmd.CommandText = "cursor_pk.get_orgs"
    : myCmd.CommandType = CommandType.StoredProcedure
    : myCmd.Parameters.Add(New OracleParameter("empl", OracleClient.OracleType.VarChar)).Value = 123
    : myCmd.Parameters.Add(New OracleParameter("io_cursor", OracleClient.OracleType.Cursor)).Direction = ParameterDirection.Output
    : Dim myDa As New OracleDataAdapter(myCmd)
    : Try
    : myDa.Fill(ds)
    : If ds.Tables.Count = 0 Then
    : MessageBox.Show("nothing found")
    : ElseIf ds.Tables.Count = 1 Then
    : Else
    : End If
    : Catch ex As Exception
    : MessageBox.Show(x.Message.ToString)
    : End Try
    : DataGrid1.DataSource = ds.Tables(0)
    : oraclecon.Close()
    :
    : END SUB;
    : like i said it's getting data just fine and the count property is 1 as it should be.
    :
    : any help is appreciated
    : thanks as usual
    : rik

    Not sure what to tell you. Here is some code I just wrote and it works fine. It looks a little different than yours but seems functionally equivalent.

    [code]
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim connection As OracleConnection
    Dim command As OracleCommand
    Dim adapter As OracleDataAdapter
    Dim ds As DataSet

    Try
    connection = New OracleConnection("blah blah blah")

    command = New OracleCommand
    With command
    .Connection = connection
    .CommandType = CommandType.StoredProcedure
    .CommandText = "my_pkg.get_region_data_sp"
    .Parameters.Add(New OracleParameter("i_region_id", OracleType.VarChar)).Value = "RGN9"
    .Parameters.Add(New OracleParameter("i_begin_date", OracleType.DateTime)).Value = DateTime.Now.AddDays(-365)
    .Parameters.Add(New OracleParameter("i_end_date", OracleType.DateTime)).Value = DateTime.Now.AddDays(365)
    .Parameters.Add(New OracleParameter("o_return_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output
    End With

    adapter = New OracleDataAdapter(command)
    ds = New DataSet
    adapter.Fill(ds)

    Me.DataGrid1.DataSource = ds.Tables(0)

    Catch ex As Exception
    MsgBox(ex.ToString)
    Finally
    ds = Nothing
    adapter = Nothing
    command = Nothing
    Try
    'try to close the connection no matter what
    connection.Close()
    Catch ex As Exception
    'do nothing
    End Try
    connection = Nothing
    End Try

    End Sub
    [/code]

    All I can say is that if your procedure is returning the columns but no data, then the query must be returning no rows.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]



  • rikb53rikb53 Posts: 64Member
    [b][red]This message was edited by rikb53 at 2004-7-23 4:46:30[/red][/b][hr]
    i found the mistake (big fat user error,me) a typo on one of my variables. but, i really like the style of the code you sent and i appreciate you responding. i will start using that., but i do have one more question.
    have you ever experienced this when calling a stored procedure from oracle?
    when i open up the form and type in my variable to be used in the stored procedure, IT WORKS JUST FINE ONCE.
    but, to, say, change the variable and look for something else causes this ("multiple instances of named argument in list") and the only recourse is to close the program - re-open it and make another call.
    since the arguments come from the client side, i hardly think its an oracle problem and in the stored procedure, i do close the cursor.

    thanks again
    rik






  • infidelinfidel Posts: 2,900Member
    : when i open up the form and type in my variable to be used in the stored procedure, IT WORKS JUST FINE ONCE.
    : but, to, say, change the variable and look for something else causes this ("multiple instances of named argument in list") and the only recourse is to close the program - re-open it and make another call.
    : since the arguments come from the client side, i hardly think its an oracle problem and in the stored procedure, i do close the cursor.

    Sounds like whatever collection is storing the arguments or parameters for your stored procedure is not being reset between calls. Like you're effectively trying to add the same argument to the list twice.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

Sign In or Register to comment.