how to cancel Pipe row?-CodePudding

I have a pipeline function. If I catch an exception, I want to return no lines. Even if the exception occurs after the first utilisation of pipe row.

CREATE TYPE a_r IS OBJECT (a1 VARCHAR (1), a2 VARCHAR (1));

CREATE TYPE a_t IS TABLE OF a_r;

CREATE or replace FUNCTION get_a
    RETURN a_t
    PIPELINED
IS
BEGIN
    FOR c IN (SELECT '1' a , '2' b FROM DUAL)
    LOOP
        PIPE ROW (a_r(c.a,c.b));
    END LOOP;
    FOR a
            IN (SELECT 'a2' a,
                       'b' b
                  FROM DUAL)
        LOOP
            PIPE ROW (a_r(a.a,a.b));
        END LOOP;
exception
        WHEN VALUE_ERROR
        THEN
            DBMS_OUTPUT.put_line ('VALUE_ERROR');
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line ('other');
    
END;

select * from table(get_a())

In this example, Before the error ('ab' is not varchar2(1)) occurs, the first line is already piped. But I want nothing to be returned. Is there a way to write cancel what was piped in the exception block?

code

CodePudding user response：

No because the client may already have consumed the row.

You could code your function so that it fills a local collection with data and only at the end decides whether to iterate through the collection to actually return the rows. That will require more memory since you're materializing the entire result set before returning it and defeats some of the performance benefits of using pipelined table functions.

Something like this would appear to do what you want

CREATE or replace FUNCTION get_a
    RETURN a_t
    PIPELINED
IS
  l_a_t a_t := new a_t();
BEGIN
    FOR c IN (SELECT '1' a , '2' b FROM DUAL)
    LOOP
        l_a_t.extend;
        l_a_t( l_a_t.count ) := a_r(c.a,c.b);
    END LOOP;
    FOR a
            IN (SELECT 'a2' a,
                       'b' b
                  FROM DUAL)
        LOOP
            l_a_t.extend;
            l_a_t( l_a_t.count ) := a_r(a.a,a.b);
        END LOOP;
        
   for i in 1 .. l_a_t.count
   loop
     pipe row( l_a_t(i) );
   end loop;
exception
        WHEN VALUE_ERROR
        THEN
            DBMS_OUTPUT.put_line ('VALUE_ERROR');
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line ('other');
    
END;
/

See this dbfiddle