script return error
v_xml := XMLTYPE(UTL_HTTP.REQUEST(URL => 'http://www.nbg.ge/rss.php'));
the CDATA length is 6227, how I can extract data from this XML
script return error
v_xml := XMLTYPE(UTL_HTTP.REQUEST(URL => 'http://www.nbg.ge/rss.php'));
the CDATA length is 6227, how I can extract data from this XML
Code to replicate this problem:
declare
v_xml xmltype;
begin
v_xml := XMLTYPE(UTL_HTTP.REQUEST(URL => 'http://www.nbg.ge/rss.php'));
end;
However there seems to be nothing wrong with the XML itself. If I put the URL in a browser and get the XML there. It will parse into an XMLTYPE.
declare
v_xml xmltype ;
begin
v_xml := xmltype('<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
<channel>
<title>RSS NBG Currency Rates</title>
.... rest of the xml ....
</channel>
</rss>
');
end;
Problem is probably with getting a long string from the request. The following code will take care of it reading the data in chunks of 2000.
declare
l_clob clob;
l_req utl_http.req;
l_resp utl_http.resp;
l_buffer varchar2(4096);
l_pos pls_integer;
l_xml xmltype;
begin
dbms_lob.createtemporary(lob_loc => l_clob, cache => true, dur => dbms_lob.call);
-- -----------------------------------
-- OPEN TEMPORARY LOB FOR READ / WRITE
-- -----------------------------------
dbms_lob.open(lob_loc => l_clob, open_mode => dbms_lob.lob_readwrite);
l_req := utl_http.begin_request('http://www.nbg.ge/rss.php');
utl_http.set_header(l_req, 'Content-Type', 'text/xml; charset=utf-8');
utl_http.set_header(l_req, 'User-Agent', 'Mozilla/4.0');
l_resp := utl_http.get_response(l_req);
begin
loop
utl_http.read_text(r => l_resp, data => l_buffer, len => 2000);
dbms_lob.writeappend(lob_loc => l_clob, amount => length(l_buffer), buffer => l_buffer);
end loop;
exception
when utl_http.end_of_body then
null;
when others then
raise;
end;
utl_http.end_response(l_resp);
l_xml := xmltype(l_clob);
end;