CREATE TABLE employees AS SELECT * FROM hr.employees
/

Function with Boolean Parameter

CREATE OR REPLACE FUNCTION uc_last_name ( 
   employee_id_in   IN employees.employee_id%TYPE, 
   upper_in         IN BOOLEAN) 
   RETURN employees.last_name%TYPE 
IS 
   l_return   employees.last_name%TYPE; 
BEGIN 
   SELECT last_name 
     INTO l_return 
     FROM employees 
    WHERE employee_id = employee_id_in; 
 
   RETURN CASE WHEN upper_in THEN UPPER (l_return) ELSE l_return END; 
END; 

Reference a Boolean Inside SQL!

DECLARE
   b BOOLEAN := TRUE;
BEGIN 
   FOR rec IN (SELECT uc_last_name (employee_id, b) lname 
                 FROM employees 
                WHERE department_id = 10) 
   LOOP 
      DBMS_OUTPUT.put_line (rec.lname); 
   END LOOP; 
END; 

Bind Boolean in Dynamic SQL

DECLARE 
   l_uc   BOOLEAN := TRUE; 
BEGIN 
   EXECUTE IMMEDIATE 
      'BEGIN DBMS_OUTPUT.PUT_LINE (uc_last_name (138, :b)); END;' 
      USING l_uc; 
END; 

CREATE OR REPLACE FUNCTION is_it_null (value_in IN VARCHAR2) 
   RETURN BOOLEAN 
IS 
BEGIN 
   RETURN value_in IS NULL; 
END; 

Bind Variable to Boolean Returned by Function

DECLARE 
   l_uc   BOOLEAN := TRUE; 
BEGIN 
   EXECUTE IMMEDIATE 'BEGIN :is_it_null := is_it_null (''abc''); END;' 
      USING OUT l_uc; 
END; 

CREATE OR REPLACE FUNCTION f (x BOOLEAN, y PLS_INTEGER) 
   RETURN employees.employee_id%TYPE 
   AUTHID CURRENT_USER 
AS 
BEGIN 
   IF x 
   THEN 
      RETURN y; 
   ELSE 
      RETURN 2 * y; 
   END IF; 
END; 

More Fun with Booleans in SELECTs

DECLARE 
   name   employees.last_name%TYPE; 
   b      BOOLEAN := TRUE; 
BEGIN 
   SELECT last_name 
     INTO name 
     FROM employees 
    WHERE employee_id = f (b, 100); 
 
   DBMS_OUTPUT.put_line (name); 

   b := FALSE; 
 
   SELECT last_name 
     INTO name 
     FROM employees 
    WHERE employee_id = f (b, 100); 
 
   DBMS_OUTPUT.put_line (name); 
END; 

Package with Collection of Records (Type)

CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
AS 
   TYPE rec IS RECORD 
   ( 
      f1   NUMBER, 
      f2   VARCHAR2 (30) 
   ); 
 
   TYPE mytab IS TABLE OF rec 
      INDEX BY PLS_INTEGER; 
END; 

Yes We Can! (in 12.1)

DECLARE 
   v1   pkg.mytab;   
   v2   pkg.rec; 
   c1   SYS_REFCURSOR; 
BEGIN 
   OPEN c1 FOR SELECT * FROM TABLE (v1); 
 
   FETCH c1 INTO v2; 
 
   CLOSE c1; 
END; 

First, TABLE Operator with Nested Table

CREATE OR REPLACE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100); 

DECLARE 
   happyfamily   list_of_names_t 
                    := list_of_names_t ('Sally', 'Sam', 'Agatha'); 
BEGIN 
   FOR rec IN (  SELECT COLUMN_VALUE family_name 
                   FROM TABLE (happyfamily) 
               ORDER BY family_name) 
   LOOP 
      DBMS_OUTPUT.put_line (rec.family_name); 
   END LOOP; 
END; 

Now TABLE with Package-based Associative Array Type

CREATE OR REPLACE PACKAGE names_pkg 
IS 
   TYPE list_of_names_t IS TABLE OF VARCHAR2 (100) 
      INDEX BY PLS_INTEGER; 
END; 

Read-Consistency with TABLE (Array)

DECLARE 
   happyfamily   names_pkg.list_of_names_t; 
BEGIN 
   happyfamily (1) := 'Sally'; 
   happyfamily (2) := 'Sam'; 
   happyfamily (3) := 'Agatha'; 
 
   FOR rec IN (  SELECT COLUMN_VALUE family_name 
                   FROM TABLE (happyfamily) 
               ORDER BY family_name) 
   LOOP 
      happyfamily.delete; 
      DBMS_OUTPUT.put_line (rec.family_name); 
      DBMS_OUTPUT.put_line (happyfamily.COUNT); 
   END LOOP; 
 
   DBMS_OUTPUT.put_line (happyfamily.COUNT); 
END; 

Bind a User-Defined Record Type

CREATE OR REPLACE PACKAGE rec_pkg 
AS
   TYPE rec_t IS RECORD (
      number1   NUMBER,
      number2   NUMBER
   );

   PROCEDURE set_rec (n1_in IN NUMBER, n2_in IN NUMBER, 
      rec_out OUT rec_t);
END rec_pkg;
/

CREATE OR REPLACE PACKAGE BODY rec_pkg
AS
   PROCEDURE set_rec (n1_in IN NUMBER, n2_in IN NUMBER, 
      rec_out OUT rec_t)
   AS
   BEGIN
      rec_out.number1 := n1_in;
      rec_out.number2 := n2_in;
   END set_rec;
END rec_pkg;
/

DECLARE
   l_record  rec_pkg.rec_t;
BEGIN
   EXECUTE IMMEDIATE 'BEGIN rec_pkg.set_rec (10, 20, :rec); END;' 
      USING OUT l_record;

   DBMS_OUTPUT.put_line ('number1 = ' || l_record.number1);
   DBMS_OUTPUT.put_line ('number2 = ' || l_record.number2);
END;
/