Home » SQL & PL/SQL » SQL & PL/SQL » Weird PL/SQL behavior with SQL statement containing a function in WITH clause (12.1 to 19.8)
Weird PL/SQL behavior with SQL statement containing a function in WITH clause [message #683451] Fri, 15 January 2021 13:39
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Previous Topic: After "Drop Materialized View" it's data continuing be shown
Next Topic: Regex Help
Goto Forum:
  


Current Time: Tue Apr 13 18:40:39 CDT 2021