CREATE OR REPLACE FUNCTION longer_string (string_in      IN VARCHAR2,
                                          to_length_in   IN INTEGER)
   RETURN VARCHAR2
   AUTHID DEFINER
IS
BEGIN
   RETURN RPAD (string_in, to_length_in, 'x');
END;

CREATE OR REPLACE TYPE strings_t IS TABLE OF VARCHAR2 (100)

CREATE OR REPLACE FUNCTION random_strings (count_in IN INTEGER)
   RETURN strings_t
   AUTHID DEFINER
IS
   l_strings   strings_t := strings_t ();
BEGIN
   l_strings.EXTEND (count_in);

   FOR indx IN 1 .. count_in
   LOOP
      l_strings (indx) := DBMS_RANDOM.string ('u', 10);
   END LOOP;

   RETURN l_strings;
END;

DECLARE
   l_strings   strings_t := random_strings (5);
BEGIN
   FOR indx IN 1 .. l_strings.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_strings (indx));
   END LOOP;
END;

SELECT COLUMN_VALUE FROM TABLE (random_strings (5))

CREATE OR REPLACE TYPE two_strings_ot
   AUTHID DEFINER IS OBJECT
(
   string1 VARCHAR2 (10),
   string2 VARCHAR2 (10)
)

CREATE OR REPLACE TYPE two_strings_nt IS TABLE OF two_strings_ot

CREATE OR REPLACE FUNCTION three_pairs
   RETURN two_strings_nt
   AUTHID DEFINER
IS
   l_strings   two_strings_nt;
BEGIN
   RETURN two_strings_nt (two_strings_ot ('a', 'b'),
                          two_strings_ot ('c', 'd'),
                          two_strings_ot ('e', 'f'));
END;

SELECT string1, string2 FROM TABLE (three_pairs ())

DROP TABLE string_pairs

CREATE TABLE string_pairs
(
   string1   VARCHAR2 (10),
   string2   VARCHAR2 (10)
)

BEGIN
   INSERT INTO string_pairs
        VALUES ('a', 'bb');

   INSERT INTO string_pairs
        VALUES ('cc', 'dd');

   COMMIT;
END;

SELECT * FROM string_pairs
UNION ALL
SELECT * FROM TABLE (three_pairs ())

  SELECT sp.string1, sp.string2 sp_string2, p3.string2 ps_string2
    FROM string_pairs sp, TABLE (three_pairs ()) p3
   WHERE sp.string1 = p3.string1
ORDER BY string1

CREATE OR REPLACE VIEW three_pairs_v
AS
   SELECT * FROM TABLE (three_pairs ())

SELECT * FROM string_pairs
UNION ALL
SELECT * FROM three_pairs_v

CREATE OR REPLACE PACKAGE my_pkg AUTHID DEFINER
AS
   TYPE names_t IS TABLE OF VARCHAR2 (100);
   FUNCTION my_names RETURN names_t PIPELINED;
END;

CREATE OR REPLACE PACKAGE BODY my_pkg
AS
   FUNCTION my_names RETURN names_t PIPELINED
   IS
   BEGIN
      PIPE ROW ('Loey');
      PIPE ROW ('Juna');
      PIPE ROW ('Grandpa Steven');
      RETURN;
   END;
END;

SELECT COLUMN_VALUE a_name FROM TABLE (my_pkg.my_names)

DECLARE
   TYPE numbers_t IS TABLE OF NUMBER;

   l_numbers   numbers_t := numbers_t ();
   l_count     INTEGER;
BEGIN
   SELECT COUNT (*) INTO l_count FROM TABLE (l_numbers);
END;