PLSQL pipelined function to return a list

Question

I'm trying to create a function to get a list of values from my database. After some researches I found that I need to use the PIPELINE function and I found some examples. I did my function but I somehow got 2 errors that I don't understand.

Here's my code :

CREATE OR REPLACE TYPE LISTE_VALUES AS TABLE OF VARCHAR2(2000);
/    

CREATE OR REPLACE FUNCTION F_GET_VAL(
        PI_1 IN VARCHAR2,
        PI_2 IN NUMBER,
        PI_3 IN VARCHAR2)
    RETURN LISTE_VALUES PIPELINED

    IS
        W_ROW_COUNT NUMBER := 0;

    BEGIN

        FOR CUR IN (SELECT VALUE FROM TABLE 
                    WHERE ...
                      ...

                    )

        LOOP
            PIPE ROW (CUR);
            W_ROUNT_COUNT := W_ROW_COUNT + 1;
        END LOOP;

        DBMS_OUTPUT.PUT_LINE('There were '
                            || W_ROW_COUNT
                            || ' rows selected' );

    END F_GET_VAL;
    /

And These are the errors I get :

[Error] PLS-00382 : PLS-00382: expression is of wrong type (at the line : PIPE ROW (CUR);)

[Error] PLS-00201 : PLS-00201: identifier 'W_ROUNT_COUNT' must be declared

(at the line : W_ROUNT_COUNT := W_ROW_COUNT + 1;)

For the first error I triple checked and VALUE in my table has a type VARCHAR2(2000), exactly as I declared my type at the beginning (a table of VARCHAR2(2000)).

And for the second, I don't understand because I declared the variable W_ROW_COUNT in my IS statement.

If someone could help me it would be nice ! Thanks

Answer 1

A PIPE ROW can be created for a single row and not the cursor's name variable, which contains the entire recordset.

Just use

PIPE ROW ( cur.value );

instead of PIPE ROW ( cur );

You may also store the query output into a collection and then pipe each element.

Regarding the error due to W_ROW_COUNT, it is a typo. You have wrongly used it as W_ROUNT_COUNT while adding it.

Demo