-- pkg_foo.sql

CREATE OR REPLACE PACKAGE pkg_foo
AS
  FUNCTION func_insert_foo
  (
        lnum1         in  NUMBER,
        lnum2         in  NUMBER,
        lnum3         in  NUMBER,
        lnum4         in  NUMBER
  ) RETURN NUMBER;

  PROCEDURE proc_get_foo
  (
        pi_foo_id     in  NUMBER,
        pio_foo       out SYS_REFCURSOR
  );

END pkg_foo;
/

CREATE OR REPLACE PACKAGE BODY pkg_foo
AS

  FUNCTION func_insert_foo
  (
        lnum1         in  NUMBER,
        lnum2         in  NUMBER,
        lnum3         in  NUMBER,
        lnum4         in  NUMBER
  ) RETURN NUMBER AS
     my_foo_id           NUMBER;
   BEGIN

     SELECT FOO_ID_SEQ.nextval
       INTO my_foo_id
       FROM dual;

     INSERT INTO foo VALUES
     (
        my_foo_id,
        lnum1,
        lnum2,
        lnum3,
        lnum4
     );

     RETURN my_foo_id;

   END func_insert_foo;

   PROCEDURE proc_get_foo
   (
         pi_foo_id         in  NUMBER,
         pio_foo           out SYS_REFCURSOR
   ) IS
   BEGIN

    OPEN pio_foo FOR
       SELECT
        foo_id,
        lnum1,
        lnum2,
        lnum3,
        lnum4
       FROM foo
      WHERE foo_id = pi_foo_id;

  END proc_get_foo;

END pkg_foo;
/