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!
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.