table-function-demo

View All Scripts Login to Run Script

Script Name table-function-demo
Description Demo of using a table function in a WHERE clause.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor William Robertson
Created Tuesday January 17, 2017

Statement 1
Used sys.ku$_objnumset for convenience as it already exists, but any collection type will do as long as it is in scope for SQL. Typically this means a schema-level type created using CREATE OR REPLACE TYPE x AS TABLE OF y, although in some cases a type defined in a package specification can be used (depending on Oracle version and how you feel about temporary objects appearing in your schema).
Define a table function
```
create or replace function get_num
    ( p_length in integer )
    return sys.ku$_objnumset
is
    l_numlist sys.ku$_objnumset := sys.ku$_objnumset();
begin
    l_numlist.extend(p_length);

    for i in 1..p_length loop
        l_numlist(i) := i;
    end loop;

    return l_numlist;
end get_num;
```
```
Function created.
```

Statement 2

create table demo (id primary key, name not null unique)
as
select 1, 'Eeny' from dual union all
select 2, 'Meeny' from dual union all
select 3, 'Miny' from dual union all
select 4, 'Moe' from dual

Table created.

Statement 3

select column_value from table(get_num(3))


COLUMN_VALUE
1
2
3

3 rows selected.

COLUMN_VALUE
1
2
3

Statement 4

select * from demo d where d.id in (select column_value from table(get_num(2)) )


ID NAME
1 Eeny
2 Meeny

2 rows selected.

ID	NAME
1	Eeny
2	Meeny

Additional Information

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