Oracle PL/SQL/Stored Procedure Function/PIPELINED

An example of a pipelined table function.

  
SQL>
SQL>
SQL> CREATE TYPE MyType AS OBJECT (
  2    field1 NUMBER,
  3    field2 VARCHAR2(50));
  4  /

SQL>
SQL> CREATE or replace TYPE MyTypeList AS TABLE OF MyType;
  2  /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION PipelineMe
  2    RETURN MyTypeList PIPELINED AS
  3    v_MyType MyType;
  4  BEGIN
  5    FOR v_Count IN 1..20 LOOP
  6      v_MyType := MyType(v_Count, "Row " || v_Count);
  7      PIPE ROW(v_MyType);
  8    END LOOP;
  9    RETURN;
 10  END PipelineMe;
 11  /
Function created.
SQL>
SQL> SELECT *
  2    FROM TABLE(PipelineMe);
    FIELD1 FIELD2
---------- --------------------------------------------------
         1 Row 1
         2 Row 2
         3 Row 3
         4 Row 4
         5 Row 5
         6 Row 6
         7 Row 7
         8 Row 8
         9 Row 9
        10 Row 10
        11 Row 11
        12 Row 12
        13 Row 13
        14 Row 14
        15 Row 15
        16 Row 16
        17 Row 17
        18 Row 18
        19 Row 19
        20 Row 20
20 rows selected.
SQL>
SQL> DROP TYPE MyType;

SQL>
SQL>

pipelined returning value and table() function

   
SQL>
SQL>
SQL> create table myTable as
  2   select trunc(created) created
  3   from all_objects;
Table created.
SQL>
SQL> create or replace
  2   type date_list is table of Date;
  3  /
Type created.
SQL> create or replace function pipe_date(p_start date, p_limit number)
  2   return date_list pipelined is
  3   begin
  4       for i in 0 .. p_limit-1 loop
  5           pipe row (p_start + i);
  6       end loop;
  7   return;
  8   end;
  9  /
Function created.
SQL>
SQL> select column_value, count(created) no_of_obj
  2   from myTable, table(pipe_date(trunc(sysdate)-14,14))
  3   where column_value = myTable.created(+)
  4   group by column_value
  5  /
COLUMN_VA  NO_OF_OBJ
--------- ----------
12-OCT-09          0
13-OCT-09          0
14-OCT-09          0
15-OCT-09         45
16-OCT-09         55
17-OCT-09        243
18-OCT-09        177
19-OCT-09         53
20-OCT-09          0
21-OCT-09         24
22-OCT-09          0
COLUMN_VA  NO_OF_OBJ
--------- ----------
23-OCT-09          0
24-OCT-09          0
25-OCT-09          0
14 rows selected.
SQL>
SQL> drop table myTable;
Table dropped.

Using a pipelined table function.

   
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE OracleError AS OBJECT (
  2    ErrNumber INTEGER,
  3    Message VARCHAR2(4000));
  4  /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE OracleErrors AS TABLE OF OracleError;
  2  /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION OracleErrorTable
  2    RETURN OracleErrors DETERMINISTIC PIPELINED
  3  AS
  4    v_Low PLS_INTEGER := -65535;
  5    v_High PLS_INTEGER := 100;
  6    v_Message VARCHAR2(4000);
  7  BEGIN
  8    FOR i IN v_Low..v_High LOOP
  9      v_Message := SQLERRM(i);
 10
 11      IF v_Message != " -" || TO_CHAR(i) || ": non-ORACLE exception "
 12      AND v_Message != "ORA" || TO_CHAR(i, "00000") || ": Message " ||
 13          TO_CHAR(-i) || " not found;  product=RDBMS; facility=ORA"
 14      THEN
 15        PIPE ROW(OracleError(i, v_Message));
 16      END IF;
 17    END LOOP;
 18    RETURN;
 19  END;
 20  /
Function created.
SQL>
SQL> CREATE OR REPLACE VIEW all_oracle_errors
  2    AS SELECT * FROM TABLE(OracleErrorTable());
View created.
SQL>
SQL> desc all_oracle_errors
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ERRNUMBER                                          NUMBER
 MESSAGE                                            VARCHAR2(4000)
SQL> SELECT MIN(errnumber), MAX(errnumber), COUNT(*) FROM all_oracle_errors;
MIN(ERRNUMBER) MAX(ERRNUMBER)   COUNT(*)
-------------- -------------- ----------
        -44412            100      15430
1 row selected.
SQL>
SQL>
SQL> COLUMN message FORMAT a60
SQL>
SQL> SELECT *
  2    FROM all_oracle_errors
  3    WHERE errnumber BETWEEN -115 AND 100;
 ERRNUMBER MESSAGE
---------- ------------------------------------------------------------
      -115 ORA-00115: connection refused; dispatcher connection table i
           s full
      -114 ORA-00114: missing value for system parameter SERVICE_NAMES
      -113 ORA-00113: protocol name  is too long
      -112 ORA-00112: value of  is null
      -111 ORA-00111: invalid attribute
      -110 ORA-00110: invalid value  for attribute , must be between  a
           nd
      -109 ORA-00109: invalid value for attribute :
      -108 ORA-00108: failed to set up dispatcher to accept connection
           asynchronously
      -107 ORA-00107: failed to connect to ORACLE listener process
      -106 ORA-00106: cannot startup/shutdown database when connected t
           o a dispatcher
      -105 ORA-00105: too many dispatcher configurations
      -104 ORA-00104: deadlock detected; all public servers blocked wai
           ting for resources
      -103 ORA-00103: invalid network protocol; reserved for use by dis
           patchers
      -102 ORA-00102: network protocol  cannot be used by dispatchers
      -101 ORA-00101: invalid specification for system parameter DISPAT
           CHERS
      -100 ORA-00100: no data found
       -97 ORA-00097: use of Oracle SQL feature not in SQL92  Level
       -96 ORA-00096: invalid value  for parameter , must be from among

       -94 ORA-00094:  requires an integer value
       -93 ORA-00093:  must be between  and
       -92 ORA-00092: LARGE_POOL_SIZE must be greater than LARGE_POOL_M
           IN_ALLOC
       -91 ORA-00091: LARGE_POOL_SIZE must be at least
       -90 ORA-00090: failed to allocate memory for cluster database OR
           ADEBUG command
       -89 ORA-00089: invalid instance number in ORADEBUG command
       -88 ORA-00088: command cannot be executed by shared server
       -87 ORA-00087: command cannot be executed on remote instance
       -86 ORA-00086: user call does not exist
       -85 ORA-00085: current call does not exist
       -84 ORA-00084: global area must be PGA, SGA, or UGA
       -83 ORA-00083: warning: possibly corrupt SGA mapped
       -82 ORA-00082: memory size of  is not in valid set of [1], [2],
           [4]
       -81 ORA-00081: address range [, ) is not readable
       -80 ORA-00080: invalid global area specified by level
       -79 ORA-00079: variable  not found
       -78 ORA-00078: cannot dump variables by name
       -77 ORA-00077: dump  is not valid
       -76 ORA-00076: dump  not found
       -75 ORA-00075: process "" not found in this instance
       -74 ORA-00074: no process has been specified
       -73 ORA-00073: command  takes between  and  argument(s)
       -72 ORA-00072: process "" is not active
       -71 ORA-00071: process number must be between 1 and
       -70 ORA-00070: command  is not valid
       -69 ORA-00069: cannot acquire lock -- table locks disabled for
       -68 ORA-00068: invalid value  for parameter , must be between  a
           nd
       -67 ORA-00067: invalid value  for parameter ; must be at least
       -65 ORA-00065: initialization of FIXED_DATE failed
       -64 ORA-00064: object is too large to allocate on this O/S (,)
       -63 ORA-00063: maximum number of log files exceeded
       -62 ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS
            is 0
       -61 ORA-00061: another instance has a different DML_LOCKS settin
           g
       -60 ORA-00060: deadlock detected while waiting for resource
       -59 ORA-00059: maximum number of DB_FILES exceeded
       -58 ORA-00058: DB_BLOCK_SIZE must be  to mount this database (no
           t )
       -57 ORA-00057: maximum number of temporary table locks exceeded
       -56 ORA-00056: DDL lock on object "." is already held in an inco
           mpatible mode
       -55 ORA-00055: maximum number of DML locks exceeded
       -54 ORA-00054: resource busy and acquire with NOWAIT specified
       -53 ORA-00053: maximum number of enqueues exceeded
       -52 ORA-00052: maximum number of enqueue resources () exceeded
       -51 ORA-00051: timeout occurred while waiting for a resource
       -50 ORA-00050: operating system error occurred while obtaining a
           n enqueue
       -42 ORA-00042: Unknown Service name
       -41 ORA-00041: active time limit exceeded - session terminated
       -40 ORA-00040: active time limit exceeded - call aborted
       -38 ORA-00038: Cannot create session: server group belongs to an
           other user
       -37 ORA-00037: cannot switch to a session belonging to a differe
           nt server group
       -36 ORA-00036: maximum number of recursive SQL levels () exceede
           d
       -35 ORA-00035: LICENSE_MAX_USERS cannot be less than current num
           ber of users
       -34 ORA-00034: cannot  in current PL/SQL session
       -33 ORA-00033: current session has empty migration password
       -32 ORA-00032: invalid session migration password
       -31 ORA-00031: session marked for kill
       -30 ORA-00030: User session ID does not exist.
       -29 ORA-00029: session is not a user session
       -28 ORA-00028: your session has been killed
       -27 ORA-00027: cannot kill current session
       -26 ORA-00026: missing or invalid session ID
       -25 ORA-00025: failed to allocate
       -24 ORA-00024: logins from more than one process not allowed in
           single-process mode
       -23 ORA-00023: session references process private memory; cannot
            detach session
       -22 ORA-00022: invalid session ID; access denied
       -21 ORA-00021: session attached to some other process; cannot sw
           itch session
       -20 ORA-00020: maximum number of processes () exceeded
       -19 ORA-00019: maximum number of session licenses exceeded
       -18 ORA-00018: maximum number of sessions exceeded
       -17 ORA-00017: session requested to set trace event
        -1 ORA-00001: unique constraint (.) violated
         0 ORA-0000: normal, successful completion
         1 User-Defined Exception
       100 ORA-01403: no data found
91 rows selected.
SQL>
SQL>
SQL> DROP TYPE OracleErrors;
Type dropped.
SQL>