JoeGarrepy.com Oracle Pipelined Function Example

Programming/
Tools
      Oracle
      JavaScript
      C#

Fun Stuff
      Music
      Martial Arts
      Robotech

Other
      Main

DISCLAIMER

This disclaimer is intended to cover all software/freeware, and other various files which are downloadable through or from this site.
Joe Garrepy, isp, and web host can not be held responsible for any software downloaded from this site. All freeware is downloaded at your own risk. Software which is listed on this site is assumed to work properly, but may or may not have different effects on different computer systems. The same rules apply to these programs. Joe Garrepy assumes no responsibility for those unusual occurrences where software has adverse effects on users computer. Joe Garrepy does not warrant these programs/files, and as such, can offer no guidelines in which to use them. All freeware/software/files, trademarks, service marks, and copyrights are acknowledged as belonging to their owners. Any of these which the owner wishes to be removed will be done so immediately. By downloading any software or viewing pages in which software can be downloaded, you agree to be bound to this agreement.

JoeGarrepy.com Oracle Pipelined Function Example

A powerful new feature in Oracle 9i is the Pipelined Function or the Parameterized View. You can now create functions that return rows like selecting from a table or view. I can see about a million things you could do with this including optimizing really bizarre queries and using it to truly control what Oracle Reports does. I've always had a problem with how Oracle Reports returns data from the Oracle Reports Datamodel. With a Pipelined function though, you can control right in PL what is returned. I've been testing this in Oracle Reports 6.0 and it works great!

There are two examples of a piplined function on this page. The first returns a table of the Alphabet while the second returns an ASCII Character Codes chart.

Alphabet Table SQL Script

/*
  -- ****************************************************************** --
   9/29/2004 PIPLINED FUNCTION Create Script (Joe Garrepy) VERSION 1.0
   Creates a function that will output a table of the Alphabet.
  -- ****************************************************************** --
   The following script creates an Oracle Pipelined Function that
   returns the Alphabet character, sort order and ASCII code.
  -- ****************************************************************** --
*/
--> Step 1, create a "row" type
CREATE OR REPLACE TYPE "ROW_ALPHABET"
  AS OBJECT
  (UPPER_CHARACTER VARCHAR2(1),
   LOWER_CHARACTER VARCHAR2(1),
   SORT_ORDER NUMBER(2),
   UPPER_ASCII_CODE NUMBER(5),
   LOWER_ASCII_CODE NUMBER(5))
/
--> Step 2, create a "table" type based on the rowtype you just created
CREATE OR REPLACE TYPE "TAB_ALPHABET"
  AS TABLE OF ROW_ALPHABET
/
--> Step 3, create piplined function
CREATE OR REPLACE FUNCTION "F_ALPHABET"
RETURN TAB_ALPHABET PIPELINED
IS
 --> Create a return row and initialize it
  out_row   ROW_ALPHABET := ROW_ALPHABET(NULL,NULL,NULL,NULL,NULL);
 --
  intAlpha  PLS_INTEGER := 64;
 --
BEGIN
 --
  LOOP
    EXIT WHEN CHR(intAlpha) = 'Z';
    intAlpha := intAlpha + 1;
   --> Setup row to return
    out_row.UPPER_CHARACTER := CHR(intAlpha);
    out_row.LOWER_CHARACTER := LOWER(CHR(intAlpha));
    out_row.SORT_ORDER := intAlpha - 64;
    out_row.UPPER_ASCII_CODE := intAlpha;
    out_row.LOWER_ASCII_CODE := ASCII(LOWER(CHR(intAlpha)));
   --> Pipeout Row
    PIPE ROW (out_row);
   --
  END LOOP;
 --> You must have a RETURN in a piplined fuction.  If you do not then
 --> the function will fail at runtime.  This is also true of any exception
 --> statements that you setup inside the funciton.  Each must end in a
 --> RETURN; or the code will fail and not trap the exception instead failing
 --> on the lack of the RETURN statement.
  RETURN;
 --
END f_alphabet;
/
--> Step 4, select from your Pipelined function
select * from TABLE(f_alphabet);

The function should return the following data...

UPPER_CHARACTER	LOWER_CHARACTER	SORT_ORDER	UPPER_ASCII_CODE	LOWER_ASCII_CODE
A	a	1	65	97
B	b	2	66	98
C	c	3	67	99
D	d	4	68	100
E	e	5	69	101
F	f	6	70	102
G	g	7	71	103
H	h	8	72	104
I	i	9	73	105
J	j	10	74	106
K	k	11	75	107
L	l	12	76	108
M	m	13	77	109
N	n	14	78	110
O	o	15	79	111
P	p	16	80	112
Q	q	17	81	113
R	r	18	82	114
S	s	19	83	115
T	t	20	84	116
U	u	21	85	117
V	v	22	86	118
W	w	23	87	119
X	x	24	88	120
Y	y	25	89	121
Z	z	26	90	122

ASCII Character Table SQL Script

/*
  -- ****************************************************************** --
   10/12/2004 PIPLINED FUNCTION Create Script (Joe Garrepy) VERSION 1.0
   Table of ASCII Characters
  -- ****************************************************************** --
   The following script creates a piplined function that outputs an ASCII
   character chart.  The field ASCIIDECIMAL is the ASCII value that is
   returned.
  -- ****************************************************************** --
*/
--> Step 1, create a "row" type
CREATE OR REPLACE TYPE "ROW_ASCII"
  AS OBJECT
  (ASCIIDECIMAL NUMBER(10),
   SINGLECHAR   VARCHAR2(1),
   OCTAL        NUMBER(10),
   HEXADECIMAL  VARCHAR2(10))
/
--> Step 2, create a "table" type based on the rowtype you just created
CREATE OR REPLACE TYPE "TAB_ASCII"
  AS TABLE OF ROW_ASCII
/
--> Step 3, create piplined function
CREATE OR REPLACE FUNCTION "F_ASCII"
RETURN TAB_ASCII PIPELINED
IS
 --> Create a return row and initialize it
  out_row   ROW_ASCII := ROW_ASCII(NULL,NULL,NULL,NULL);
 --
  intDec    PLS_INTEGER := -1;
  varCHR    VARCHAR2(1);
 --> The DUMP function can only be used from PL/SQL so here is cursor to get the values...
  CURSOR tblAscii IS select TO_NUMBER(SUBSTR(TRIM(SUBSTR(DUMP(varCHR,8),INSTR(Dump(varCHR,8),':') + 1,LENGTH(DUMP(varCHR,8)))),1,10)) AS OCTAL,
                            TO_NUMBER(SUBSTR(TRIM(SUBSTR(DUMP(varCHR,10),INSTR(DUMP(varCHR,10),':') + 1,LENGTH(DUMP(varCHR,10)))),1,10)) AS ASCIIDECIMAL,
                            SUBSTR(TRIM(SUBSTR(DUMP(varCHR,16),INSTR(DUMP(varCHR,16),':') + 1,LENGTH(DUMP(varCHR,16)))),1,10) AS HEXADECIMAL,
                            SUBSTR(TRIM(SUBSTR(DUMP(varCHR,17),INSTR(DUMP(varCHR,17),':') + 1,LENGTH(DUMP(varCHR,17)))),1,1) AS SINGLECHAR
                     FROM DUAL;
  rowAscii  tblAscii%ROWTYPE;

 --
BEGIN
 --
  LOOP
    EXIT WHEN intDec >= 255;
    intDec := intDec + 1;
    varCHR := CHR(intDec);
   --> Get values from DUMP function through cursor
    OPEN tblAscii;
    FETCH tblAscii INTO rowAscii;
    CLOSE tblAscii;
   --> Setup row to return
    out_row.OCTAL := rowAscii.OCTAL;
    out_row.ASCIIDECIMAL := rowAscii.ASCIIDECIMAL;
    out_row.HEXADECIMAL := rowAscii.HEXADECIMAL;
    out_row.SINGLECHAR := rowAscii.SINGLECHAR;
   --> Pipeout Row
    PIPE ROW (out_row);
   --
  END LOOP;
 --> You must have a RETURN in a piplined fuction.  If you do not then
 --> the function will fail at runtime.  This is also true of any exception
 --> statements that you setup inside the funciton.  Each must end in a
 --> RETURN; or the code will fail and not trap the exception instead failing
 --> on the lack of the RETURN statement.
  RETURN;
 --
END f_ascii;
/
--> Step 4, select from your Pipelined function
select * from TABLE(f_ascii);

If you find any bugs or want to contact me I can be reached @ doughjoe77@hotmail.com.