Insert Into Temporary Table Via Parallel Pipelined Table Function Fails Without Error (Doc ID 1359879.1)

Last updated on JULY 17, 2017

Applies to:

PL/SQL - Version 10.2.0.1 to 10.2.0.5 [Release 10.2]
Information in this document applies to any platform.

Symptoms

Using parallel_enable with pipelined functions to insert into temporary table
results in null records / empty table in 10.2 and prior.

CODE SAMPLE TO REPRODUCE:

set serveroutput on;
DROP TYPE TestTableOfNumber_t;
CREATE OR REPLACE TYPE TestTableOfNumber_t is table of number;
/

DROP TYPE TestStatusList;
DROP TYPE TestStatusObj;

CREATE OR REPLACE TYPE TestStatusObj as object(
 sid number,
 ctr1 number,
 ctr2 number,
 ctr3 number);
/

CREATE OR REPLACE TYPE TestStatusList is table of TestStatusObj;
/

DROP TABLE TestTmpTable;
CREATE GLOBAL TEMPORARY TABLE TestTmpTable
  (value number) on commit preserve rows;

CREATE OR REPLACE PACKAGE test_pkg
AS
 TYPE TestStatusRec is record (
  sid number,
  ctr1 number,
  ctr2 number,
  ctr3 number);
 TYPE TestStatusTab is table of TestStatusRec;
FUNCTION FillTmpTable(id in varchar2)
  RETURN TestStatusRec;
FUNCTION ptf (p_cursor IN sys_refcursor)
  RETURN TestStatusList PIPELINED PARALLEL_ENABLE(PARTITION p_cursor BY any);
end;
/

CREATE OR REPLACE PACKAGE BODY test_pkg
AS
FUNCTION FillTmpTable(id in varchar2) RETURN TestStatusRec
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  result TestStatusRec;
  sid number;
  type ton is table of number;
  tids TestTableOfNumber_t := TestTableOfNumber_t();
  records number := 0;
BEGIN
  SELECT userenv('SID') INTO sid FROM dual;
  result.sid := sid;
  DELETE FROM TestTmpTable;
  FOR i in 1..100 LOOP
    tids.extend;
    tids(tids.last) := i;
  END LOOP;
  FORALL i in 1..tids.count
    INSERT INTO TestTmpTable (value) VALUES (tids(i));
  -- get number of records inserted
  records := sql%rowcount;
  result.ctr1 := records;
  -- retrieve again before commit
  SELECT count(*) INTO records FROM TestTmpTable;
  result.ctr2 := records;
  COMMIT;
  -- retrieve again after commit
  SELECT count(*) INTO records FROM TestTmpTable;
  result.ctr3 := records;
RETURN result;
END;

FUNCTION ptf (p_cursor IN sys_refcursor)
RETURN TestStatusList PIPELINED PARALLEL_ENABLE(PARTITION p_cursor BY any)
IS
  rec test_pkg.TestStatusRec;
  value number;
  sid number;
  ctr integer := 0;
BEGIN
  SELECT userenv('SID') INTO sid FROM dual;
  rec := FillTmpTable('IN PTF');
  LOOP
    FETCH p_cursor INTO value;
    EXIT WHEN p_cursor%NOTFOUND;
    ctr := ctr + 1;
  END LOOP;
  -- as a result i am only interested in the results of FillTmpTable():
  PIPE ROW (TestStatusObj(rec.sid, rec.ctr1, rec.ctr2, rec.ctr3));
RETURN;
END;
end;
/

DECLARE
  tons TestTableOfNumber_t;
  counts TestTableOfNumber_t;
  status test_pkg.TestStatusRec;
  statusList test_pkg.TestStatusTab;
BEGIN
  status := test_pkg.FillTmpTable('MAIN');
  dbms_output.put_line('main thread:'
   || ' sid #' || status.sid
   || ' / #' || status.ctr1 || ' inserted '
   || ' / #' || status.ctr2 || ' before commit'
   || ' / #' || status.ctr3 || ' after commit');

  SELECT value bulk collect INTO tons FROM TestTmpTable;

  SELECT * bulk collect
  INTO statusList
  FROM TABLE(test_pkg.ptf(CURSOR(select /*+ parallel(tab,2) */ value from TestTmpTable tab)));

 FOR i in 1..StatusList.count LOOP
  dbms_output.put_line('worker thread #' || i || ':'
    || ' sid #' || statusList(i).sid
    || ' / #' || statusList(i).ctr1 || ' inserted '
    || ' / #' || statusList(i).ctr2 || ' before commit'
    || ' / #' || statusList(i).ctr3 || ' after commit');
 END LOOP;
end;
/


Notable difference:  The output will differ on your system for the SID value.

OUTPUT (10.2.0.5 and prior)

main thread: sid #41 / #100 inserted / #100 before commit / #100 after commit
worker thread #1: sid #42 / #100 inserted / #0 before commit / #0 after commit
worker thread #2: sid #34 / #100 inserted / #0 before commit / #0 after commit

PL/SQL procedure successfully completed.


EXPECTED OUTPUT

main thread: sid #36 / #100 inserted / #100 before commit / #100 after commit
worker thread #1: sid #5 / #100 inserted / #100 before commit / #100 after commit
worker thread #2: sid #28 / #100 inserted / #100 before commit / #100 after commit

PL/SQL procedure successfully completed.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms