# Solved: Using Oracle stored procedure with SqlDataSource



## Ariena (Apr 4, 2008)

Hi all,

I have a stored procedure for an Oracle 11g database which I want to display the results of in an ASP page (VB code file). The actual query is rather long because it joins several tables together, but the basic idea is:


```
CREATE PROCEDURE search_proc(cur IN OUT sys_refcursor)
AS
BEGIN
  OPEN cur FOR
    SELECT DISTINCT *
    FROM my_table
    ORDER BY name;
END search_proc;
```
So I have this procedure, which returns an open cursor with the results of that search.
How do I access this cursor in a SqlDataSource control?

What I have thus far in the aspx file:

```
<asp:SqlDataSource
    id="SqlDataSource1"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
    ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>"
    SelectCommandType="StoredProcedure"
    SelectCommand="search_proc"
    >

<SelectParameters>
<asp:Parameter Name="cur" Direction="Output" />
</SelectParameters>

</asp:SqlDataSource>
```
Nothing relevant in the vb file. I tried just retrieving rows from the cursor without using the SqlDataSource control but I don't know how to access the cursor.

One page I found (http://www.codeproject.com/KB/database/OraSqlDataSource.aspx) tells me that I need to assign a type to the cursor object? Is that correct, and if so, how do I do that?

Thanks in advance
- Ariena


----------



## Ariena (Apr 4, 2008)

Update.. I now am stuck on the error "Cannot find table 0".

What I changed was: Bypassed SqlDataSource control, added an event "OnNeedDataSource" for my grid, and am executing the sproc from my VB code:

```
Protected Sub RadGrid1_NeedDataSource(ByVal [source] As Object, ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource
        Dim connStr = "Data Source=orcl;User Id=user;Password=password;"
        conn = New OracleConnection(connStr)
        conn.Open()

        Dim sql As String = "search_proc"
        Dim cmd As New OracleCommand(sql, conn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add("cur", OracleDbType.RefCursor, ParameterDirection.InputOutput)

        Dim da As New OracleDataAdapter(cmd)
        Dim ds As New DataSet()
        da.Fill(ds)

        RadGrid1.DataSource = ds.Tables(0)
        conn.Close()
End Sub
```
I checked the SQL and it ought to be returning results. Am I doing the OracleDataAdapter, DataSet stuff correctly? I'm new to the whole Oracle and ASP / VB.NET thing... any help would be much appreciated!

- Ariena


----------



## Ariena (Apr 4, 2008)

Another update, in case anyone eventually finds this useful 

This code works - kind of. Still telling me it's not returning any results, but not giving me errors now.


```
Protected Sub RadGrid1_NeedDataSource(ByVal [source] As Object, ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource
        Me.testlbl.Text = "Datasource = " + conn.DataSource
        conn.Open()

        Try
            Dim sql As String = "search_proc"
            Dim cmd As New OracleCommand(sql, conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("cur", OracleDbType.RefCursor, ParameterDirection.InputOutput)

            Dim dr = cmd.ExecuteReader()
            RadGrid1.DataSource = dr
            'RadGrid1.DataBind()
            'dr.Close()

            Me.testlbl.Text += "<br /><b>Success!</b>"
        Catch err As System.Exception
            Me.testlbl.Text += "<br /><b>Error!</b><br /><div id='err'>" + err.ToString() + "</div>"
        End Try
        conn.Close()
    End Sub 'RadGrid1_NeedDataSource
```
The line "RadGrid1.DataBind()" causes an error because you aren't supposed to have it in a NeedDataSource handler, I think.
Also, if you call dr.Close() where it is in the code, it won't be able to load the data and gives an error.

Not sure why there aren't any results, so I'll update if I get that fixed.


----------



## Ariena (Apr 4, 2008)

Fixed that problem - the procedure was returning an invalid cursor. I removed the statement in my procedure which declared the cursor and the sql works now.

Unfortunately, this leads to yet another error: "Connection must be open for this operation." (any help is still much appreciated!)


----------



## Ariena (Apr 4, 2008)

Fixed!
I had to replace the code in my vb file with:

```
Protected Sub RadGrid1_NeedDataSource(ByVal sender As Object, ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource
        conn.Open()

        Try
            Dim sql As String = "search_proc"
            Dim cmd As New OracleCommand(sql, conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("cur", OracleDbType.RefCursor, ParameterDirection.InputOutput)

            ' This is what works
            Dim da As New OracleDataAdapter(cmd)
            Dim ds As New DataSet()
            da.Fill(ds)
            Me.RadGrid1.DataSource = ds

            ' This was what I had previously - gives error
            'dr = cmd.ExecuteReader()
            'RadGrid1.DataSource = dr

            Me.testlbl.Text += "<br /><b>Success!</b>"
        Catch err As System.Exception
            Me.testlbl.Text += "<br /><b>Error!</b><br /><div id='err'>" + err.ToString() + "</div>"
        End Try

        conn.Close()
    End Sub 'RadGrid1_NeedDataSource
```


----------

