Home » SQL & PL/SQL » SQL & PL/SQL » Parallel Pipelined Funtion with MERGE returns ORA-00905: missing keyword
Parallel Pipelined Funtion with MERGE returns ORA-00905: missing keyword [message #267552] Thu, 13 September 2007 12:54 Go to next message
mmajerczyk
Messages: 2
Registered: September 2007
Location: Chicago, US
Junior Member
Hello,
I hope somebody can help me with that. I'm trying to use parallel pipelined function with merge statement inside, to merge data in parallel to a table.
When I try to do that the function is returning:

ORA-12801: error signaled in parallel query server P007
ORA-00905: missing keyword

If I remove parallelism (either by commenting out the parallel_enabled clause or removing parallel hint from the query on the pipelined function)
or if I leave intact parallelism, but instead I replace merge statement with insert/update, it works correctly.
So my assumption is that there is something wrong when using merge inside the parallel enabled pipelined function.

Please find below simplified code which can be used to reproduce the problem..

The oracle version:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

The exact error message:
SELECT *
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000
ORA-00905: missing keyword
ORA-06512: at "KCMXM47.TESTPKG", line 20


--THE CODE
set echo on
set feedback on

-- Version Number
SELECT * FROM v$version;

-- Drop Tables and Types
DROP TABLE t1;
DROP TABLE t2;
DROP TYPE typ_tbl;
DROP TYPE typ_obj;
DROP PACKAGE testpkg;

-- Create Types
CREATE OR REPLACE TYPE typ_obj IS OBJECT
(
C1 VARCHAR2(255),
C2 VARCHAR2(255)
)
/

CREATE OR REPLACE TYPE typ_tbl IS TABLE OF typ_obj
/

-- Create Source Table
CREATE TABLE t1
(
T1_C1 VARCHAR2(255),
T1_C2 VARCHAR2(255)
);

INSERT INTO t1
SELECT object_name || rownum t1_c1, object_type t1_c2
FROM dba_objects
WHERE rownum < 10000;

COMMIT;

-- Create Desyination Table
CREATE TABLE t2
(
T2_C1 VARCHAR2(255),
T2_C2 VARCHAR2(255)
);

-- Create Unique Index On Destination Table
CREATE UNIQUE INDEX t2_idx ON t2(t2_c1);

-- Create Package Specification
CREATE OR REPLACE PACKAGE testpkg
IS
TYPE typ_rec IS RECORD
(
C1 VARCHAR2(255),
C2 VARCHAR2(255)
);

TYPE typ_refcur IS REF CURSOR RETURN typ_rec;

FUNCTION loaddata
(p_refcur typ_refcur)
RETURN typ_tbl
PIPELINED
PARALLEL_ENABLE(PARTITION p_refcur BY ANY);

END testpkg;
/

-- Create Package Body
CREATE OR REPLACE PACKAGE BODY testpkg
IS
FUNCTION loaddata
(p_refcur typ_refcur)
RETURN typ_tbl
PIPELINED
PARALLEL_ENABLE(PARTITION p_refcur BY ANY)
IS
v_c1 VARCHAR2(255);
v_c2 VARCHAR2(255);
v_tbl typ_tbl;

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
LOOP
FETCH p_refcur INTO v_c1, v_c2;
EXIT WHEN p_refcur%NOTFOUND;

MERGE INTO t2
USING (
SELECT v_c1 c1, v_c2 c2
FROM dual
) t
ON (
t2.t2_c1 = t.c1
)
WHEN MATCHED THEN
UPDATE
SET t2.t2_c2 = t.c2
WHEN NOT MATCHED THEN
INSERT (
t2.t2_c1,
t2.t2_c2
)
VALUES
(
t.c1,
t.c2
);
-- INSERT INTO t2 VALUES (v_c1, v_c2);
COMMIT;

END LOOP;
CLOSE p_refcur;
RETURN;
END;
END testpkg;
/


-- Load Data
SELECT *
FROM TABLE(testpkg.loaddata
(CURSOR
(
SELECT /*+ parallel(t1) */ t1_c1, t1_c2
FROM t1
)
)
);
  • Attachment: testpkg.sql
    (Size: 2.46KB, Downloaded 277 times)
Re: Parallel Pipelined Funtion with MERGE returns ORA-00905: missing keyword [message #267554 is a reply to message #267552] Thu, 13 September 2007 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Parallel Pipelined Funtion with MERGE returns ORA-00905: missing keyword [message #268081 is a reply to message #267554] Mon, 17 September 2007 08:32 Go to previous messageGo to next message
mmajerczyk
Messages: 2
Registered: September 2007
Location: Chicago, US
Junior Member
Thank you. Please find below the formatted version:

set echo on
set feedback on

-- Version Number
SELECT * FROM v$version;

-- Drop Tables and Types
DROP TABLE t1;
DROP TABLE t2;
DROP TYPE typ_tbl;
DROP TYPE typ_obj;
DROP PACKAGE testpkg;

-- Create Types
CREATE OR REPLACE TYPE typ_obj IS OBJECT
(
  C1  VARCHAR2(255),
  C2  VARCHAR2(255)
)
/

CREATE OR REPLACE TYPE typ_tbl IS TABLE OF typ_obj
/

-- Create Source Table
CREATE TABLE t1
(
  T1_C1  VARCHAR2(255),
  T1_C2  VARCHAR2(255)
);

INSERT INTO t1
SELECT object_name || rownum t1_c1, object_type t1_c2
FROM   dba_objects
WHERE  rownum < 10000;

COMMIT;

-- Create Desyination Table
CREATE TABLE t2
(
  T2_C1  VARCHAR2(255),
  T2_C2  VARCHAR2(255)
);

-- Create Unique Index On Destination Table
CREATE UNIQUE INDEX t2_idx ON t2(t2_c1);

-- Create Package Specification
CREATE OR REPLACE PACKAGE testpkg
IS
   TYPE typ_rec IS RECORD
      (
       C1   VARCHAR2(255),
       C2   VARCHAR2(255)
      );

   TYPE typ_refcur IS REF CURSOR RETURN typ_rec;

   FUNCTION loaddata
              (p_refcur typ_refcur)
   RETURN   typ_tbl
   PIPELINED
   PARALLEL_ENABLE(PARTITION p_refcur BY ANY);

END testpkg;
/

-- Create Package Body
CREATE OR REPLACE PACKAGE BODY testpkg
IS
   FUNCTION loaddata
              (p_refcur typ_refcur)
   RETURN   typ_tbl
   PIPELINED
   PARALLEL_ENABLE(PARTITION p_refcur BY ANY)
   IS
      v_c1   VARCHAR2(255);
      v_c2   VARCHAR2(255);
      v_tbl  typ_tbl;

      PRAGMA AUTONOMOUS_TRANSACTION;

   BEGIN
      LOOP
         FETCH p_refcur INTO v_c1, v_c2;
           EXIT WHEN p_refcur%NOTFOUND;

         MERGE INTO t2
         USING (
                SELECT v_c1 c1, v_c2 c2
                FROM   dual
                ) t
         ON   (
               t2.t2_c1 = t.c1
               )
         WHEN MATCHED THEN
            UPDATE
               SET  t2.t2_c2 = t.c2
         WHEN NOT MATCHED THEN
            INSERT (
                    t2.t2_c1,
                    t2.t2_c2
                    )
            VALUES
                   (
                    t.c1,
                    t.c2
                    );
--      INSERT INTO t2 VALUES (v_c1, v_c2);
      COMMIT;

      END LOOP;
      CLOSE p_refcur;
      RETURN;
   END;
END testpkg;
/


-- Load Data
SELECT *
FROM   TABLE(testpkg.loaddata
          (CURSOR
             (
              SELECT /*+ parallel(t1) */ t1_c1, t1_c2
              FROM   t1
            )
         )
      );
Re: Parallel Pipelined Funtion with MERGE returns ORA-00905: missing keyword [message #268224 is a reply to message #267552] Tue, 18 September 2007 00:25 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

A space is appended at the first line of your code. Remove that one.
Re: Parallel Pipelined Funtion with MERGE returns ORA-00905: missing keyword [message #268236 is a reply to message #268224] Tue, 18 September 2007 00:52 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Can you be a bit clearer?
There are several blocks of code; please denote which 'first line' you are talking about.
Previous Topic: Request Size
Next Topic: Insert into openquery error
Goto Forum:
  


Current Time: Mon Dec 05 13:16:11 CST 2016

Total time taken to generate the page: 0.13827 seconds