Hi All,
I use the following WITH clause as an SQL statement. It works without an error. However, when I try to run it in PL/SQL block. I get the ORA-32047 error. How can I run it in PL/SQL without using dynamic SQL? Also, do you know why PL/SQL doesn't support recursive WITH clause?
I use Oracle 19c.
declare
arr sys.ODCINumberList;
begin
WITH
Q_REC(ID, QRESULT)
AS
(
SELECT 1 ID, QRESULT
FROM (SELECT JSON_OBJECT(KEY 'qresult' VALUE '1') jdata FROM DUAL),
JSON_TABLE(
jdata, '$'
COLUMNS(
QRESULT varchar2(50) path '$.qresult'
)
) T
UNION ALL
SELECT (Q.ID+1), Q.QRESULT
FROM Q_REC Q
WHERE Q.ID < 10
)
CYCLE ID SET CYCLE TO 1 DEFAULT 0
SELECT id bulk collect into arr
FROM Q_REC;
end;
The following can work.
declare
arr sys.ODCINumberList;
begin
execute immediate q'[
WITH
Q_REC(ID, QRESULT)
AS
(
SELECT 1 ID, QRESULT
FROM (SELECT JSON_OBJECT(KEY 'qresult' VALUE '1') jdata FROM DUAL),
JSON_TABLE(
jdata, '$'
COLUMNS(
QRESULT varchar2(50) path '$.qresult'
)
) T
UNION ALL
SELECT (Q.ID+1), Q.QRESULT
FROM Q_REC Q
WHERE Q.ID < 10
)
CYCLE ID SET CYCLE TO 1 DEFAULT 0
SELECT id
FROM Q_REC]' bulk collect into arr;
end;
Thanks