Oracle Pipelined function

Question

I'm trying to create a function which returns an object that can be used in the FROM Clause. According to research on the oracle documentation I've found that a PIPELINED function is what I need.

I have this code:

CREATE TYPE type_struct AS OBJECT
(
    i NUMBER
);

CREATE TYPE tp_struct AS TABLE OF type_struct;

CREATE OR REPLACE FUNCTION gen_nums (na NUMBER, nb NUMBER)
RETURN tp_struct PIPELINED
IS
    rec type_struct;
    counter NUMBER;
BEGIN
    counter := na;

    WHILE (counter <= nb)
    LOOP
        SELECT counter
        INTO rec
        FROM dual;

        counter := counter + 1;

        PIPE ROW (rec);
    END LOOP;

    RETURN;
END gen_nums;
/

The intended result is a table with records from 'na' to 'nb', both inclusive.

However I get this error when compiling the function:

ORA 00932 inconsistent datatypes expected udt got number

Answer 1

ORA 00932 inconsistent datatypes expected udt got number

You get this because your code assigns a scalar to the output type. You need to cast the variable to match the assignment target. So:

   SELECT type_struct(counter)
    INTO rec
    FROM dual;

You don't necessarily need a pipelined function. We can use table() with any function which returns a collection.

Here is a much simpler implementation, which requires only one UDT.

CREATE TYPE tp_numbers AS TABLE OF number;
/
CREATE OR REPLACE FUNCTION gen_nums (na NUMBER, nb NUMBER)
RETURN tp_numbers 
IS
    return_value tp_numbers ;
BEGIN
    SELECT (na + level) - 1 
    bulk collect    INTO return_value  
    FROM dual
    connect by level <= nb;

    RETURN return_value ;
END gen_nums;
/