Problem with utl_http

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
Shakall
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

I'm using the UTL_http to connect to a WebService and exchange information with it, everything is OK, working very well, but from time to time to work to work by displaying the following error.



The displayed problem is the fact that there are many open connections, from what I studied Oracle automatically closes your connections, but in my code I am Using the UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT (HTTP_REQ, TRUE); where it maintains the connection, so I can send several data within a loop without having to open a connection each time, this is to send large amounts of data, now the doubt how to close the connection? Below is the access code below.
Thanks!

Select all

 
http_req := utl_http.begin_request(WW_URL, 'POST',utl_http.HTTP_VERSION_1_1); 
                        utl_http.set_persistent_conn_support(http_req,true); 
                        utl_http.set_body_charset(http_req, 'UTF-8'); 
                        UTL_HTTP.set_authentication(http_req, 'SA', 'GN5', 'Basic', TRUE ); 
                        utl_http.set_header(http_req, 'Content-Type', 'text/xml'); 
                        utl_http.set_header(http_req, 'Content-Length', length(WW_ENV)); 
                        utl_http.set_header(http_req, 'SOAPAction', 'setCliente'); 
                        utl_http.write_text(http_req,WW_ENV); 
 
                        http_resp := utl_http.get_response(http_req); 
 
                       IF (http_resp.status_code =utl_http.HTTP_OK ) THEN 
                              utl_http.read_text(http_resp, WW_ENV_RESP); 
                              utl_http.end_response(http_resp); 
                              WW_RESP := xmltype.createxml(WW_ENV_RESP); 
 
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

CLOSE_PERSISTENT_CONN
CLOSES A HTTP PERSISTENT CONNECTION IN THE CURRENT SESSION UTL_HTTP.CLOSE_PERSISTENT_CONN (Conn in Connection);
TBD

Select all

 
Example: Using SET_PERSISTENT_CONN_SUPPORT 
 
DECLARE 
  TYPE vc2_table IS TABLE OF VARCHAR2(256) INDEX BY binary_integer; 
  paths vc2_table; 
 
  PROCEDURE fetch_pages(paths IN vc2_table) AS 
    url_prefix VARCHAR2(256) := 'http://www.my-company.com/'; 
    req   utl_http.req; 
    resp  utl_http.resp; 
    data  VARCHAR2(1024); 
  BEGIN 
    FOR i IN 1..paths.count LOOP 
      req := utl_http.begin_request(url_prefix || paths(i)); 
 
      -- Use persistent connection except for the last request 
      IF (i < paths.count) THEN 
        utl_http.set_persistent_conn_support(req, TRUE); 
      END IF; 
   
      resp := utl_http.get_response(req); 
   
      BEGIN 
        LOOP 
          utl_http.read_text(resp, data); 
          -- do something with the data 
        END LOOP; 
      EXCEPTION 
        WHEN utl_http.end_of_body THEN 
          NULL; 
      END; 
      utl_http.end_response(resp); 
    END LOOP; 
  END; 
 
BEGIN 
  utl_http.set_persistent_conn_support(FALSE, 1); 
  paths(1) := '...'; 
  paths(2) := '...'; 
  ...    
  fetch_pages(paths); 
END; 
 
Shakall
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

In this example is it tugged my where you close the connection?
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

How to close?

Select all

utl_http.close_persistent_conn(conn IN connection); 
Where to close?

where you want .. now preferably has to be after the exchange of informations desired, perhaps the last thing to be done
Shakall
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

beleza, is that in your example of condigo does not use

Select all

utl_http.close_persistent_conn(conn IN connection); 
so I was in doubt of how you do this in the code shown.
Shakall
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

Friend sorry for ignorance but I have a doubt in command utl_http.close_persistent_conn (Conn in connection);
What I step of parameter "Conn in Connection"

Thanks!
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

Syntax


Parameters

Table 169-18 Close_Persistent_Conn procedure parameters
] Parameter Description

Conn

The HTTP Persistent Connection to close



is your http_req even

: LOL:
Shakall
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

So face when passing my http_req, the following error.

Select all

utl_htt.close_persistent_conn(http_req);

Select all

 
PLS-00306: wrong number or types of arguments in call to 'CLOSE_PERSISTENT_CONN' 
PLS/SQL: Statement ignored 
PLS-00201: identifier 'CONN' must be declared 
PL/SQL: Statement ignored 
PLS-00201: identifier 'CONN' must be declared 
PL/SQL: Statement ignored 
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

Who is 'Conn' in your program?
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest