I presented “Write Less (Code) With More (Oracle 12c New Features)” yesterday at OGh Tech Experience 2017.
One of the features I talked about was PL/SQL in the WITH Clause. One of the restrictions of this feature is that you cannot embed a static SQL query, that contains PL/SQL in the WITH clause, in PL/SQL (see the section PL/SQL in SQL in PL/SQL in this post).
I was asked, regarding this restriction, if it’s possible to embed in PL/SQL a static “regular” SQL query, that selects from a view, that contains PL/SQL in the WITH clause. The answer is yes, since the restriction is only syntactic.
Using the same example from the original post:
create view people_with_dates_v as with function is_date(i_info in varchar2) return number as l_date date; begin if i_info is null then return 0; else l_date := to_date(i_info, 'dd/mm/yyyy'); return 1; end if; exception when others then return 0; end; select p.* from people p where is_date(p.general_info) = 1; / View created.
create or replace procedure show_date_people as begin for l_rec in ( select * from people_with_dates_v ) loop dbms_output.put_line(l_rec.person_id || ': ' || l_rec.first_name || ' ' || l_rec.last_name); end loop; end show_date_people; /
> exec show_date_people 102: Paul McCartney 202: Ella Fitzgerald 203: Etta James PL/SQL procedure successfully completed.