Hints and tricks for Oracle and other stuff...

procedure CreateXMLFromTable(ptabela varchar2) is
 v_plsql varchar2(4000);
 v_tabela varchar2(30) := upper(ptabela);
 v_statussend varchar2(1) := 'N';
begin

v_plsql := 'declare
  v_result clob := '''';
  v_resultrid xmltype;
begin
savepoint sp;
select xmlelement(
"XYZ", xmlagg(xmlelement(
  "ROWID", rowidtochar(r.rid))))
  ,
xmlelement("'v_tabela'", xmlagg(xmlelement("POST"
';
for r in
(select
'
      , xmlelement("'u.COLUMN_NAME'", '
   decode(u.DATA_TYPE,
    'NUMBER','to_char(r.'u.COLUMN_NAME',''FM99999999999999999990.00000'')',
    'DATE','to_char(r.'u.COLUMN_NAME',''dd.mm.yyyy hh24:mi:ss'')',
    'r.'u.COLUMN_NAME
   )')' polje, u.COLUMN_NAME
from user_tab_columns u
where u.TABLE_NAME = v_tabela
order by u.column_id) loop

if r.column_name = 'STATUSSEND' then
v_statussend := 'D';
end if;

if r.column_name <> 'RID' then
v_plsql := v_plsql r.polje;
end if;

end loop;
   
v_plsql := v_plsql '))).getclobval()
into v_resultrid, v_result
from 'v_tabela' r';
if v_statussend = 'D' then
v_plsql := v_plsql '
;
for r in (select
  chartorowid(extractvalue(value(x), ''ROWID'')) rid
from table(xmlsequence(extract(v_resultrid,''XYZ/ROWID''))) x) loop
  update 'v_tabela' set statusprepisa = 1 where rowid in r.rid;
end loop;';
else
v_plsql := v_plsql '
;';
end if;
/*v_plsql := v_plsql '
if instr(v_result,'''') > 0 then
insert into save_data
  (id, table, sourcexml, statussend)
values
  (prenos_produkcija_seq.nextval, '''v_tabela''', v_result, 0);
end if;';*/
v_plsql := v_plsql '
commit;
exception when others then
rollback to sp;
raise;
end;';
--htp.p(v_plsql);
execute immediate v_plsql;
end;