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.