2 Replies Latest reply: Mar 11, 2010 2:18 AM by Billy~Verreynne RSS

    Using UTL_HTTP.GET_RESPONSE function (PL/SQL)

    373178
      Hello,
      I have a problem using the UTL_HTTP.GET_RESPONSE (URL, 'POST') function while I try to call a function that returns an XML;

      The function returning my XML is:
      FUNCTION MyFunction return XMLTYPE is
      begin
      return XMLTYPE('<PROVA>test</PROVA>');
      end MyFunction;

      To perform the http call I use this function:
      FUNCTION POST(URL VARCHAR2, DATA_IN CLOB) RETURN CLOB IS
      BEGIN
      DECLARE
      DATA_OUT CLOB;
      PIECE VARCHAR2(4000);
      AMT PLS_INTEGER := 4000;
      POS PLS_INTEGER := 1;
      HTTP_REQ UTL_HTTP.REQ;
      HTTP_RESP UTL_HTTP.RESP;

      BEGIN
      HTTP_REQ := UTL_HTTP.BEGIN_REQUEST (URL, 'POST');
      UTL_HTTP.SET_HEADER(HTTP_REQ, 'content-length', LENGTH(DATA_IN));

      LOOP
      DBMS_LOB.READ(DATA_IN,AMT,POS,PIECE);
      UTL_HTTP.WRITE_TEXT(HTTP_REQ, PIECE);
      EXIT WHEN AMT < 4000;
      POS := POS + AMT;
      AMT := 4000;
      END LOOP;

      HTTP_RESP := UTL_HTTP.GET_RESPONSE (HTTP_REQ);

      BEGIN

      LOOP
      UTL_HTTP.READ_TEXT(HTTP_RESP, PIECE);
      DATA_OUT := DATA_OUT || PIECE;
      END LOOP;

      EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN NULL;

      END;

      UTL_HTTP.END_RESPONSE (HTTP_RESP);
      RETURN DATA_OUT;

      END;
      END;


      The script pl/sql that calls the preceding function is:
      declare
      v_resp CLOB;
      v_url VARCHAR2(4000);
      begin
      v_url := 'http:// ... /meters.export_table.MyFunction'
      v_resp := POST(v_url, '-');

      end;

      After this call to my url, the variabile v_resp contains the following error message:
      "<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
      <HTML><HEAD>
      <TITLE>400 Bad Request</TITLE>
      </HEAD><BODY>
      <H1>Bad Request</H1>
      Your browser sent a request that this server could not understand.<P>
      mod_plsql: /pls/prjsi/meters.export_table.MyFunction HTTP-400 Missing '=' in query string or post form<P>
      <HR>
      <ADDRESS>Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server Server at websvil.aem.torino.it Port 80</ADDRESS>
      </BODY></HTML>"

      Do you know how I can get my XML? What is the problem in that call http for my function?

      Thanks
        • 1. Re: Using UTL_HTTP.GET_RESPONSE function (PL/SQL)
          704043
          Hello !

          I have not understand exactly what have you try to achieve with your code , but i thing there are few things misunderstood in your code ,
          so i'm posting this very basic but working example in hope that it will help you

          SQL> 
          SQL> 
          SQL> conn scott/tiger
          Connected.
          SQL> 
          SQL> 
          SQL> create or replace procedure http_test is
            2  begin
            3    htp.p('<PROVA>test</PROVA>'); 
            4  end http_test;
            5  /
          
          Procedure created.
          
          SQL> CREATE OR REPLACE function HTTP_POST return varchar2 is
            2  
            3    req  utl_http.req;
            4    resp utl_http.resp; 
            5    
            6    v_txt varchar2(1024);
            7    
            8  BEGIN
            9    
           10    req  := UTL_HTTP.begin_request ('http://localhost:7777/pls/my_utf8/http_test'
           11                                   ,'POST','HTTP/1.1');
           12    Utl_Http.Set_Authentication ( r => req, username => 'scott', password => 'tiger'
           13                                , scheme => 'Basic', for_proxy => false );
           14    resp := UTL_HTTP.get_response  (req);
           15    utl_http.read_text(resp,v_txt);
           16    utl_http.end_response(resp);
           17    return v_txt;
           18  END;
           19  /
          
          Function created.
          
          SQL> select http_post from dual;
          
          HTTP_POST
          --------------------------------------------------------------------------------
          <PROVA>test</PROVA>
          
          SQL> 
          T
          • 2. Re: Using UTL_HTTP.GET_RESPONSE function (PL/SQL)
            Billy~Verreynne
            f.bonavia@reply.it wrote:

            After this call to my url, the variabile v_resp contains the following error message:
            "<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
            <HTML><HEAD>
            <TITLE>400 Bad Request</TITLE>
            </HEAD><BODY>
            You have not formatted a valid http +POST+ command - thus the web server is unable to process it.

            I have posted sample code that does a +POST+ (a SOAP envelope to a web server) in message {message:id=4091246}. This should provide you with the basic template of how to construct a valid +POST+ using PL/SQL.

            Note that if the web service (or web server) that processes that URL expect name-values , you need to provide name-values. You can only submit raw XML (like in my sample code) to a web service that expects raw XML (e.g. a SOAP envelope). Else it needs to be a name-value pair.

            With a +GET+, you will supply the name-value pairs in the URL query string (part of the URL, itself). With a +POST+ the the name-values are written into the http header (not part of the actual URL itself). In this case, the name-value pairs have to be in the correct format. See this Wikipedia entry (http://en.wikipedia.org/wiki/POST_%28HTTP%29 ) for details.