Package with package level cursor variable

    
SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6)
 10  );

Table created.

SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1004,  'Kyte', 'tkyte@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1008, 'Oracle', 'oracle1@g.com', SYSDATE, 'DBA', 20000, 4, 1006);

1 row created.

SQL>
SQL> create or replace package people as
  2    type employees_ref_cursor_type is ref cursor return employees%rowtype;
  3
  4    function get_employee( p_employee_id in number )
  5      return employees_ref_cursor_type;
  6
  7    function get_employees( p_search in varchar2 )
  8      return employees_ref_cursor_type;
  9
 10  end people;
 11  /

Package created.

SQL> show error
No errors.
SQL>
SQL> create or replace package body people as
  2    function get_employee( p_employee_id in number )
  3      return employees_ref_cursor_type
  4    is
  5      l_emp employees_ref_cursor_type;
  6    begin
  7      open l_emp for
  8        select * from employees where employee_id = p_employee_id;
  9      return l_emp;
 10    end get_employee;
 11
 12    function get_employees( p_search in varchar2 )
 13      return employees_ref_cursor_type
 14    is
 15      l_emps employees_ref_cursor_type;
 16    begin
 17      open l_emps for
 18        select * from employees
 19         order by employee_id;
 20      return l_emps;
 21    end get_employees;
 22  end people;
 23  /

Package body created.

SQL> show error
No errors.
SQL>
SQL> drop table employees;

Table dropped.

SQL>
SQL> --

Related examples in the same category

1.	Create a package declaration and create its body after
2.	Create a package level variable
3.	call utitilities package
4.	Package for output employee table and log message
5.	Overloading Packaged Subprograms
6.	A PL/SQL package with two methods
7.	Package level Exception
8.	This package will not compile because the body does not match the specification.
9.	dependencies between an anonymous calling block and package runtime state.
10.	RESTRICT_REFERENCES pragma.
11.	Without RESTRICT_REFERENCES pragma.
12.	Persistance of packaged variables.
13.	A package with one method
14.	Package level variable as global level variable
15.	Global definition
16.	Student fetch package
17.	Cursor operation between package functions
18.	The pragma is not valid at the package level.
19.	Create package and member cursor
20.	Nested package reference
21.	Overloaded packages.
22.	Overloading based on user defined object types.
23.	package RECURSION
24.	Crosss reference between two packages
25.	Counter package
26.	Package initialization.
27.	Packages allows forward references, thus opening the possibilities for recursion.
28.	Use package level type as global variables
29.	Use package member variable to pass value

Package with package level cursor variable : Package « PL SQL « Oracle PL / SQL

Related examples in the same category