Re: PIPELINE FUNCTION varchar parameters no rows returned

<hooge007_at_gmail.com> a écrit dans le message de news: 1142996458.158891.270500_at_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
|

Once corrected the errors in your procedure and inconsistency in your post, this works with scott schema and emp table in 9.2.0.6

For your future posts, copy and paste a real execution.

Regards
Michel Cadot Received on Tue Mar 21 2006 - 23:42:08 CST