How to Cast PLSQL Function Output as View

An Oracle view is like a window to the output of a SQL statement, but sometimes, a single SQL isn’t sufficient – you may need to do some procedural data manipulation before you get the output you want. Does this mean an Oracle view cannot be used if the underlying code cannot be framed as a single SQL? Not at all! This article shows you how to cast PLSQL function output as a view.

For the purpose of demo, let us take this objective: a view that lists the number of months with their names in chronological order, for any number between 1-12. For a number > 12, it gives a user-defined error.

Implementing the above using a PL/SQL function wrapped inside a view.

1. Creating the objects to define the function parameters

SQL> -- Object to hold month number and name
SQL> create type month_obj_type
  2  as object
  3  (
  4    month_num   number
  5  , month_name  varchar2(10)
  6  )
  7  /

Type created.

SQL>
SQL> -- Table of month object
SQL> -- (month number, month name)
SQL> create type month_tbl_type
  2  as table of month_obj_type
  3  /

Type created.

2. Creating the PL/SQL function

SQL> -- Function to return list of
SQL> -- (month number, month name); length of
SQL> -- list based on the input count
SQL>
SQL> create or replace function get_month_list
  2  (
  3    month_count  in number
  4  )
  5  return month_tbl_type
  6  as
  7    po_month_tbl month_tbl_type := month_tbl_type();
  8
  9  begin
 10
 11    if month_count > 12 then
 12      dbms_output.put_line('I/P out of range: 1-12');
 13      return null;
 14    end if;
 15
 16    for i in 1..month_count loop
 17
 18      po_month_tbl.extend;
 19
 20      po_month_tbl(i) := month_obj_type(
 21                          i
 22                        , to_char(to_date(i,'MM')
 23                                        ,'MONTH')
 24                                     );
 25
 26    end loop;
 27
 28    return po_month_tbl;
 29
 30  end;
 31  /

Function created.

3. Test runs of the PL/SQL function

SQL> -- Example 1: Using get_month_list
SQL> -- for months upto 5
SQL> select get_month_list(5) months
  2  from dual
  3  /

MONTHS(MONTH_NUM, MONTH_NAME)
--------------------------------------------------
MONTH_TBL_TYPE(MONTH_OBJ_TYPE(1, 'JANUARY  '), MON
TH_OBJ_TYPE(2, 'FEBRUARY '), MONTH_OBJ_TYPE(3, 'MA
RCH    '), MONTH_OBJ_TYPE(4, 'APRIL    '), MONTH_O
BJ_TYPE(5, 'MAY      '))

SQL>
SQL> -- Example 2: Using get_month_list
SQL> -- for months upto 15 [Error]
SQL> select get_month_list(15) months
  2  from dual
  3  /

MONTHS(MONTH_NUM, MONTH_NAME)
--------------------------------------------------

Input outside range: 1-12

4. Creating the view over the function

SQL> -- Casting the output of get_month_list
SQL> -- as a view
SQL> create or replace view month_list_view
  2  as
  3    select *
  4    from table(get_month_list(5))
  5  /

View created.

5. Test run of the view

SQL> -- Using the view that internally
SQL> -- uses a PL/SQL function
SQL> select *
  2  from month_list_view
  3  /

 MONTH_NUM MONTH_NAME
---------- ----------
         1 JANUARY
         2 FEBRUARY
         3 MARCH
         4 APRIL
         5 MAY