1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Returning Procedure OUT Arguments as a Recordset

Discussion in 'SQL PL/SQL' started by C0D3BR3AK, Jun 5, 2010.

  1. C0D3BR3AK

    C0D3BR3AK Guest

    I normally just work with MS Access but need to set up a 3rd party application to retrieve the results of a stored procedure from a customer's Oracle 9 database. I'm testing with 10g XE since I can't find a download for v9. The application uses ADO to connect to Oracle but only allows you to execute SQL commands and view the recordset that's returned (if any). It doesn't support setting/retrieving parameter values. It's similar to a generic SQL query tool that's just meant to do simple INSERT, UPDATE, and SELECT statements.

    The procedure looks like this (except it has more OUT arguments):

    Code (Text):
    create or replace procedure "P_GET_DATA"
    (barcode IN VARCHAR2,
    prodcode OUT VARCHAR2,
    diameter OUT NUMBER)
     
    I need to pass the barcode in and retrieve all the OUT values as a recordset.

    I simplified it for testing by creating a procedure with just one OUT argument and no IN argument. I found in SQL*Plus I could execute the following commands to retrieve the data and return it as a recordset:
    Code (Text):
    var pcode varchar2(50);
    exec p_get_data(:pcode);
    select :pcode as prodcode from dual;
     
    I found I had to put it in a begin/end statement block or I'd get the error: "ORA-00900 invalid SQL Statement". However, if I execute the statement block from my app or query tool, I still get syntax errors with the variable declarations like:

    Code (Text):
    ORA-06550: line 2, column 5:
    PLS-00103: Encountered the symbol "PCODE" when expecting one of the following:
     := . ( @ % ;
    I've used begin/end blocks for multiple insert statements in my app so I know it works with multiple statements. The app's documentation shows executing stored procedures in SQL Server like: "EXEC sp_helpdb" so I should be able to execute them in Oracle, but there's no documentation on using arguments. Is there another way to do this in Oracle SQL or different syntax to use? I don't know if I could get the customer to modify their stored procedure, although I might be able to get them to add a view or something in Oracle that would format the data the way I need.

    I found the thread "Creating procedure and get returned data" on this site that helped me get this far but I haven't found this specific issue when I searched this forum or googled it. Maybe I just don't know the right search terms to use since I don't know much about Oracle.

    Thanks!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO