Ggtcf

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

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