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>