create or replace package xx_extract
cursor v_cursor
is 
select id,number
from tablename;

v_cursor_rec  v_cursor%rowtype;


v_in_number,xxc_all.in_number%TYPE;
v_in_date   xxc_all.in_date%TYPE;
 

Begin
   procedure xxc_process is
    Begin
        xxc_get(v_cursor_rec.id,v_cursor_rec.number);

         open v_cursor;
       loop
         fetch v_cursor into v_cursor_rec;
         exit when v_cursor%notfound;
       end loop;
    close v_cursor;
   end xxc_process;

   procedure xxc_get(in_id     tablename.id%type,
                     in_number tablename.number%type)
   is
    begin
     select in_number,in_date
     into v_in_number,v_in_date
     from xxc_all
     where column1=to_char(in_id)
     and column2=to_char(in_number);
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
                  in_id := NULL;
                  in_number := NULL;
   end xxc_get;
end;

select in_number,in_date
     into v_in_number,v_in_date
     from xxc_all
     where column1=to_char(in_id)
     and column2=to_char(in_number);
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
                  in_id := NULL;
                  in_number := NULL;

Wrote file afiedt.buf

  1  create or replace package xx_extract
  2  as
  3  procedure xxc_process;
  4  procedure xxc_get(in_id     emp.empno%type,
  5                       in_number emp.deptno%type);
  6* end ;
SQL> /

Package created.

 1  create or replace package xx_extract
 2  as
 3  cursor v_cursor
 4  is
 5  select empno,deptno
 6  from emp a,dept d
 7  where e.deptno=d.deptno;
 8  v_cursor_rec  v_cursor%rowtype;
 9  v_in_number emp.empno%type;
10  v_in_date   emp.hiredate%type;
11     procedure xxc_process is
12      Begin
13          xxc_get(v_cursor_rec.empno,v_cursor_rec.deptno);
14           open v_cursor;
15         loop
16           fetch v_cursor into v_cursor_rec;
17           exit when v_cursor%notfound;
18         end loop;
19      close v_cursor;
20     end xxc_process;
21     procedure xxc_get(in_id     emp.empno%type,
22                       in_number emp.deptno%type)
23     is
24      begin
25       select empno,hiredate
26       into v_in_number,v_in_date
27       from emp
28       where empno=to_char(in_id)
29       and deptno=to_char(in_number);
30        EXCEPTION
31            WHEN NO_DATA_FOUND THEN
32                    in_id := NULL;
33                    in_number := NULL;
34     end xxc_get;
35* end;
36  /

arning: Package created with compilation errors.

QL> show error
rrors for PACKAGE XX_EXTRACT:

INE/COL ERROR
------- -----------------------------------------------------------------
2/5     PLS-00103: Encountered the symbol "BEGIN" when expecting one of
        the following:
        language

1/4     PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
        of the following:
        end not pragma final instantiable order overriding static
        member constructor map
        The symbol "static" was substituted for "PROCEDURE" to continue.

CREATE OR REPLACE PACKAGE body xx_extract
AS
   CURSOR v_cursor
   IS
      SELECT empno, a.deptno,a.hiredate
        FROM emp a, dept d
       WHERE a.deptno = d.deptno;

   v_cursor_rec   v_cursor%ROWTYPE;
   v_in_number    emp.empno%TYPE;
   v_in_date      emp.hiredate%TYPE;

   PROCEDURE xxc_process
   IS
   BEGIN

      OPEN v_cursor;

      LOOP
         FETCH v_cursor
          INTO v_cursor_rec;

         EXIT WHEN v_cursor%NOTFOUND;
         xxc_get (v_cursor_rec.empno, v_cursor_rec.deptno);
      END LOOP;

      CLOSE v_cursor;
   END xxc_process;

   PROCEDURE xxc_get (in_id in emp.empno%TYPE, in_number in  emp.deptno%TYPE)
   IS
   BEGIN
      SELECT empno, hiredate
        INTO v_in_number, v_in_date
        FROM emp
       WHERE empno =in_id
       AND deptno = in_number;
       dbms_output.put_line('Number'||v_in_number||'   '||'Date'||v_in_date);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_in_number := NULL;
         v_in_date := NULL;
   END xxc_get;
END xx_extract;

SQL> set serveroutput on
SQL> set line 10000
SQL> CREATE OR REPLACE PACKAGE body xx_extract
  2  AS
  3     CURSOR v_cursor
  4     IS
  5        SELECT empno, a.deptno,a.hiredate
  6          FROM emp a, dept d
  7         WHERE a.deptno = d.deptno;
  8  
  9     v_cursor_rec   v_cursor%ROWTYPE;
 10     v_in_number    emp.empno%TYPE;
 11     v_in_date      emp.hiredate%TYPE;
 12  
 13     PROCEDURE xxc_process
 14     IS
 15     BEGIN
 16  
 17        OPEN v_cursor;
 18  
 19        LOOP
 20           FETCH v_cursor
 21            INTO v_cursor_rec;
 22  
 23           EXIT WHEN v_cursor%NOTFOUND;
 24           xxc_get (v_cursor_rec.empno, v_cursor_rec.deptno);
 25        END LOOP;
 26  
 27        CLOSE v_cursor;
 28     END xxc_process;
 29  
 30     PROCEDURE xxc_get (in_id in emp.empno%TYPE, in_number in  emp.deptno%TYPE)
 31     IS
 32     BEGIN
 33        SELECT empno, hiredate
 34          INTO v_in_number, v_in_date
 35          FROM emp
 36         WHERE empno =in_id
 37         AND deptno = in_number;
 38         dbms_output.put_line('Number'||v_in_number||'   '||'Date'||v_in_date);
 39     EXCEPTION
 40        WHEN NO_DATA_FOUND
 41        THEN
 42           v_in_number := NULL;
 43           v_in_date := NULL;
 44     END xxc_get;
 45  END xx_extract;
 46  /

Package body created.