Simple Table Function Example: Collection of Scalars

View All Scripts Login to Run Script

Script Name Simple Table Function Example: Collection of Scalars
Description A table function is a function executed with the TABLE operator, and then within the FROM clause of a query - in other words, a function that is selected from just like a relational table! A common usage of table functions in the Data Warehousing world is to stream data directly from one process or transformation, to the next process without intermediate staging (a "streaming" table function - see my script demonstrating that elsewhere on LiveSQL). But you can also leverage table functions to programatically create a dataset and then make it available in SQL.
Area PL/SQL General
Contributor Steven Feuerstein
Created Friday May 06, 2016

Statement 1
A table function must return a collection that is visible in the SQL layer. So the collection's type must be defined at the schema-level as shown here or in 12.1 and higher in a package specification.
Create Nested Table Type
```
CREATE OR REPLACE TYPE names_nt IS TABLE OF VARCHAR2 ( 1000 ); 
```
```
Type created.
```

Statement 2

I need to generated N number of names. Here's a function that does it. It's a silly example. I can do this in SQL, too, but it demonstrates the ability to programmatically (procedurally) populate a collection.

Silly Dataset Generator

CREATE OR REPLACE FUNCTION lotsa_names (  
   base_name_in   IN   VARCHAR2  
 , count_in       IN   INTEGER  
)  
   RETURN names_nt  
IS  
   retval names_nt := names_nt ( );  
BEGIN  
   retval.EXTEND ( count_in );  
  
   FOR indx IN 1 .. count_in  
   LOOP  
      retval ( indx ) := base_name_in || ' ' || indx;  
   END LOOP;  
  
   RETURN retval;  
END lotsa_names;

Function created.

Statement 3

And there you have it, folks! Embed the function invocation inside the TABLE operator, in the FROM clause, and Oracle Database works its magic for you. And when you have a collection of scalars, the column name is hard-coded to COLUMN_VALUE but you can use a column alias to change it to whatever you'd like.

Call table function inside SELECT

SELECT COLUMN_VALUE my_name 
  FROM TABLE ( lotsa_names ( 'Steven', 100 )) names


MY_NAME
Steven 1
Steven 2
Steven 3
Steven 4
Steven 5
Steven 6
Steven 7
Steven 8
Steven 9
Steven 10
Steven 11
Steven 12
Steven 13
Steven 14
Steven 15
Steven 16
Steven 17
Steven 18
Steven 19
Steven 20
Steven 21
Steven 22
Steven 23
Steven 24
Steven 25
Steven 26
Steven 27
Steven 28
Steven 29
Steven 30
Steven 31
Steven 32
Steven 33
Steven 34
Steven 35
Steven 36
Steven 37
Steven 38
Steven 39
Steven 40
Steven 41
Steven 42
Steven 43
Steven 44
Steven 45
Steven 46
Steven 47
Steven 48
Steven 49
Steven 50

Rows 1 - 50. More rows exist.

MY_NAME
Steven 1
Steven 2
Steven 3
Steven 4
Steven 5
Steven 6
Steven 7
Steven 8
Steven 9
Steven 10
Steven 11
Steven 12
Steven 13
Steven 14
Steven 15
Steven 16
Steven 17
Steven 18
Steven 19
Steven 20
Steven 21
Steven 22
Steven 23
Steven 24
Steven 25
Steven 26
Steven 27
Steven 28
Steven 29
Steven 30
Steven 31
Steven 32
Steven 33
Steven 34
Steven 35
Steven 36
Steven 37
Steven 38
Steven 39
Steven 40
Steven 41
Steven 42
Steven 43
Steven 44
Steven 45
Steven 46
Steven 47
Steven 48
Steven 49
Steven 50

Statement 4

Once TABLE has transformed your collection into a relational dataset, you can join it to other tables, perform unions, etc. Whatever you would and could do with a "normal" table or view.

A "Table" Just Like Any Other

SELECT COLUMN_VALUE my_alias  
  FROM hr.employees, TABLE ( lotsa_names ( 'Steven', 10 )) names


MY_ALIAS
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1

Rows 1 - 50. More rows exist.

Statement 5
Here's an example of calling the table function, converting to a SQL dataset, assigning it to a cursor variable, and then returning that via the function. This function could then be invoked from a host environment, say a Java program, and the data will be consumed. That Java or UI developer will have no idea how the data set was constructed, and why should they care?
Return Cursor Variable to Dataset
```
CREATE OR REPLACE FUNCTION lotsa_names_cv (  
   base_name_in   IN   VARCHAR2  
 , count_in       IN   INTEGER  
)  
   RETURN sys_refcursor  
IS  
   retval sys_refcursor;  
BEGIN  
   OPEN retval FOR  
      SELECT COLUMN_VALUE  
        FROM TABLE ( lotsa_names ( base_name_in, count_in )) names;  
  
   RETURN retval;  
END lotsa_names_cv; 
```
```
Function created.
```

MY_ALIAS
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1
Steven 1

Statement 6

DECLARE 
   l_names_cur sys_refcursor; 
   l_name VARCHAR2 ( 32767 ); 
BEGIN 
   l_names_cur := lotsa_names_cv ( 'Steven', 100 ); 
 
   LOOP 
      FETCH l_names_cur INTO l_name; 
 
      EXIT WHEN l_names_cur%NOTFOUND; 
      DBMS_OUTPUT.put_line ( 'Name = ' || l_name ); 
   END LOOP; 
 
   CLOSE l_names_cur; 
END;

Name = Steven 1
Name = Steven 2
Name = Steven 3
Name = Steven 4
Name = Steven 5
Name = Steven 6
Name = Steven 7
Name = Steven 8
Name = Steven 9
Name = Steven 10
Name = Steven 11
Name = Steven 12
Name = Steven 13
Name = Steven 14
Name = Steven 15
Name = Steven 16
Name = Steven 17
Name = Steven 18
Name = Steven 19
Name = Steven 20
Name = Steven 21
Name = Steven 22
Name = Steven 23
Name = Steven 24
Name = Steven 25
Name = Steven 26
Name = Steven 27
Name = Steven 28
Name = Steven 29
Name = Steven 30
Name = Steven 31
Name = Steven 32
Name = Steven 33
Name = Steven 34
Name = Steven 35
Name = Steven 36
Name = Steven 37
Name = Steven 38
Name = Steven 39
Name = Steven 40
Name = Steven 41
Name = Steven 42
Name = Steven 43
Name = Steven 44
Name = Steven 45
Name = Steven 46
Name = Steven 47
Name = Steven 48
Name = Steven 49
Name = Steven 50
Name = Steven 51
Name = Steven 52
Name = Steven 53
Name = Steven 54
Name = Steven 55
Name = Steven 56
Name = Steven 57
Name = Steven 58
Name = Steven 59
Name = Steven 60
Name = Steven 61
Name = Steven 62
Name = Steven 63
Name = Steven 64
Name = Steven 65
Name = Steven 66
Name = Steven 67
Name = Steven 68
Name = Steven 69
Name = Steven 70
Name = Steven 71
Name = Steven 72
Name = Steven 73
Name = Steven 74
Name = Steven 75
Name = Steven 76
Name = Steven 77
Name = Steven 78
Name = Steven 79
Name = Steven 80
Name = Steven 81
Name = Steven 82
Name = Steven 83
Name = Steven 84
Name = Steven 85
Name = Steven 86
Name = Steven 87
Name = Steven 88
Name = Steven 89
Name = Steven 90
Name = Steven 91
Name = Steven 92
Name = Steven 93
Name = Steven 94
Name = Steven 95
Name = Steven 96
Name = Steven 97
Name = Steven 98
Name = Steven 99
Name = Steven 100

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database