none
returning tabular data from an Oracle stored procedure RRS feed

  • Question

  • I've got what should be a simple question, but searching the forums has yielded no results. All I want to do is return data from an Oracle stored procedure into a dataset using the dataset designer. As many people on the forums have pointed out, when you try to do this, the Tabular data option is disabled. I am using the OLE provider. 

    Unfortunately, while the problems seems fairly common, I have seen no solutions. 

    One clue (maybe). I attempted to create the tableadapter to return a single scalar value, just to see if I could modify it later. I got an error that says:
    System.Data.OleDb.OleDbDataAdapter internal error: invalid parameter accessor: 1 USUPPORTED CONVERSION. 

    The only paramter I have is the OUT parameter, which is the cursor I'm trying to return. 

    any ideas? 

    • Moved by Himanshu Vasishth Thursday, April 23, 2009 9:27 PM Geoff, I am moving this thread to DataSet forum where someone might have an answer for your question
    Tuesday, April 21, 2009 3:20 PM

All replies

  • Could you post your code? You may also want to check the below article for an Oracle example which works with a ref cursor:


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, April 21, 2009 3:32 PM
  • There is no code. I'm doing this entirely through the Visual Studio 2008 dataset designer. I dragged the stored procedure object from the server explorer and dropped it in the dataset. When I attempt to configure it, there are three options: Tabular, single value, and no value. Tabular, which is what I need, is disabled. 

    I will look at that article, though. Thanks. 
    Tuesday, April 21, 2009 3:37 PM
  • If you are unable to resolve the issue I would post the contents of your stored procedure.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, April 21, 2009 4:36 PM
  • Here's the stored procedure; it's pretty simple. types.rsltcurtyp is defined in a package as a REF CURSOR. 

    Just a note: within the server explorer, I am able to get results from the stored procedure. That doesn't help me at all, except to indicate that the problem is not likely to be with the stored procedure. 


    CREATE OR REPLACE
    PROCEDURE DCF_52.SP_OPENITEMS (cursItems out types.rsltcurtyp) AS
    BEGIN
        open cursItems for select sw_reportid from swreportitems where sw_reportid in (select sw_reportid from swreport
         )  and selection1_c <> 'Compliance';
     
    END SP_OPENITEMS;
    Tuesday, April 21, 2009 5:12 PM
  • OLEDB provider does not support tabular data parameters (neither System.Data.OracleClient). Instead, you can use Oracle Data Provider for .Net (ODP.Net) to send/receive tabular data from Oracle database. Try this sample: http://www.codeproject.com/KB/database/Rajesh_Babu.aspx - search on Oracle's documentation / web site for more details.

    Postings are provided "As Is" with no warranties and confer no rights.
    Tuesday, April 21, 2009 9:49 PM
  • I tried that one, actually, but it says "the dropped database objects cannot be added to the dataset". The example you give is a code example; is it possible that Oracle stored procedures cannot be done in the designer? That would seem to be a pretty big drawback.

    I will move ahead with trying it in code. Thanks. But hopefully someone will have a way to use the designer, since that is how I'm organizing all my data.

    Wednesday, April 22, 2009 1:35 PM
  • The message from Nissim above had been marked as an answer, so I unmarked it. Hope that doesn't bother anyone. The question was, at its simplest, how can I get an Oracle Stored Procedure to work through the Dataset Designer. So far, I have not been able to do so, regardless of the provider I am using.



    Wednesday, April 22, 2009 5:23 PM