CODEWITHSUNDEEP
oracle-databasecompilationcompiler-errorspackage
Dave T.
Dave T.

Reputation: 1

Successfully compiled Oracle package fails when called with 'ORA-04063: package body "..." has errors

I have a pkg that I use to keep report oriented code CMS_REPORTS.

I added a procedure to return a ref cursor and the pkg compiles fine, but fails when I call the proc to test it with: ORA-04063: package body "CMS.CMS_REPORTS" has errors ORA-06508: PL/SQL: could not find program unit being called: "CMS.CMS_REPORTS"

I've removed the orig proc and replaced it with this to keep things simple - same problem.

The proc is this:

procedure test_ref_cur(p_testno    in  number,
                       p_cur       in out ref_cur) as 
begin 

  open p_cur for
    select p_testno + 1 from dual;

end test_ref_cur;

I have defined the ref cursor in the pkg spec like this: 

 type ref_cur is ref cursor;
  procedure test_ref_cur(p_testno    in  number,
                         p_cur       in out ref_cur);

I've tried all sorts of combinations of using ref cursor and sys_refcursor and all bring up the same error. If I remove the proc from the pkg, it works fine.

I'm beginning to think it's a system issue?

Has anyone else had this problem?

Regards Dave

Upvotes: 0

Views: 2942

Answers (2)

the_slk
the_slk

Reputation: 2182

In order to achieve what you want you have to use SYS_REFCURSOR:

create procedure test_ref_cur(p_testno    in  number,
                       p_cur       in out SYS_REFCURSOR) as 
begin 

  open p_cur for
    select p_testno + 1 from dual;

end test_ref_cur;
-- PROCEDURE TEST_REF_CUR compiled

... and example:

DECLARE
    l_sysrc SYS_REFCURSOR;
    l_num   NUMBER;

    procedure test_ref_cur(p_testno    in  number,
                           p_cur       in out SYS_REFCURSOR) as 
    begin 

      open p_cur for
        select p_testno + 1 from dual;

    end test_ref_cur;
BEGIN
    test_ref_cur(1, l_sysrc);

    FETCH l_sysrc INTO l_num;

    DBMS_OUTPUT.PUT_LINE(l_num);
END;
-- Result:
-- 2

Since Oracle 7.3 the REF CURSOR type has been available to allow recordsets to be returned from stored procedures and functions. Oracle 9i introduced the predefined SYS_REFCURSOR type, meaning we no longer have to define our own REF CURSOR types.

Source: http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81998

Hard to tell what is the issue here, since it doesn't look like we are seeing the relevant code.

So here are some thing I recommend to double check:

  • package and package body are there and are actually compiled without an exception

  • you are in the schema/user that contains package and package body.

  • There are no other objects with the same name, that might hide your package/package body

  • the procedure you try to call is present in package and package body.

  • remove all code from package + package body except a single trivial procedure and check if that works.

If you've done all that update the question with the results.

Upvotes: 1

Related Questions