I have a proc in my package body:
create or replace package body MYPACKAGE is
procedure "GetAllRules"(p_rules out sys_refcursor)
is
begin
open p_rules for
select * from my_rules;
end "GetAllRules";
-- etc
And I'm exposing this in my package spec.
How do I execute this procedure in a new SQL Window in PL SQL Developer (or similar)?
You can execute the procedure relatively easily
DECLARE
l_rc sys_refcursor;
BEGIN
mypackage."GetAllRules"( l_rc );
END;
Of course, that simply returns the cursor to the calling application. It doesn't do anything to fetch the data from the cursor, to do something with that data, or to close the cursor. Assuming that your goal is to write some data to dbms_output (which is useful sometimes for prototyping but isn't something that production code should be relying on), you could do something like
DECLARE
l_rc sys_refcursor;
l_rec my_rules%rowtype;
BEGIN
mypackage."GetAllRules"( l_rc );
LOOP
FETCH l_rc INTO l_rec;
EXIT WHEN l_rc%NOTFOUND;
dbms_output.put_line( <<print data from l_rec>> );
END LOOP;
CLOSE l_rc;
END;
If you're really doing something like this with the cursor in PL/SQL, I'd strongly suggest returning a strongly-typed ref cursor rather than a weakly-typed one so that you can declare a record in terms of the cursor's %rowtype rather than forcing the caller to know exactly what type to declare and hoping that the query in the procedure doesn't change. This also requires you to explicitly write code to display the data which gets annoying.
If you're using SQL*Plus (or something that supports some SQL*Plus commands), you can simplify things a bit
VARIABLE rc REFCURSOR;
EXEC mypackage."GetAllRules"( :rc );
PRINT :rc;
As an aside, I'm not a fan of using case-sensitive identifiers. It gets very old to have to surround identifiers like "GetAllRules" with double-quotes every time you want to call it. Unless you have really compelling reasons, I'd suggest using standard case-insensitive identifiers. It's perfectly reasonable to capitalize identifiers reasonably in your code, of course, it just doesn't make a lot of sense to go to the effort of forcing them to be case-sensitive in the data dictionary.
