CODEWITHSUNDEEP
sqloracle-databaseplsqlcursor
Morticia A. Addams
Morticia A. Addams

Reputation: 383

FETCH SYS_REFCURSOR with recursive WITH ORA-32042

I have table with this definition:

create table my_tab as
  select '1' p, '2' c from dual
  union
  select '1' p, '3' c from dual
  union
  select '4' p, '5' c from dual
  union
  select '4' p, '6' c from dual
  union
  select '4' p, '2' c from dual
  union
  select '7' p, '3' c from dual
  union
  select '8' p, '4' c from dual;

I two TYPEs (Тhis is just for information. I simplified the example as much as possible!):

TYPE TObj_OUT AS OBJECT(File_Name VARCHAR2(128),
                            File_Records NUMBER,
                            Session_ID NUMBER,
                            Instanse_ID NUMBER);
CREATE OR REPLACE type TObj_OUT_NT AS TABLE OF TObj_OUT;

Package that print content from SYS_REFCURSOR(return some dummy data just for example):

CREATE OR REPLACE FUNCTION print_info(ip_Select IN SYS_REFCURSOR
                                     ) RETURN TObj_OUT_NT PIPELINED AS
      TYPE TROW IS TABLE OF VARCHAR2(32767);
      v_rows    TROW;
   BEGIN
      LOOP
        FETCH ip_Select BULK COLLECT INTO v_rows LIMIT 100;
         FOR i IN 1 .. v_rows.COUNT LOOP
            DBMS_OUTPUT.put_line(v_rows(i));
         END LOOP;
         EXIT WHEN ip_Select%NOTFOUND;
      END LOOP;
      CLOSE ip_Select;
      PIPE ROW (TObj_OUT('bb', 0, 0, 5));
      RETURN;
END print_info;

I have tried this:

declare
v_cnt number(8);
my_cursor SYS_REFCURSOR;
begin

open my_cursor for with t1(p,
    c) as
     (select distinct null p, t.p as c
        from my_tab t
      union all
      select t.p, t.c
        from t1
        join my_tab t
          on t.p = t1.c)
    select p || c from t1; 

SELECT file_records
    into v_cnt
    FROM TABLE(
print_info(
-- cursor start
cursor(
-- FIRST QUERY
with t1(p,
    c) as
     (select distinct null p, t.p as c
        from my_tab t
      union all
      select t.p, t.c
        from t1
        join my_tab t
          on t.p = t1.c)
    select p || c from t1
--FIRST_QUERY END
--SECOND QUERY
--select p||c from my_tab
--SECONF QUERY END
)
-- cursor end
-- my_cursor
));
end;

When I execute block I receive this error:

ORA-32042: recursive WITH clause must reference itself directly in one of the UNION ALL branches

ORA-06512: at "PRINT_INFO", line 9

ORA-06512: at line 17

BUT when i comment code from cursor start to cursor end

-- cursor start
cursor(with t1(p,
    c) as
     (select distinct null p, t.p as c
        from my_tab t
      union all
      select t.p, t.c
        from t1
        join my_tab t
          on t.p = t1.c)
    select p || c from t1)
-- cursor end

and uncomment line

-- my_cursor

It works fine! If comment FIRST QUERY and uncomment SECOND QUERY It works fine again! I can't understand where is the problem!

Upvotes: 1

Views: 423

Answers (1)

Dr Y Wit
Dr Y Wit

Reputation: 2020

It's a bug with cursor expressions + ANSI -> native transformation.

Your case may be simplified just to one SQL statement.

SQL> select cursor(with t1(p, c) as (select distinct null p, t.p as c
  2                                    from my_tab t
  3                                  union all
  4                                  select t.p, t.c
  5                                    from t1
  6                                    join my_tab t
  7                                      on t.p = t1.c)
  8           select p || c from t1) c from dual;
select cursor(with t1(p, c) as (select distinct null p, t.p as c
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-32042: recursive WITH clause must reference itself directly in one of the
UNION ALL branches


SQL> select * from table(dbms_xplan.display_cursor(format => 'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select cursor(with t1(p, c) as (select distinct null p, t.p as c
                           from my_tab t
 union all                                 select t.p, t.c
                     from t1                                   join
my_tab t                                     on t.p = t1.c)
select p || c from t1) c from dual

Plan hash value: 956860371

------------------------------------------------------------
| Id  | Operation                                 | Name   |
------------------------------------------------------------
|   0 | SELECT STATEMENT                          |        |
|   1 |  VIEW                                     |        |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|        |
|   3 |    SORT UNIQUE                            |        |
|   4 |     TABLE ACCESS FULL                     | MY_TAB |
|   5 |    VIEW                                   |        |
|   6 |     HASH JOIN                             |        |
|   7 |      RECURSIVE WITH PUMP                  |        |
|   8 |      TABLE ACCESS FULL                    | MY_TAB |
|   9 |  FAST DUAL                                |        |
------------------------------------------------------------


26 rows selected.

SQL> select cursor(with t1(p, c) as (select distinct null p, t.p as c
  2                                    from my_tab t
  3                                  union all
  4                                  select t.p, t.c
  5                                    from t1, my_tab t
  6                                   where t.p = t1.c)
  7           select p || c from t1) c from dual;

C
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

P|
--
1
8
7
4
12
13
42
45
46
73
84
42
45
46

14 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(format => 'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select cursor(with t1(p, c) as (select distinct null p, t.p as c
                           from my_tab t
 union all                                 select t.p, t.c
                     from t1, my_tab t
where t.p = t1.c)          select p || c from t1) c from dual

Plan hash value: 2529699678

------------------------------------------------------------
| Id  | Operation                                 | Name   |
------------------------------------------------------------
|   0 | SELECT STATEMENT                          |        |
|   1 |  VIEW                                     |        |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|        |
|   3 |    SORT UNIQUE                            |        |
|   4 |     TABLE ACCESS FULL                     | MY_TAB |
|   5 |    HASH JOIN                              |        |
|   6 |     RECURSIVE WITH PUMP                   |        |
|   7 |     TABLE ACCESS FULL                     | MY_TAB |
|   8 |  FAST DUAL                                |        |
------------------------------------------------------------


24 rows selected.

So first query (with ANSI join syntax) failed however second one (with native join syntax) completed successfully.

The difference in query plans is VIEW operation for the first query (ID = 5). It informs us that inline view has been created during query transformation.

Let's have a look at transformed queries in trace file for event 10053.

select cursor(with "T1"("P", "C")
              as((select "from$_subquery$_004"."QCSJ_C000000000400001_2" "P",
                         "from$_subquery$_004"."QCSJ_C000000000400003_3" "C"
                    from (select "T1"."P" "QCSJ_C000000000400000",
                                 "T1"."C" "QCSJ_C000000000400002",
                                 "T"."P"  "QCSJ_C000000000400001_2",
                                 "T"."C"  "QCSJ_C000000000400003_3"
                            from "T1" "T1", "MY_TAB" "T"
                           where "T"."P" = "T1"."C") "from$_subquery$_004")
                 union all (select distinct null "P", "T"."P" "C"
                    from "MY_TAB" "T")) select "T1"."P" || "T1"."C"
              "P||C" from "T1" "T1") "C"
  from "SYS"."DUAL" "DUAL"

select cursor(with "T1"("P", "C")
              as((select "T"."P" "P", "T"."C" "C"
                    from "T1" "T1", "MY_TAB" "T"
                   where "T"."P" = "T1"."C") union all
                 (select distinct null "P", "T"."P" "C"
                    from "MY_TAB" "T")) select "T1"."P" || "T1"."C"
              "P||C" from "T1" "T1") "C"
  from "SYS"."DUAL" "DUAL"

You see that join has been transformed in the first query into additional inline view with where clause and if you try to run first query it fails with ORA-32042.

Upvotes: 1

Related Questions