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

Your code won't compile because the internal assignment names don't match the parameter names. If you're going to edit your code for publishing please make sure it's still correct. — APC, Nov 16 '16 at 11:28

APC · Accepted Answer · 2016-11-16T11:47:46.523

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;
/

Thank you! Great reply, and helped me have a better idea on how to implement this! — Nuno Valente, Nov 16 '16 at 12:13

score 0 · Answer 2 · answered Nov 16 '16 at 11:17

CREATE OR REPLACE FUNCTION gen_nums (na NUMBER, nb NUMBER)
RETURN sys.DBMS_DEBUG_VC2COLL PIPELINED --sys.DBMS_DEBUG_VC2COLL an oracle provided collection type.
IS
    counter NUMBER;
BEGIN
    counter := na;
    WHILE (counter <= nb)
    LOOP
        PIPE ROW (counter);
        counter := counter + 1;
    END LOOP;
    RETURN;
END gen_nums;

Oracle Pipelined function

2 Answers2

Linked