Not really a question, more an observation.
Starting with version 12.1 you can declare a function in the WITH clause of a SELECT statement which is a precious thing for a DBA having to deal with LONG columns in Oracle dictionary like in the following query (meaningless just an example, "search_condition" is of LONG datatype):
SQL> With
2 function cons_search (oname varchar2, cname varchar2) return varchar2
3 is
4 def varchar2(32760) := '';
5 begin
6 select search_condition into def from dba_constraints
7 where owner=oname and constraint_name=cname;
8 return def;
9 end;
10 select 'search: '||cons_search(user,constraint_name) s
11 from user_constraints
12 where search_condition is not null
13 /
S
---------------------------------------------------------------------------------
search: "REC_UNIT_ID" IS NOT NULL
search: "ITEM_ID" IS NOT NULL
search: "REC_UNIT_ID" IS NOT NULL
search: "ORIGIN" IS NOT NULL
search: "C5" IS NOT NULL
search: "MODULE_ID" IS NOT NULL
search: "OBJECT_TYPE" IS NOT NULL
search: "OBJECT_ID" IS NOT NULL
So far so good.
Now we want to do it in PL/SQL in a cursor loop:
SQL> begin
2 for rec in (
3 With
4 function cons_search (oname varchar2, cname varchar2) return varchar2
5 is
6 def varchar2(32760) := '';
7 begin
8 select search_condition into def from dba_constraints
9 where owner=oname and constraint_name=cname;
10 return def;
11 end;
12 select 'search: '||cons_search(user,constraint_name)
13 from user_constraints
14 where search_condition is not null
15 ) loop
16 dbms_output.put_line(rec.s);
17 end loop;
18 end;
19 /
function cons_search (oname varchar2, cname varchar2) return varchar2
*
ERROR at line 4:
ORA-06550: line 4, column 12:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 30:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop
or explicitly declaring a cursor:
SQL> declare
2 cursor c is
3 With
4 function cons_search (oname varchar2, cname varchar2) return varchar2
5 is
6 def varchar2(32760) := '';
7 begin
8 select search_condition into def from dba_constraints
9 where owner=oname and constraint_name=cname;
10 return def;
11 end;
12 select 'search: '||cons_search(user,constraint_name) s
13 from user_constraints
14 where search_condition is not null;
15 begin
16 for rec in c loop
17 dbms_output.put_line(rec.s);
18 end loop;
19 end;
20 /
function cons_search (oname varchar2, cname varchar2) return varchar2
*
ERROR at line 4:
ORA-06550: line 4, column 12:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "SELECT"
BUT using a string and a manual cursor loop, it works!!!
SQL> declare
2 res varchar2(32767);
3 str varchar2(1000) :=
4 'With
5 function cons_search (oname varchar2, cname varchar2) return varchar2
6 is
7 def varchar2(32760) := '''';
8 begin
9 select search_condition into def from dba_constraints
10 where owner=oname and constraint_name=cname;
11 return def;
12 end;
13 select ''search: ''||cons_search(user,constraint_name) s
14 from user_constraints
15 where search_condition is not null';
16 c sys_refcursor;
17 begin
18 open c for str;
19 loop
20 fetch c into res;
21 exit when c%notfound;
22 dbms_output.put_line(res);
23 end loop;
24 end;
25 /
search: "OBJECT_ID" IS NOT NULL
search: "OBJECT_TYPE" IS NOT NULL
search: "MODULE_ID" IS NOT NULL
search: "C5" IS NOT NULL
search: "ORIGIN" IS NOT NULL
search: "REC_UNIT_ID" IS NOT NULL
search: "ITEM_ID" IS NOT NULL
search: "REC_UNIT_ID" IS NOT NULL
PL/SQL procedure successfully completed.
Well, when I say it works, it sometimes works and sometimes you get the error:
ORA-32034: unsupported use of WITH clause
which explains why this syntax fails but I'm surprised it does as I thought Oracle merged SQL and PL/SQL engines in 9i, it seems clear I was wrong.
If anyone has some thought or experience to share about this feature, please do.
[Updated on: Sat, 16 January 2021 10:42]
Report message to a moderator