PL/SQL Packages and Procedures

Question

I'm trying to use a procedure within a package so that I can call in in an external program. I keep getting a encountered the symbol "CREATE" error. I've tried a lot of different ways of doing it so I'm sorry if my code is messy, I'm really lost with this one

create or replace package GetAllCust as
function GeTAllCust return sys_refcursor;

procedure GET_ALLCUST_FROM_DB(pSysRefCur OUT sys_refcursor);
end GetAllCust;

create or replace package body GetAllCust as
procedure GET_ALLCUST_FROM_DB (pSysRefCur OUT sys_refcursor) as
begin
    open pSysRefCur for select * from customer;
    return pSysRefCur;
exception
    when others then
        raise_application_error(-20000, SQLERRM);
end GET_ALLCUST_FROM_DB;
 end GetAllCust;

Answer 1

You have defined function GeTAllCust in package specification, however in body it is not specified. And another error was return statement in procedure body which is not required.

Try creating your package and package body as

CREATE OR REPLACE PACKAGE GetAllCust
AS
   FUNCTION GeTAllCust
      RETURN SYS_REFCURSOR;

   PROCEDURE GET_ALLCUST_FROM_DB (pSysRefCur OUT SYS_REFCURSOR);
END GetAllCust;
/

CREATE OR REPLACE PACKAGE BODY GetAllCust
AS
   FUNCTION GeTAllCust
      RETURN SYS_REFCURSOR
   AS
      p_cursor   SYS_REFCURSOR;
   BEGIN
      OPEN p_cursor FOR SELECT * FROM customer;

      RETURN p_cursor;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         raise_application_error (-20001, SQLERRM);
   END GeTAllCust;

   PROCEDURE GET_ALLCUST_FROM_DB (pSysRefCur OUT SYS_REFCURSOR)
   AS
   BEGIN
      OPEN pSysRefCur FOR SELECT * FROM customer;
   EXCEPTION
      WHEN OTHERS
      THEN
         raise_application_error (-20000, SQLERRM);
   END GET_ALLCUST_FROM_DB;
END GetAllCust;
/