Re: throwing exception in parallel pipelined table function

From: Frank Bergemann <FBergemann_at_web.de>
Date: Mon, 12 Dec 2011 03:46:10 -0800 (PST)
Message-ID: <329606d2-4803-47cb-aef2-3f2b7da968fd_at_f11g2000yql.googlegroups.com>

On Dec 12, 11:56 am, Frank Bergemann <FBergem..._at_web.de> wrote:
> On Dec 8, 6:42 pm, Frank Bergemann <FBergem..._at_web.de> wrote:
>
> > what happens to such?
> > In my application it seems, it is catched by the main thread .
> > But i wonder how this can work if MULTIPLE parallel pipelined table
> > functions raise exception at different location of the code - e.g. for
> > user abort of main session(?!)
>
> > - many thanks!
>
> > regards,
> > Frank
>
> I found it out myself meanwhile
> In case of MULTIPLE parallel sessions started via pipelined table
> functions, there is given a dedicated exception to the main thread:
> ORA-12801: error signaled in parallel query server <string>
> ... instead of the concrete exception(s) raised by the individual
> worker thread(s).
>
> regards,
> Frank

Here's a test script, if someone wants to reproduce - pls. see below. Just change the oracle hint from
  /*+ parallel(test_input,3) */
to
  /*+ parallel(test_input,1) */

... to see the difference.

regards,
Frank

------------------ snip
--------------------------------------------------------------

drop table test_input;

create table test_input (a number);

alter table test_input parallel 3;

create or replace package test_pkg
as
 type rec is record (
   sid number,
   a number);

 type tab is table of rec;

 type data_cur is ref cursor return rec;

 function tableFunc(
 cur in data_cur)
 return tab pipelined
 parallel_enable(partition cur by any);

 procedure do;
end;
/

create or replace package body test_pkg
as
 function tableFunc(
 cur in data_cur)
 return tab pipelined
 parallel_enable(partition cur by any)

 is
  rowdata rec;
  sid number;
 begin
   select userenv('SID') into sid from dual;    dbms_output.put_line('hello from worker thread #' || sid);    rowdata.a := 10;
   rowdata.sid := sid;
   pipe row(rowdata);

  raise ROWTYPE_MISMATCH ;

 end;

 procedure do
 is
  data tab;
  sid number;
 begin
   sid := userenv('sessionid');
   dbms_output.put_line('hello from main thread #' ||sid);    for i in 1..100 loop
    insert into test_input(a) values(i);   end loop;

   select * bulk collect into data from table(tableFunc    (CURSOR(select /*+ parallel(test_input,3) */ * from test_input)));

   for i in data.first..data.last loop

      dbms_output.put_line('got a/sid = ' || data(i).a || '/' || data(i).sid);

   end loop;
 exception when others then
   dbms_output.put_line('!!! Exception:' || SQLERRM);  end;
end;
/

set serveroutput on
truncate table test_input;
declare
begin
 test_pkg.do;
end;
/

------------------ snap
--------------------------------------------------------------

Received on Mon Dec 12 2011 - 05:46:10 CST