Access a webservice with plsql

[fabdepaula]

Good afternoon.

I would like to know if any knows how I use a webservice service in Oracle.

I have a WebService that I created and is running on a Tomcat server, now I needed to access this webservice with my application in Oracle, I need to access through a PL function or it can be by Forms 6i .

Someone knows how to do this ??

[Shakall]

Hello, would you like to know if you could solve your problem of connecting to a WebService via PL / SQL?

[nightmare]

Look, I'vê never done anything like this but you probably need to use the UTL_Connection package to open a connection through Port 80 and then the Write method (I think the one you write) riding a SOAP envelope. Once again, otherwise SOAP is wrong will look like it;

Select all

<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="URN DO WEB SERVICE AQUI">  
   <soapenv:Header/>  
   <soapenv:Body>  
      <urn:NOME DO SERVICO AQUI soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">  
        SEU XML AQUI. 
      </urn:NOME DO SERVICO AQUI >  
   </soapenv:Body>  
</soapenv:Envelope>  

If no one answers your message, I will try to make a simple example at home and put to you.

You have some test WebService (a public)

Hugs

[rafaelfrocha]

Hello,

I have no example to give you, but I know you can do as follows:

1) Use Oracle JDeveloper to set up a Class of access to your webservice (it does almost all automatically).

2) Import the Access Java to Oracle. Search how to do using Load Java.

3) Create a stored procedure that accesses Java. Then you can use it both in the bank and Forms.


Rafael

[izaiascatelli]

The routine below allows you to read an HTML page

Select all

 
DECLARE 
  c  utl_tcp.connection;  -- TCP/IP connection to the Web server 
  ret_val pls_integer; 
BEGIN 
  c := utl_tcp.open_connection(remote_host =>'www.site.com.br', -- remote_host => 
                              remote_port =>  80, -- 
                              --charset     => 'US7ASCII', 
                              tx_timeout  => 5);  -- open connection --  charset     => 
 
  ret_val := utl_tcp.write_line(c, 'GET / HTTP/1.0');    -- send HTTP request 
  ret_val := utl_tcp.write_line(c); 
  BEGIN 
    LOOP 
      dbms_output.put_line(utl_tcp.get_line(c, TRUE));  -- read result 
    END LOOP; 
  EXCEPTION 
    WHEN utl_tcp.end_of_input THEN 
      NULL; -- end of input 
  END; 
  utl_tcp.close_connection(c); 
EXCEPTION WHEN OTHERS THEN 
   utl_tcp.close_connection(c); 
END; 

[Shakall]

only use UTL_http;

[rafaelfrocha]

I did not understand the example above!

Serve to work with SOAP ??

[Shakall]

It follows an example to connect to a WebService via SOAP, in my case this code is within a loop, as I am sending several notes to the webservice, so I put the True
option on the set_persistence, so that it keeps open connection, this need not form open it every cover note and the last note I put set_persistence to false, thus ending the process it automatically closes the connection.

the variable WW_ENV own envelope, to complete the SOAP message.

WW_XML, contains all the value of the XML notes that I am sending to the WebService, ie there on the WebService in the method set_notes has the input variable notes.

WW_URL possesses the address of the webservice.

http_req, possesses the call of webservice.

http_resp possesses the value of the web service response.

Select all

 
WW_ENV:='<?xml version="1.0" encoding="utf-8"?> 
              <S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns2="http://xml.apache.org/xml-soap"> 
                    <S:Body> 
                          <ns2:setNotas xmlns:ns2="http://Serv/"> 
                                <Cliente>1</Cliente> 
                                <Notas>'||WW_XML||'</Notas> 
                          </ns2:setNotas> 
                    </S:Body> 
              </S:Envelope>'; 
              begin 
                    http_req := utl_http.begin_request(WW_URL, 'POST',utl_http.HTTP_VERSION_1_1); 
                    utl_http.set_persistent_conn_support(http_req,true); 
----Se você setar como TRUE segnifica que a conexão ficará aberta, desta forma você pode acessar o webservice varias vezes sem fechar a conexão aumentando a performance, se for acessa-lo apenas 1 vez então set como false ele fecha a conexão automaticamente. 
                    utl_http.set_body_charset(http_req, 'UTF-8'); 
                    UTL_HTTP.set_authentication(http_req, 'login', 'senha', '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', 'setNotas'); 
                    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); 

[Shakall]

There is how to mount the SOAP package varies from WebService to WebService, in my case as I use a Java WebService, which is on a Tomcat server, so add the XMLNS tag: ns2 = "http://xml.apache.org/xml-soap"> in case .NET servers This tag is not necessary, but others are, then the best thing is to study the webservice and how to set up the correct envelope for it to work.

[e_muniz]

Shakall, sorry to resuscitate an old topic, your NFS sending code is missing any part ?? Like you. attaches the digital signature part ??

ATS.

Ernandes Muniz

[alexrsilva]

http://www.oracle-base.com/articles/10g/utl_dbws10g.php

[alemds]

Good morning ..
Does anyone have a complete example of sending electronic invoice and signature?

Thanks

[deniscezar]

Good afternoon to all

I need to send lots of service invoice and would like to know if anyone has already done something like I would like to send the batches through a screen in the forms or would like to do everything Through Oracle, today the São Paulo prefecture validates a TXT file of service bills but there are some cities that require sending through WebService does anyone have something as an example? thanks

[alexrsilva]

Do you want to consume a WebService with PL / SQL?
If this is searching for DBMS_WS that will answer you.

[victorhugomuniz]

You have to ride your xml that will send to your webservice dynamically so that your notes will be nodes in your xml .. this is the difficult part .. consuming the service will be much easier as you can see here above

[deniscezar]

thanks for the feedback !!!
Mount the XML for me would not be the worst part, I do not understand is how to make the communication and sending the XML through Oracle you would have some simple example of running? An example taking the XML file and sending to the webservice of the city hall? Did you get this with some city hall?

[alexrsilva]

No, I did not get to do with a city hall.
What I got to do was an implementation where I generated the XML and recorded in a directory.
After I was monitoring some directories to pick up the files and record in the bank.
and I already made a PL / SQL code to consume WS. Regarding the City Hall, it would be the endpoint that would change and the return you have to deal with.

[deniscezar]

Alex
My knowledge regarding WebService in Oracle is zero Do not you have something ready to pass me? Because it is easier on top of your making the changes I need !!!!
Thanks.

[natanclara]

Despite the time, I verified that no one has made available an Oracle Client. I have this one I did about 5 years ago and it worked all these years without problem. Of course the data is exchanged for examples. This WS is ready for HTTPS.

Select all

PROCEDURE SP_CONSOMEWS( lido in out varchar2, acpf in varchar2) 
is 
    req UtL_HTTP.REQ; 
    resp UTL_HTTP.RESP; 
    l_url varchar2(255) default  'https://ws.serve.com.br/webservices/servico.asmx'; 
    l_acao varchar2(255) default 'http://tempuri.org/autoriza'; 
    l_wallet_path varchar2(100); 
    envelope  VARCHAR2(32767); 
    doc xmltype; 
    docfilho xmltype; 
    doccampo varchar2(256); 
    docvalor varchar2(32767); 
begin 
    lido := null; 
    l_wallet_path := 'file:/opt/oracle/db/chave'; 
    UTL_HTTP.set_wallet(l_wallet_path,'senhadowaller'); 
    envelope := '<?xml version="1.0" encoding="utf-8"?>'|| 
                    '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">'|| 
                    '<soap:Body>'|| 
                    '<autoriza xmlns="http://tempuri.org/">'|| 
                    '<usuario>joao</usuario>'|| 
                    '<senha>noisnafaixa</senha>'|| 
                    '<cpf_cnpj>'||acpf||'</cpf_cnpj>'|| 
                    '</autoriza>'|| 
                    '</soap:Body>'|| 
                    '</soap:Envelope>'; 
 
    req := utl_http.begin_request(l_url, 'POST','HTTP/1.0'); 
    utl_http.set_header(req, 'Content-Type', 'text/xml'); 
    utl_http.set_header(req, 'Content-Length', length(trim( envelope ))); 
    utl_http.set_header(req, 'SOAPAction', l_acao); 
    utl_http.write_text(req, envelope); 
    resp := utl_http.get_response(req); 
    utl_http.read_text(resp, envelope); 
    utl_http.end_response(resp); 
    doc := xmltype.createxml(envelope); 
    doc := doc.extract('/soap:Envelope/soap:Body/child::node()', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'); 
    docfilho := doc.extract('/autorizaResponse', 'xmlns="http://tempuri.org/'); 
    if (docfilho is not null) then 
        doccampo := docfilho.extract('/autorizaResponse/AutorizaEmprestimoResult/child::text()', 
                                                'xmlns="http://tempuri.org/').getstringval(); 
    end if; 
    lido := doccampo; 
exception 
    when utl_http.end_of_body then 
        utl_http.end_response( resp ); 
end SP_CONSOMEWS;

[deniscezar]

Hello Natanclara,
You managed to sign your XML with plsql
How can you send an example?

For I have already researched a lot and I found nothing about tags

<DigestValue>
<SignatureValue>
<X509Certificate>

in PLSQL ....

Thanks