Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
blog oracle
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;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.