Using a pipelined table function. : PIPELINED « Stored Procedure Function « Oracle PL / SQL






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>

   
    
    
  








Related examples in the same category

1.An example of a pipelined table function.
2.pipelined returning value and table() function