Howdy, Stranger!

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

Categories

return resultset with stored procedure

jeffreyhambyjeffreyhamby Member Posts: 175
I suppose I've been spoiled by using SQL Server for so long now. I've got a really slow connection to a data warehouse that lives offsite (new company that shopped out most of it's work initially). So I want to write SP's to query faster and return data. in Sybase and Microsoft's SQL Server, you can simply but the query in an SP construct and everything is fine. I understand you need a cursor, or some sort of return variable, to do the same with Oracle (8i, if that's important).

Here is an example given to me which, unfortunately, doesn't work. However, it at least contains a simple query that does work on it's own. Can someone help me with the syntax I need to return the resultset?

note: the package compiles with no errors, but the SP doesn't
[code]
CREATE OR REPLACE PACKAGE types
AS
TYPE ref_cursor IS REF CURSOR;
END;

CREATE OR REPLACE PROCEDURE sptest
RETURN types.ref_cursor
AS
types.ref_cursor IS REF CURSOR;
BEGIN
OPEN types.ref_cursor FOR
SELECT ES.EMPLOYEE_NUMBER, ES.NAME
FROM CPC1.EMPLOYEE_SNAPSHOT_CPC ES;

RETURN types.ref_cursor;
END;
[/code]

Comments

  • infidelinfidel Member Posts: 2,900
    : I suppose I've been spoiled by using SQL Server for so long now. I've got a really slow connection to a data warehouse that lives offsite (new company that shopped out most of it's work initially). So I want to write SP's to query faster and return data. in Sybase and Microsoft's SQL Server, you can simply but the query in an SP construct and everything is fine. I understand you need a cursor, or some sort of return variable, to do the same with Oracle (8i, if that's important).
    :
    : Here is an example given to me which, unfortunately, doesn't work. However, it at least contains a simple query that does work on it's own. Can someone help me with the syntax I need to return the resultset?
    :
    : note: the package compiles with no errors, but the SP doesn't
    : [code]
    : CREATE OR REPLACE PACKAGE types
    : AS
    : TYPE ref_cursor IS REF CURSOR;
    : END;
    :
    : CREATE OR REPLACE PROCEDURE sptest
    : RETURN types.ref_cursor
    : AS
    : types.ref_cursor IS REF CURSOR;
    : BEGIN
    : OPEN types.ref_cursor FOR
    : SELECT ES.EMPLOYEE_NUMBER, ES.NAME
    : FROM CPC1.EMPLOYEE_SNAPSHOT_CPC ES;
    :
    : RETURN types.ref_cursor;
    : END;
    : [/code]

    You can't return a value from a procedure. Change the word "PROCEDURE" to "FUNCTION" and see if that helps.

    Also, types.ref_cursor is your type, you can't open a type. Change

    [code]types.ref_cursor IS REF CURSOR;[/code]

    to

    [code]mycursor types.ref_cursor;[/code]

    This declares a variable, mycursor, of type types.ref_cursor. Then "OPEN mycursor" and "RETURN mycursor" rather than the type.

    I've never been able to get a function to return a cursor. We use procedures that have an OUT parameter of the cursor type. Let me know if you still have problems.



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

  • jeffreyhambyjeffreyhamby Member Posts: 175
    [italic][blue]
    : I've never been able to get a function to return a cursor. We use procedures that have an OUT parameter of the cursor type. Let me know if you still have problems.
    [/blue][/italic]

    Yes, seems I still am having trouble. Could you post an example of how you would create an SP/Function that returns a resultset? I hit Oracle's site and tried the following example, which still didn't work... and I tried it as both a procedure and as a function.

    [code]
    CREATE OR REPLACE FUNCTION test
    AS

    DECLARE
    type my_curs_type is REF CURSOR; --must be weakly typed
    curs my_curs_type;
    str varchar2(100);
    ret varchar2(100);
    BEGIN
    str := 'SELECT ES.EMPLOYEE_NUMBER, ES.NAME FROM CPC1.EMPLOYEE_SNAPSHOT_CPC ES';
    --No placeholders, so no USING clause
    OPEN curs FOR str;
    loop
    FETCH curs INTO ret;
    EXIT when curs%notfound;
    dbms_output.put_line(ret);
    end loop;
    CLOSE curs;

    END;
    [/code]
  • jeffreyhambyjeffreyhamby Member Posts: 175
    [italic][blue]
    Yes, seems I still am having trouble. Could you post an example of how you would create an SP/Function that returns a resultset? I hit Oracle's site and tried the following example, which still didn't work... and I tried it as both a procedure and as a function.
    [/blue][/italic]

    I got the following version of the SP to compile, but when I try to run it I get

    [red] ORA-06576: not a function or procedure [/red]

    [code]
    CREATE OR REPLACE PROCEDURE RETURN_RESULT_SET
    (oCursor IN OUT types.ref_cursor) AS
    BEGIN
    open oCursor FOR
    SELECT ES.EMPLOYEE_NUMBER, ES.NAME
    FROM CPC1.EMPLOYEE_SNAPSHOT_CPC ES;
    END RETURN_RESULT_SET;
    [/code]


  • infidelinfidel Member Posts: 2,900
    : [italic][blue]
    : Yes, seems I still am having trouble. Could you post an example of how you would create an SP/Function that returns a resultset? I hit Oracle's site and tried the following example, which still didn't work... and I tried it as both a procedure and as a function.
    : [/blue][/italic]
    :
    : I got the following version of the SP to compile, but when I try to run it I get
    :
    : [red] ORA-06576: not a function or procedure [/red]
    :
    : [code]
    : CREATE OR REPLACE PROCEDURE RETURN_RESULT_SET
    : (oCursor IN OUT types.ref_cursor) AS
    : BEGIN
    : open oCursor FOR
    : SELECT ES.EMPLOYEE_NUMBER, ES.NAME
    : FROM CPC1.EMPLOYEE_SNAPSHOT_CPC ES;
    : END RETURN_RESULT_SET;
    : [/code]

    That's basically how we do it. Did you pass it a refcursor variable? "IN OUT" arguments are kind of like ByRef arguments in VB or "by reference" arguments in C/C++. To call your procedure, you would need to do something like this:

    DECLARE
    mycursor types.ref_cursor;
    BEGIN
    return_result_set(mycursor);
    END;


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

  • jeffreyhambyjeffreyhamby Member Posts: 175
    [italic][blue]
    That's basically how we do it. Did you pass it a refcursor variable? "IN OUT" arguments are kind of like ByRef arguments in VB or "by reference" arguments in C/C++. To call your procedure, you would need to do something like this:

    DECLARE
    mycursor types.ref_cursor;
    BEGIN
    return_result_set(mycursor);
    END;
    [/blue][/italic]

    You're the man... that works great for fetching the data into the cursor. With C++ or even VB I can parse the data from the cursor that way. However, is there an Oracle function to parse the data so I can see it in SQL*Plus or WinSQL?

    Thanks, by the way.

    Jeff
  • infidelinfidel Member Posts: 2,900
    : [italic][blue]
    : That's basically how we do it. Did you pass it a refcursor variable? "IN OUT" arguments are kind of like ByRef arguments in VB or "by reference" arguments in C/C++. To call your procedure, you would need to do something like this:
    :
    : DECLARE
    : mycursor types.ref_cursor;
    : BEGIN
    : return_result_set(mycursor);
    : END;
    : [/blue][/italic]
    :
    : You're the man... that works great for fetching the data into the cursor. With C++ or even VB I can parse the data from the cursor that way. However, is there an Oracle function to parse the data so I can see it in SQL*Plus or WinSQL?

    In sqlplus you can print a cursor variable out like:

    print mycursor

    I can't get it to work on my system at the moment, but I think that's the correct way.


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

  • infidelinfidel Member Posts: 2,900
    : : [italic][blue]
    : : That's basically how we do it. Did you pass it a refcursor variable? "IN OUT" arguments are kind of like ByRef arguments in VB or "by reference" arguments in C/C++. To call your procedure, you would need to do something like this:
    : :
    : : DECLARE
    : : mycursor types.ref_cursor;
    : : BEGIN
    : : return_result_set(mycursor);
    : : END;
    : : [/blue][/italic]
    : :
    : : You're the man... that works great for fetching the data into the cursor. With C++ or even VB I can parse the data from the cursor that way. However, is there an Oracle function to parse the data so I can see it in SQL*Plus or WinSQL?
    :
    : In sqlplus you can print a cursor variable out like:
    :
    : print mycursor
    :
    : I can't get it to work on my system at the moment, but I think that's the correct way.

    Ok, here it is. I was forgetting the colon

    SQL> variable mycursor refcursor
    SQL> execute return_result_set(:mycursor);
    SQL> print mycursor




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

  • infidelinfidel Member Posts: 2,900
    I got it to work as a function too

    [code]
    PACKAGE TYPES IS

    type refcursor is ref cursor;

    END;
    [/code]

    [code]
    FUNCTION GET_REFCURSOR RETURN types.refcursor IS
    v_cursor types.refcursor;
    BEGIN
    open v_cursor for select * from user_objects;
    return v_cursor;
    END;
    [/code]

    SQL> variable mycursor refcursor
    SQL> execute :mycursor := get_refcursor;
    SQL> print mycursor

    It all depends on that silly colon before the variable name. Weird.


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

  • jeffreyhambyjeffreyhamby Member Posts: 175
    Man, if I had a trophy handy, I'd send it your way. I've got to get into some Oracle classes if I'm going to continue to hit this stuff outside of VB/C++.

    Thanks so much,
    Jeff
Sign In or Register to comment.