Skip to Main Content
  • Questions

  • Need to have a package that contains a procedure with an OUT SYS_REFCUSOR variable to work with .Net application

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 02, 2015 - 9:37 am UTC

Last updated: September 15, 2015 - 2:20 am UTC

Version: Oracle Database: 11.2.0.1

Viewed 1000+ times

You Asked

Hi,

I have been searching and searching to find the answer to this question, so I'm very sorry if it's been asked before.

I am trying to have a C# host application get information from my database using a refcursor and a package.

Here is the syntax I have so far: 
CREATE OR REPLACE PACKAGE GET_ALL_PACK AS
 RetCusor SYS_REFCURSOR;
 PROCEDURE GET_ALLCUST(AllCust OUT SYS_REFCURSOR);
END GET_ALL_PACK;
/
CREATE OR REPLACE PACKAGE BODY GET_ALL_PACK AS
 RetCusor SYS_REFCURSOR;
 PROCEDURE GET_ALLCUST(AllCust OUT SYS_REFCURSOR) IS
 begin
  OPEN RetCusor FOR
   SELECT * FROM Customer;
 end;
END GET_ALL_PACK;


But this is giving me errors, and I am unsure how to fix it, thanks for any help.

and Chris said...

Please provide the errors you're getting when asking questions - it makes it much easier for us to answer!

In this case, when I compile your package I get:

PLS-00994: Cursor Variables cannot be declared as part of a package


This gives a valuable clue - you can't specify the variable RetCursor at the package level. It must be within a procedure. So move the declaration of RetCursor inside the body of get_allcust procedure:

CREATE OR REPLACE PACKAGE GET_ALL_PACK AS
    PROCEDURE GET_ALLCUST(AllCust OUT SYS_REFCURSOR);
END GET_ALL_PACK;
/ 
show err

No errors.

CREATE OR REPLACE PACKAGE BODY GET_ALL_PACK AS
    PROCEDURE GET_ALLCUST(AllCust OUT SYS_REFCURSOR) IS
      RetCusor SYS_REFCURSOR;
    begin
        OPEN RetCusor FOR
            SELECT * FROM Customer;
    end;
END GET_ALL_PACK;
/ 
show err

No errors.

Rating

  (1 rating)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

RetCusor won't return anything to C#

Kim Berg Hansen, September 02, 2015 - 10:23 am UTC

The declared RetCusor won't return anything to the C# calling code.

But the OUT parameter AllCust can return something to C#:

CREATE OR REPLACE PACKAGE GET_ALL_PACK AS
    PROCEDURE GET_ALLCUST(AllCust OUT SYS_REFCURSOR);
END GET_ALL_PACK;
/ 

CREATE OR REPLACE PACKAGE BODY GET_ALL_PACK AS
    PROCEDURE GET_ALLCUST(AllCust OUT SYS_REFCURSOR) IS
    begin
        OPEN AllCust FOR
            SELECT * FROM Customer;
    end GET_ALLCUST;
END GET_ALL_PACK;
/


This method we use extensively in our e-commerce web application ;-)
Chris Saxon
September 02, 2015 - 10:37 am UTC

Yep, you are corect Kim! Thanks for the correction.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library