Oracle PL/SQL/Stored Procedure Function/PIPELINED
An example of a pipelined table function.
<source lang="sql">
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>
</source>
pipelined returning value and table() function
<source lang="sql">
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.
</source>
Using a pipelined table function.
<source lang="sql">
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>
</source>