blog oracle

blob.sql

create or replace function clobToBlob( c IN CLOB ) RETURN BLOB is
  b     BLOB;
  warn  VARCHAR2(255);
  cs_id NUMBER := NLS_CHARSET_ID('WE8MSWIN1252');
  do    NUMBER := 1; -- dest offset 
  so    NUMBER := 1; -- src offset 
  lc    NUMBER := 0; -- lang context
BEGIN
   DBMS_LOB.createTemporary(b, TRUE );
   DBMS_LOB.CONVERTTOBLOB(b, UTL_URL.UNESCAPE(c), DBMS_LOB.LOBMAXSIZE, do, so, cs_id, lc, warn);
   RETURN b; 
END clobTocREATE OR REPLACE procedure C##ORIGEN.ejecutar(ip varchar2, url varchar, body varchar,  salida out blob)
is
x xmltype;
y xmltype;
begin
   with c1 as
(
select t.column_value f from
table(
split(
   body
   ,chr(38) ) ) t
),
c2 as
(
 select rownum r, split(f,'=') d from c1
), 
c3 as
(
select *
from
(
     select row_number() over (partition by r order by r) || r n,column_value v from c2, table(c2.d) c2r
)
pivot
(
   min(v)
   for n in (11,12,21,22)
)
)
select 
   toxmlform("21"),    toxmlform("22")   into x,y
from c3;    
select   UTL_COMPRESS.LZ_COMPRESS (
               xmlelement(root,
               x,   y,
               xmlelement(url,url),
                xmlelement(t,xmlagg(xmlelement(t2,table_name)))).getBlobVal(871)
   )  into salida from user_tab_columns;
end;
/Blob;

cREATE OR REPLACE procedure C##ORIGEN.ejecutar(ip varchar2, url varchar, body varchar,  salida out blob)
is
x xmltype;
y xmltype;
begin
   with c1 as
(
select t.column_value f from
table(
split(
   body
   ,chr(38) ) ) t
),
c2 as
(
 select rownum r, split(f,'=') d from c1
), 
c3 as
(
select *
from
(
     select row_number() over (partition by r order by r) || r n,column_value v from c2, table(c2.d) c2r
)
pivot
(
   min(v)
   for n in (11,12,21,22)
)
)
select 
   toxmlform("21"),    toxmlform("22")   into x,y
from c3;    
select   UTL_COMPRESS.LZ_COMPRESS (
               xmlelement(root,
               x,   y,
               xmlelement(url,url),
                xmlelement(t,xmlagg(xmlelement(t2,table_name)))).getBlobVal(871)
   )  into salida from user_tab_columns;
end;
/