Access a webservice with plsql

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
User avatar
fabdepaula
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Thu, 15 May 2008 8:53 pm
Location: Araraquara -SP

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
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

Hello, would you like to know if you could solve your problem of connecting to a WebService via PL / SQL?
nightmare
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 2
Joined: Fri, 15 Aug 2008 4:02 pm
Location: São Paulo - SP

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
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 45
Joined: Wed, 31 Oct 2007 9:30 am
Location: Uberlândia
Rafael Rocha

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
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 16
Joined: Tue, 17 May 2005 4:24 pm
Location: curitiba pr

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
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

only use UTL_http;
rafaelfrocha
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 45
Joined: Wed, 31 Oct 2007 9:30 am
Location: Uberlândia
Rafael Rocha

I did not understand the example above!

Serve to work with SOAP ??
Shakall
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

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
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 29
Joined: Wed, 13 Aug 2008 6:57 pm
Location: Blumenau - SC

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
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 19
Joined: Wed, 01 Jul 2009 10:35 am
Location: Belo Horizonte / MG

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
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 153
Joined: Tue, 27 May 2008 1:31 pm
Location: Rio de Janeiro - RJ

alemds
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Tue, 20 Oct 2009 9:22 am
Location: ORLANDIA-SP

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

Thanks
deniscezar
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Fri, 27 Aug 2010 12:16 pm
Location: são paulo - sp

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
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 153
Joined: Tue, 27 May 2008 1:31 pm
Location: Rio de Janeiro - RJ
Alex Silva
Analista de Sistemas
11i.10 Applications Developer Implementation Champion
11i.10 E-Business Suite Integration Champion

Do you want to consume a WebService with PL / SQL?
If this is searching for DBMS_WS that will answer you.
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

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
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Fri, 27 Aug 2010 12:16 pm
Location: são paulo - sp

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
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 153
Joined: Tue, 27 May 2008 1:31 pm
Location: Rio de Janeiro - RJ
Alex Silva
Analista de Sistemas
11i.10 Applications Developer Implementation Champion
11i.10 E-Business Suite Integration Champion

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
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Fri, 27 Aug 2010 12:16 pm
Location: são paulo - sp

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
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Fri, 01 Apr 2011 1:28 am
Location: linhares - es

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
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Fri, 27 Aug 2010 12:16 pm
Location: são paulo - sp

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
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest