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.
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.