oracle11g - use of with clause in stored procedure -

oracle11g - use of with clause in stored procedure -

create or replace procedure proc begin declare time_to_stay number(3):=90 date_partitions (select dbms_xmlgen.getxmltype(' select p.table_owner, p.table_name, p.high_value all_part_key_columns k, all_tab_cols c, all_tab_partitions p k.owner = c.owner , k.column_name = c.column_name , k.name = c.table_name , k.owner = p.table_owner , k.name = p.table_name , (c.data_type = ''date'' or c.data_type ''timestamp%'')') xml dual) select x.* date_partitions p, xmltable('/rowset/row' passing p.xml columns table_owner varchar2(30) path '/row/table_owner', table_name varchar2(30) path '/row/table_name', high_value varchar2(30) path '/row/high_value' ) x to_date(substr(x.high_value, instr(high_value, '''')+2, 19), 'yyyy-mm-dd hh24:mi:ss') <= sysdate-time_to_stay end;

when execute code procedure gets created. not work. when seek compile procedure next error. error(7,1): pls-00103: encountered symbol "with" when expecting 1 of following: * & = - + ; < / > @ in mod remainder not rem <> or != or ~= >= <= <> , or like2 like4 likec between || multiset fellow member submultiset ...

if execute code begin section, works fine me.

you've made few typos:

namely:

your added

declare. not needed stored procedures.

your time_to_stay needed before begin block.

missing semi colon on time_to_stay.

you have have loop in there sql can homecoming 2+ rows.

here's illustration outputs info buffer.

sql> create or replace procedure proc 2 3 time_to_stay number(3):=90; 4 begin 5 r_tab in (with date_partitions 6 (select dbms_xmlgen.getxmltype(' 7 select p.table_owner, 8 p.table_name, 9 p.high_value 10 all_part_key_columns k, 11 all_tab_cols c, 12 all_tab_partitions p 13 k.owner = c.owner 14 , k.column_name = c.column_name 15 , k.name = c.table_name 16 , k.owner = p.table_owner 17 , k.name = p.table_name 18 , (c.data_type = ''date'' or 19 c.data_type ''timestamp%'')') xml 20 dual) 21 select x.* 22 date_partitions p, 23 xmltable('/rowset/row' passing p.xml columns table_owner varchar2(30) path 24 '/row/table_owner', table_name varchar2(30) path '/row/table_name', 25 high_value varchar2(30) path '/row/high_value') x 26 to_date(substr(x.high_value, instr(high_value, '''') + 2, 19), 27 'yyyy-mm-dd hh24:mi:ss') <= sysdate - time_to_stay) 28 loop 29 dbms_output.put_line(r_tab.table_owner||','||r_tab.table_name||','||r_tab.high_value); 30 end loop; 31 end; 32 / procedure created.

oracle11g