?
Solved

[ORACLE] XMLTYPE.createXML .... Problem with local proxy

Posted on 2012-09-06
2
Medium Priority
?
1,077 Views
Last Modified: 2012-09-08
Hello,

i am trying to retrieve some XML data of a webpage and i ran into a problem.

Here is my code:

CREATE TABLE xml_data_tab ( xml_data xmltype );

CREATE TABLE url_tab
(
  URL_NAME VARCHAR2(100),
  URL      SYS.URIType
);

INSERT INTO url_tab VALUES
('This is a test URL',
sys.UriFactory.getUri('http://www.bsi.si/_data/tecajnice/dtecbs.xml')
);

Open in new window


So far so good but  after i execute this :


INSERT INTO ANDREJV.xml_data_tab
SELECT sys.XMLTYPE.createXML(u.url.getClob()) FROM ANDREJV.url_tab u;

Open in new window


I get:

Error report:
SQL Error: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-12535: TNS:operation timed out
ORA-06512: at "SYS.HTTPURITYPE", line 34
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.

Open in new window


We are using proxy to connect trough it to internet....i am 99% sure there is a problem. Could someone help mw with this?

How to describe a proxy ? (with ip,port,user name, password

Thank you!
0
Comment
Question by:Caruso_eu
  • 2
2 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 38377517
first, let me suggest a simpler way of generating the XMLTYPE from the uri value.

rather than getclob and then converting to xmltype, just call getxml

INSERT INTO xml_data_tab SELECT u.url.getxml() FROM url_tab u;


but.. that won't really work because the uri functions don't support proxy authentication.
you'll have to invoke utl_http directly for that.

here's a small example that shows how to do the authentication.
You'll probably want to change the dbms_output to something that constructs a clob and after the loop creates the xmltype

(basically the opposite of what I just suggested for sql)


DECLARE
    v_request    UTL_HTTP.req;
    v_response   UTL_HTTP.resp;
    v_text       VARCHAR2(32767);
BEGIN
    UTL_HTTP.set_proxy('YOURPROXY:1234', NULL);

    v_request   := UTL_HTTP.begin_request('http://www.bsi.si/_data/tecajnice/dtecbs.xml');

    UTL_HTTP.set_authentication(v_request, 'YOURPROXYUSER', 'YOURPROXYPASSWORD', 'Basic', TRUE);

    UTL_HTTP.set_header(v_request, 'User-Agent', 'Mozilla/4.0');
    v_response  := UTL_HTTP.get_response(v_request);

    BEGIN
        LOOP
            UTL_HTTP.read_line(v_response, v_text, TRUE);
            DBMS_OUTPUT.put_line(v_text);
        END LOOP;
    EXCEPTION
        WHEN UTL_HTTP.end_of_body
        THEN
            NULL;
    END;


    UTL_HTTP.end_response(v_response);
END;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38377553
here's a more complete example that does the xml construction and insert


DECLARE
    v_request    UTL_HTTP.req;
    v_response   UTL_HTTP.resp;
    v_text       VARCHAR2(32767);
BEGIN
    UTL_HTTP.set_proxy('yourproxy:1234', NULL);

    v_request   := UTL_HTTP.begin_request('http://www.bsi.si/_data/tecajnice/dtecbs.xml');

    UTL_HTTP.set_authentication(v_request, 'YOURPROXYUSER', 'YOURPROXYPASSWORD', 'Basic', TRUE);

    UTL_HTTP.set_header(v_request, 'User-Agent', 'Mozilla/4.0');
    v_response  := UTL_HTTP.get_response(v_request);

    DBMS_LOB.createtemporary(v_clob, TRUE);

    BEGIN
        LOOP
            UTL_HTTP.read_text(v_response, v_text, 32767);
            DBMS_LOB.writeappend(v_clob, LENGTH(v_text), v_text);
        END LOOP;
    EXCEPTION
        WHEN UTL_HTTP.end_of_body
        THEN
            UTL_HTTP.end_response(v_response);
    END;

    INSERT INTO xml_data_tab
         VALUES (xmltype(v_clob));

    DBMS_LOB.freetemporary(v_clob);
END;
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question