PIPELINE FUNCTION varchar parameters no rows returned

From: <hooge007_at_gmail.com>
Date: 21 Mar 2006 19:00:58 -0800
Message-ID: <1142996458.158891.270500@g10g2000cwb.googlegroups.com>

A monsterous problem beside setting ALTER SESSION SET CURSOR_SHARING=EXACT;
if you recieve no rows returned a function and are wondering why - this maybe it. In our environment we Oracle 9.2.0.5 and using VARCHARS in a pipeling function
DOES NOT EVALUATE! You have to use CHAR! This took me a hell of a long time to figure out ! It not in the literature. BEWARE ! I could not find this anywhere.

CREATE TYPE my_emp_rec AS OBJECT

       (id NUMBER(6), fname VARCHAR2(20));
/

CREATE TYPE my_emp_rec_table AS TABLE OF my_emp_rec;
/

CREATE or replace FUNCTION emp_name (name varchar2)

         RETURN my_emp_rec_table PIPELINED IS
v_id     number(6);
v_empname   VARCHAR2(200);
v_sal        number(6);
out_rec      my_emp_rec := my_emp_rec(NULL,NULL);
BEGIN
        SELECT distinct EMPLOYEE_ID,FIRST_NAME into v_id, v_fname
        FROM myemp where employee_name=name;

out_rec := my_emp_rec(v_id, v_fname);
PIPE ROW(out_rec);
RETURN;
END;
/

select * from table(myname('Mitra'));
no rows selected

However changing

CREATE or replace FUNCTION emp_name (name char) works !

select * from table(myname('Mitra'));

        ID FNAME

• --------------------

  84539 Mitra Received on Tue Mar 21 2006 - 21:00:58 CST