Live ORACLE

DECLARE
wages         NUMBER;
hours_worked  NUMBER := 40;
hourly_salary  CONSTANT NUMBER := 17.50; -- constant value does not change
country       VARCHAR2(64) := 'UNKNOWN';
unknown       BOOLEAN;
TYPE comm_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
commissions   COMM_TAB;
TYPE jobs_var IS VARRAY(10) OF employees.job_id%TYPE;
jobids        JOBS_VAR;
CURSOR c1 IS
  SELECT department_id
  FROM   departments; -- cursor declaration
deptid        departments.department_id%TYPE;
emp_rec       employees%ROWTYPE; -- do not need TYPE declaration in this case

BEGIN
/* the following are examples of assignment statements */
wages := hours_worked
           * hourly_salary; -- compute wages
country := Upper('italy');
unknown := (country = 'UNKNOWN');
Commissions(5) := 20000
                    * 0.15;
Commissions(8) := 20000
                    * 0.18;
jobids := Jobs_var('ST_CLERK');
jobids.Extend(1);
Jobids(2) := 'SH_CLERK';
OPEN c1;
FETCH c1 INTO deptid;
CLOSE c1;
emp_rec.department_id := deptid;
emp_rec.job_id := Jobids(2);
END;
/  

Declaring and Assigning Values to Variables

--Cleanup ...........
DROP PACKAGE teams
/

DROP TYPE person_typ
/

DROP TYPE hobbies_var
/

-- Creating ..........
CREATE TYPE person_typ AS OBJECT
(NAME VARCHAR2(25),
age NUMBER
)
/

CREATE TYPE hobbies_var AS VARRAY(10) OF VARCHAR2(25)
/

CREATE OR REPLACE PACKAGE teams
AUTHID current_user
AS
PROCEDURE create_table(tab_name  VARCHAR2);

PROCEDURE insert_row(tab_name  VARCHAR2,p  PERSON_TYP,h  HOBBIES_VAR);

PROCEDURE print_table(tab_name  VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY teams
AS
PROCEDURE Create_table
   (tab_name  VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE '
                  ||tab_name
                  ||' (pers person_typ, hobbs hobbies_var)';
END;

PROCEDURE Insert_row
   (tab_name  VARCHAR2,
    p         PERSON_TYP,
    h         HOBBIES_VAR)
IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO '
                  ||tab_name
                  ||' VALUES (:1, :2)'
 USING p,h;
END;

PROCEDURE Print_table
   (tab_name  VARCHAR2)
IS
TYPE refcurtyp IS REF CURSOR;
v_cur  REFCURTYP;
p      PERSON_TYP;
h      HOBBIES_VAR;
BEGIN
OPEN v_cur FOR 'SELECT pers, hobbs FROM '
               ||tab_name;
LOOP
  FETCH v_cur INTO p,h;
  EXIT WHEN v_cur%NOTFOUND;
  -- print attributes of 'p' and elements of 'h'
  dbms_output.Put_line('Name: '
                       ||p.NAME
                       ||' - Age: '
                       ||p.age);
  FOR i IN h.FIRST.. h.LAST LOOP
    dbms_output.Put_line('Hobby('
                         ||i
                         ||'): '
                         ||H(i));
  END LOOP;
END LOOP;
CLOSE v_cur;
END;
END;
/

DECLARE
 team_name  VARCHAR2(15);
BEGIN
 team_name := 'Notables';
 teams.Create_table(team_name);
 teams.Insert_row(team_name,Person_typ('John',31),Hobbies_var('skiing','coin collecting','tennis'));
 teams.Insert_row(team_name,Person_typ('Mary',28),Hobbies_var('golf','quilting','rock climbing','fencing'));
 teams.Print_table(team_name);
END;
/  

TEAMS Package Using Dynamic SQL for Object Types and Collections

-- Cleanup before Creating
DROP TABLE depts CASCADE CONSTRAINTS PURGE
/

DROP TYPE dnames_var
/

-- Creating ........
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30)
/

CREATE TABLE depts (
 region     VARCHAR2(25),
 dept_names DNAMES_VAR)
/

BEGIN
 INSERT INTO depts
 VALUES     ('Europe',
             Dnames_var('Shipping','Sales','Finance'));
 INSERT INTO depts
 VALUES     ('Americas',
             Dnames_var('Sales','Finance','Shipping'));
 INSERT INTO depts
 VALUES     ('Asia',
             Dnames_var('Finance','Payroll','Shipping','Sales'));
 COMMIT;
END;
/

DECLARE
 TYPE dnames_tab IS TABLE OF DNAMES_VAR;
 v_depts  DNAMES_TAB;
BEGIN
 SELECT dept_names
 BULK COLLECT INTO v_depts
 FROM   depts;
 dbms_output.Put_line(v_depts.COUNT); -- prints 3

END;
/ 

Using BULK COLLECT with Nested Tables

DROP TABLE depts CASCADE CONSTRAINTS PURGE
/

DROP TYPE dnames_var
/

-- By using a varray, we put an upper limit on the number of elements
-- and ensure they always come back in the same order
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/

CREATE TABLE depts (
region     VARCHAR2(25),
dept_names DNAMES_VAR);

BEGIN
INSERT INTO depts
VALUES     ('Europe',
           Dnames_var('Shipping','Sales','Finance'));
INSERT INTO depts
VALUES     ('Americas',
           Dnames_var('Sales','Finance','Shipping'));
INSERT INTO depts
VALUES     ('Asia',
           Dnames_var('Finance','Payroll','Shipping','Sales'));
COMMIT;
END;
/

DECLARE
new_dnames   DNAMES_VAR := Dnames_var('Benefits','Advertising','Contracting','Executive',
                                     'Marketing');
some_dnames  DNAMES_VAR;
BEGIN
UPDATE depts
SET    dept_names = new_dnames
WHERE  region = 'Europe';
COMMIT;
SELECT dept_names
INTO   some_dnames
FROM   depts
WHERE  region = 'Europe';
FOR i IN some_dnames.FIRST.. some_dnames.LAST LOOP
 dbms_output.Put_line('dept_names = '
                      ||Some_dnames(i));
END LOOP;
END;
/  

Using INSERT, UPDATE, DELETE, and SELECT Statements With Varrays

CREATE TYPE dnames_tab AS TABLE OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_tab)
NESTED TABLE dept_names STORE AS dnames_nt;
BEGIN
INSERT INTO depts VALUES('Europe', dnames_tab('Shipping','Sales','Finance'));
INSERT INTO depts VALUES('Americas', dnames_tab('Sales','Finance','Shipping'));
INSERT INTO depts VALUES('Asia', dnames_tab('Finance','Payroll'));
COMMIT;
END;
/
DECLARE
-- Type declaration is not needed, because PL/SQL can access the SQL object type
-- TYPE dnames_tab IS TABLE OF VARCHAR2(30); not needed
-- Declare a variable that can hold a set of department names
v_dnames dnames_tab;
-- Declare a record that can hold a row from the table
-- One of the record fields is a set of department names
v_depts depts%ROWTYPE;
new_dnames dnames_tab;
BEGIN
-- Look up a region and query just the associated department names
SELECT dept_names INTO v_dnames FROM depts WHERE region = 'Europe';
FOR i IN v_dnames.FIRST .. v_dnames.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Department names: ' || v_dnames(i));
END LOOP;
-- Look up a region and query the entire row
SELECT * INTO v_depts FROM depts WHERE region = 'Asia';
-- Now dept_names is a field in a record, so we access it with dot notation
FOR i IN v_depts.dept_names.FIRST .. v_depts.dept_names.LAST LOOP
-- Because we have all the table columns in the record, we can refer to region
DBMS_OUTPUT.PUT_LINE(v_depts.region || ' dept_names = ' ||
v_depts.dept_names(i));
END LOOP;
-- We can replace a set of department names with a new collection
-- in an UPDATE statement
new_dnames := dnames_tab('Sales','Payroll','Shipping');
UPDATE depts SET dept_names = new_dnames WHERE region = 'Europe';
-- Or we can modify the original collection and use it in the UPDATE.
-- We'll add a new final element and fill in a value
v_depts.dept_names.EXTEND(1);
v_depts.dept_names(v_depts.dept_names.COUNT) := 'Finance';
UPDATE depts SET dept_names = v_depts.dept_names
WHERE region = v_depts.region;
-- We can even treat the nested table column like a real table and
-- insert, update, or delete elements. The TABLE operator makes the statement
-- apply to the nested table produced by the subquery.
INSERT INTO TABLE(SELECT dept_names FROM depts WHERE region = 'Asia')
VALUES('Sales');
DELETE FROM TABLE(SELECT dept_names FROM depts WHERE region = 'Asia')
WHERE column_value = 'Payroll';
UPDATE TABLE(SELECT dept_names FROM depts WHERE region = 'Americas')
SET column_value = 'Payroll' WHERE column_value = 'Finance';
COMMIT;
END;
/

Using INSERT, UPDATE, DELETE, and SELECT Statements With Nested Tables

DROP TABLE department CASCADE CONSTRAINTS PURGE
/

DROP TYPE CourseList
/

DROP TYPE Course
/

CREATE TYPE Course AS OBJECT
(course_no NUMBER,
title VARCHAR2(64),
credits NUMBER);
/

CREATE TYPE CourseList AS TABLE OF course;
/

CREATE TABLE department (
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList)
NESTED TABLE courses STORE AS courses_tab
/

INSERT INTO department VALUES ('English', 'June Johnson', '491C',
CourseList(
Course(1002, 'Expository Writing', 4),
Course(2020, 'Film and Literature', 4),
Course(4210, '20th-Century Poetry', 4),
Course(4725, 'Advanced Workshop in Poetry', 4)))
/

DECLARE
revised CourseList :=
CourseList(Course(1002, 'Expository Writing', 3),
Course(2020, 'Film and Literature', 4),
Course(4210, '20th-Century Poetry', 4),
Course(4725, 'Advanced Workshop in Poetry', 5));
num_changed INTEGER;
BEGIN
SELECT COUNT(*) INTO num_changed
FROM TABLE(CAST(revised AS CourseList)) new,
TABLE(SELECT courses FROM department
WHERE name = 'English') old
WHERE new.course_no = old.course_no AND
(new.title != old.title OR new.credits != old.credits);
DBMS_OUTPUT.PUT_LINE(num_changed);
END;
/

Performing Operations on PL/SQL Nested Tables With CAST

20:46:31 SQL> DROP TABLE sophomores CASCADE CONSTRAINTS PURGE
20:46:32   2  /
DROP TABLE sophomores CASCADE CONSTRAINTS PURGE
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


Elapsed: 00:00:00.01
20:46:32 SQL> DROP TYPE student
20:46:32   2  /
DROP TYPE student
*
ERROR at line 1:
ORA-04043: object STUDENT does not exist 


Elapsed: 00:00:00.00
20:46:32 SQL> DROP TYPE CourseList
20:46:32   2  /
DROP TYPE CourseList
*
ERROR at line 1:
ORA-04043: object COURSELIST does not exist 


Elapsed: 00:00:00.00
20:46:32 SQL> CREATE TYPE CourseList AS TABLE OF VARCHAR2(10)
20:46:32   2  /

Type created.

Elapsed: 00:00:00.03
20:46:32 SQL> CREATE TYPE student AS OBJECT (
20:46:32   2  id_num INTEGER(4),
20:46:32   3  name VARCHAR2(25),
20:46:32   4  address VARCHAR2(35),
20:46:32   5  status CHAR(2),
20:46:32   6  courses CourseList)
20:46:32   7  /

Type created.

Elapsed: 00:00:00.06
20:46:32 SQL> CREATE TABLE sophomores of student NESTED TABLE courses STORE AS courses_nt
20:46:32   2  /

Table created.

Elapsed: 00:00:00.04
20:46:32 SQL> BEGIN
20:46:32   2  INSERT INTO sophomores
20:46:32   3  VALUES (5035, 'Janet Alvarez', '122 Broad St', 'FT',
20:46:32   4  CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100'));
20:46:32   5  END;
20:46:32   6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
20:46:33 SQL> SPOOL OFF

Nested Table Constructor Within a SQL Statement

DROP TABLE dept_projects CASCADE CONSTRAINTS PURGE;
DROP type projectlist;

-- Each project has a 16-character code name.
-- We will store up to 50 projects at a time in a database column.
CREATE TYPE projectlist AS VARRAY(50) OF VARCHAR2(16);
/

CREATE TABLE dept_projects ( -- create database table
dept_id  NUMBER(2),
NAME     VARCHAR2(15),
budget   NUMBER(11,2),
-- Each department can have up to 50 projects.
projects PROJECTLIST); 

 BEGIN
 INSERT INTO dept_projects
 VALUES     (60,
             'Security',
             750400,
             Projectlist('New Badges','Track Computers','Check Exits'));
END;
/  

Creating a Table with a Varray Column

DROP TABLE sophomores CASCADE CONSTRAINTS PURGE;
DROP TYPE student;
DROP TYPE CourseList;
-------------------------------------------
CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type
/
CREATE TYPE student AS OBJECT ( -- create object
id_num INTEGER(4),
name VARCHAR2(25),
address VARCHAR2(35),
status CHAR(2),
courses CourseList); -- declare nested table as attribute
/
CREATE TABLE sophomores of student
NESTED TABLE courses STORE AS courses_nt
/

20:40:43 SQL> DROP TABLE sophomores CASCADE CONSTRAINTS PURGE;

Table dropped.

Elapsed: 00:00:00.04
20:40:44 SQL> DROP TYPE student;

Type dropped.

Elapsed: 00:00:00.09
20:40:44 SQL> DROP TYPE CourseList;

Type dropped.

Elapsed: 00:00:00.06

20:40:44 SQL> CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type
20:40:44   2  /

Type created.

Elapsed: 00:00:00.01
20:40:44 SQL> CREATE TYPE student AS OBJECT ( -- create object
20:40:44   2  id_num INTEGER(4),
20:40:44   3  name VARCHAR2(25),
20:40:44   4  address VARCHAR2(35),
20:40:44   5  status CHAR(2),
20:40:44   6  courses CourseList); -- declare nested table as attribute
20:40:44   7  /

Type created.

Elapsed: 00:00:00.06
20:40:44 SQL> CREATE TABLE sophomores of student
20:40:44   2  NESTED TABLE courses STORE AS courses_nt
20:40:44   3  /

Table created.

Elapsed: 00:00:00.03

Declaring a Nested Table in SQL

DECLARE
 emp       EMPLOYEE_TYP;
 emp_ref   REF EMPLOYEE_TYP;
 emp_name  VARCHAR2(50);
BEGIN
 SELECT Ref(e)
 INTO   emp_ref
 FROM   employee_tab e
 WHERE  e.employee_id = 370;
 -- the following assignment raises an error, not allowed in PL/SQL
 -- emp_name := emp_ref.first_name || ' ' || emp_ref.last_name;
 -- emp := DEREF(emp_ref); not allowed, cannot use DEREF in procedural statements
 SELECT Deref(emp_ref)
 INTO   emp
 FROM   dual; -- use dummy table DUAL
 emp_name := emp.first_name
             ||' '
             ||emp.last_name;
 dbms_output.Put_line(emp_name);
END;
/  

Using DEREF in a SELECT INTO Statement

DECLARE
 emp      EMPLOYEE_TYP;
 emp_ref  REF EMPLOYEE_TYP;
BEGIN
 SELECT Ref(e)
 INTO   emp_ref
 FROM   employee_tab e
 WHERE  e.employee_id = 370;
 UPDATE employee_tab e
 SET    e.address = Address_typ('8701 College','Oakland','CA','94321')
 WHERE  Ref(e) = emp_ref;
END;
/  

Updating Rows in an Object Table With a REF Modifier

DECLARE
 emp  EMPLOYEE_TYP;
BEGIN
 INSERT INTO employee_tab
 VALUES     (Employee_typ(370,'Robert','Myers','RMYERS','555.111.2277',
                          '07-NOV-04','SA_REP',8800,.12,101,110,Address_typ('540 Fillmore','San Francisco','CA','94011')));
 UPDATE employee_tab e
 SET    e.address.street = '1040 California'
 WHERE  e.employee_id = 370;
 DELETE FROM employee_tab e
 WHERE       e.employee_id = 310;
END;
/  

Updating and Deleting Rows in an Object Table

DECLARE
 emp  EMPLOYEE_TYP;
BEGIN
 INSERT INTO employee_tab
 VALUES     (Employee_typ(310,'Evers','Boston','EBOSTON','555.111.2222',
                          '01-AUG-04','SA_REP',9000,.15,101,110,Address_typ('123 Main','San Francisco','CA','94111')));
 INSERT INTO employee_tab
 VALUES     (Employee_typ(320,'Martha','Dunn','MDUNN','555.111.3333',
                          '30-SEP-04','AC_MGR',12500,0,101,110,Address_typ('123 Broadway','Redwood City','CA','94065')));
END;
/  

Inserting Rows in an Object Table

DECLARE
 emp  EMPLOYEE_TYP;
BEGIN
 emp := Employee_typ(315,'Francis','Logan','FLOGAN','555.777.2222',
                     '01-MAY-04','SA_MAN',11000,.15,101,110,Address_typ('376 Mission','San Francisco','CA','94222'));
 dbms_output.Put_line(emp.first_name
                      ||' '
                      ||emp.last_name);
 dbms_output.Put_line(emp.address.street);
 dbms_output.Put_line(emp.address.city
                      ||', '
                      ||emp.address.state
                      ||' '
                      ||emp.address.postal_code);
END;
/  

Francis Logan
376 Mission
San Francisco, CA 94222

PL/SQL procedure successfully completed.

Accessing Object Attributes

DECLARE
 emp  EMPLOYEE_TYP; -- emp is atomically null

BEGIN
 IF emp IS NULL THEN
   dbms_output.Put_line('emp is NULL #1');
 END IF;
 IF emp.employee_id IS NULL THEN
   dbms_output.Put_line('emp.employee_id is NULL #1');
 END IF;
 emp.employee_id := 330;
 IF emp IS NULL THEN
   dbms_output.Put_line('emp is NULL #2');
 END IF;
 IF emp.employee_id IS NULL THEN
   dbms_output.Put_line('emp.employee_id is NULL #2');
 END IF;
 emp := Employee_typ(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
                     NULL,NULL,Address_typ(NULL,NULL,NULL,NULL));
 -- emp := NULL; -- this would have made the following IF statement TRUE
 IF emp IS NULL THEN
   dbms_output.Put_line('emp is NULL #3');
 END IF;
 IF emp.employee_id IS NULL THEN
   dbms_output.Put_line('emp.employee_id is NULL #3');
 END IF;
EXCEPTION
 WHEN access_into_null THEN
   dbms_output.Put_line('Cannot assign value to NULL object');
END;
/  

emp is NULL #1
emp.employee_id is NULL #1
emp is NULL #2
emp.employee_id is NULL #3

PL/SQL procedure successfully completed.

Null Objects in a PL/SQL Block

DECLARE

 emp  EMPLOYEE_TYP; -- emp is atomically null



BEGIN

 -- call the constructor for employee_typ

 emp := Employee_typ(315,'Francis','Logan','FLOGAN','555.777.2222',

                     '01-MAY-04','SA_MAN',11000,.15,101,110,Address_typ('376 Mission','San Francisco','CA','94222'));

 dbms_output.Put_line(emp.first_name

                      ||' '

                      ||emp.last_name); -- display details

 emp.Display_address(); -- call object method to display details



END;

/

Francis Logan
Francis Logan
376 Mission
San Francisco, CA 94222

PL/SQL procedure successfully completed.

Declaring Object Types in a PL/SQL Block

DROP TYPE address_typ;

CREATE TYPE address_typ AS OBJECT
 (street VARCHAR2(30),
  city VARCHAR2(20),
  state CHAR(2),
  postal_code VARCHAR2(6)
 );
/

DROP TYPE employee_typ;

CREATE TYPE employee_typ AS OBJECT
 (employee_id NUMBER(6),
  first_name VARCHAR2(20),
  last_name VARCHAR2(25),
  email VARCHAR2(25),
  phone_number VARCHAR2(20),
  hire_date DATE,
  job_id VARCHAR2(10),
  salary NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id NUMBER(6),
  department_id NUMBER(4),
  address ADDRESS_TYP,
  MAP MEMBER FUNCTION get_idno RETURN NUMBER,
  MEMBER PROCEDURE display_address(SELF  IN OUT NOCOPY EMPLOYEE_TYP)
 );
/

CREATE TYPE BODY employee_typ AS
 MAP MEMBER FUNCTION Get_idno
 RETURN NUMBER
 IS
 BEGIN
   RETURN employee_id;
 END;
 MEMBER PROCEDURE Display_address
      (SELF  IN OUT NOCOPY EMPLOYEE_TYP)
 IS
 BEGIN
   dbms_output.Put_line(first_name
                        ||' '
                        ||last_name);
   dbms_output.Put_line(address.street);
   dbms_output.Put_line(address.city
                        ||', '
                        ||address.state
                        ||' '
                        ||address.postal_code);
 END;
END;
/

CREATE TABLE employee_tab OF employee_typ;

Working With Object Types

DROP TABLE gradereport CASCADE CONSTRAINTS PURGE;

CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30),
weight NUMBER, grade NUMBER);

INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4);
INSERT INTO gradereport VALUES('Mark','Chemistry', 4, 3);
INSERT INTO gradereport VALUES('Mark','Maths', 3, 3);
INSERT INTO gradereport VALUES('Mark','Economics', 3, 4);

DROP PACKAGE pkg_gpa;

CREATE PACKAGE pkg_gpa IS
TYPE gpa IS TABLE OF NUMBER;
FUNCTION weighted_average(input_values SYS_REFCURSOR)
RETURN gpa PIPELINED;
END pkg_gpa;
/

CREATE PACKAGE BODY pkg_gpa IS
FUNCTION weighted_average(input_values SYS_REFCURSOR)
RETURN gpa PIPELINED IS
grade NUMBER;
total NUMBER := 0;
total_weight NUMBER := 0;
weight NUMBER := 0;
BEGIN
-- The function accepts a ref cursor and loops through all the input rows
LOOP
FETCH input_values INTO weight, grade;
EXIT WHEN input_values%NOTFOUND;
-- Accumulate the weighted average
total_weight := total_weight + weight;
total := total + grade*weight;
END LOOP;
PIPE ROW (total / total_weight);
RETURN; -- the function returns a single result
END;
END pkg_gpa;
/

-- the query result comes back as a nested table with a single row
-- COLUMN_VALUE is a keyword that returns the contents of a nested table
SELECT w.column_value "weighted result" FROM TABLE(
pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;

weighted result
---------------
            3.5

Using a Pipelined Table Function as an Aggregate Function

DROP PACKAGE refcur_pkg;

-- Define the ref cursor types
CREATE PACKAGE refcur_pkg IS
TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;
TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30));
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)
RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE PACKAGE BODY refcur_pkg IS
FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)
RETURN outrecset PIPELINED IS
out_rec outrec_typ;
in_rec1 p1%ROWTYPE;
in_rec2 p2%ROWTYPE;
BEGIN
LOOP
FETCH p2 INTO in_rec2;
EXIT WHEN p2%NOTFOUND;
END LOOP;
CLOSE p2;
LOOP
FETCH p1 INTO in_rec1;
EXIT WHEN p1%NOTFOUND;
-- first row
out_rec.var_num := in_rec1.employee_id;
out_rec.var_char1 := in_rec1.first_name;
out_rec.var_char2 := in_rec1.last_name;
PIPE ROW(out_rec);
-- second row
out_rec.var_num := in_rec2.department_id;
out_rec.var_char1 := in_rec2.department_name;
out_rec.var_char2 := TO_CHAR(in_rec2.location_id);
PIPE ROW(out_rec);
END LOOP;
CLOSE p1;
RETURN;
END;
END refcur_pkg;
/

-- SELECT query using the g_trans table function
SELECT * FROM TABLE(refcur_pkg.g_trans(
CURSOR(SELECT * FROM employees WHERE department_id = 60),
CURSOR(SELECT * FROM departments WHERE department_id = 60)));

Using Multiple REF CURSOR Input Variables