Consume Web Service Using PL/SQL

Script to create package :

create or replace
PACKAGE WS_SOAP_CLIENT
AS

FUNCTION SOAP_REQUEST_META(
     l_host_name   VARCHAR2,
    l_string_request VARCHAR2)
  RETURN VARCHAR2;
  
END WS_SOAP_CLIENT;
 

Script to create package body :

create or replace
PACKAGE BODY WS_SOAP_CLIENT AS

  FUNCTION SOAP_REQUEST_META(l_host_name      VARCHAR2,
                             l_string_request VARCHAR2) RETURN VARCHAR2 IS
    l_http_request  UTL_HTTP.req;
    l_http_response UTL_HTTP.resp;
    l_buffer_size   NUMBER(10) := 1024;
    l_substring_msg VARCHAR2(2048);
    l_raw_data      RAW(2048);
    l_clob_response CLOB;
    total_xml       VARCHAR2(16384);
  
  BEGIN
  
    UTL_HTTP.set_transfer_timeout(60);
    l_http_request := UTL_HTTP.begin_request(url    => 'http://' || l_host_name || '/example-service/endpoints/example.wsdl',
                                             method       => 'POST',
                                             http_version => 'HTTP/1.1');
    UTL_HTTP.set_header(l_http_request,
                        'User-Agent',
                        'Apache-HttpClient/4.1.1');
    UTL_HTTP.set_header(l_http_request, 'Connection', 'Keep-Alive');
    UTL_HTTP.set_header(l_http_request,
                        'Content-Type',
                        'text/xml;charset=UTF-8');
    UTL_HTTP.set_header(l_http_request,
                        'Content-Length',
                        LENGTH(l_string_request));
  
    <>
    FOR i IN 0 .. CEIL(LENGTH(l_string_request) / l_buffer_size) - 1 LOOP
      l_substring_msg := SUBSTR(l_string_request,
                                i * l_buffer_size + 1,
                                l_buffer_size);
    
      BEGIN
        l_raw_data := utl_raw.cast_to_raw(l_substring_msg);
        UTL_HTTP.write_raw(r => l_http_request, data => l_raw_data);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT request_loop;
      END;
    END LOOP request_loop;
  
    l_http_response := UTL_HTTP.get_response(l_http_request);
  
    BEGIN
    
      <>
      LOOP
        UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
        l_clob_response := l_clob_response ||
                           UTL_RAW.cast_to_varchar2(l_raw_data);
      END LOOP response_loop;
    
    EXCEPTION
      WHEN UTL_HTTP.end_of_body THEN
        UTL_HTTP.end_response(l_http_response);
    END;
  
    IF (l_http_response.status_code = 200) OR (l_http_response.status_code = 500) then
      total_xml := l_clob_response;
    
    ELSE
      RETURN 'N';
    
    end if;
  
    IF l_http_request.private_hndl IS NOT NULL THEN
      UTL_HTTP.end_request(l_http_request);
    END IF;
    RETURN total_xml;
    
  EXCEPTION
    WHEN OTHERS THEN
      raise_application_error(-20001, SQLERRM);
    
  END SOAP_REQUEST_META;

END WS_SOAP_CLIENT;

PL/SQL Global Temporary Tables (GTT)

Sometimes we need to store data temporarily in a table . From Oracle 8i onward, we can manage temporary tables can be delegated to the server by using Global Temporary Tables.

Creation of Global Temporary Tables

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session.

CREATE GLOBAL TEMPORARY TABLE temp_test_gtt (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

CREATE GLOBAL TEMPORARY TABLE temp_test_gtt (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT PRESERVE ROWS;

  Other Features

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
• Data in temporary tables is stored in temp segments in the temp tablespace.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
• There are a number of restrictions related to temporary tables but these are version specific.

Reference : http://www.oracle-base.com/articles/misc/temporary-tables.php

Connect to remote oracle database from Sql Developer / TOAD by Tunneling

Computer networks use a tunneling protocol when one network protocol (the delivery protocol) encapsulates a different payload protocol. By using tunneling one can (for example) carry a payload over an incompatible delivery-network, or provide a secure path through an untrusted network.
Tunneling typically contrasts with a layered protocol model such as those of OSI or TCP/IP. Typically, the delivery protocol operates at an equal or higher level in the model than does the payload protocol, or at the same level.
To understand a particular protocol stack, network engineers must understand both the payload and delivery protocol sets.
As an example of network layer over network layer, Generic Routing Encapsulation (GRE), a protocol running over IP (IP Protocol Number 47), often serves to carry IP packets, with RFC 1918 private addresses, over the Internet using delivery packets with public IP addresses. In this case, the delivery and payload protocols are compatible, but the payload addresses are incompatible with those of the delivery network.
In contrast, an IP payload might believe it sees a data link layer delivery when it is carried inside the Layer 2 Tunneling Protocol (L2TP), which appears to the payload mechanism as a protocol of the data link layer. L2TP, however, actually runs over the transport layer using User Datagram Protocol (UDP) over IP. The IP in the delivery protocol could run over any data-link protocol from IEEE 802.2 over IEEE 802.3 (i.e., standards-based Ethernet) to the Point-to-Point Protocol (PPP) over a dialup modem link.
Tunneling protocols may use data encryption to transport insecure payload protocols over a public network (such as the Internet), thereby providing VPN functionality. IPsec has an end-to-end Transport Mode, but can also operate in a tunneling mode through a trusted security gateway.


A secure shell (SSH) tunnel consists of an encrypted tunnel created through an SSH protocol connection. Users may set up SSH tunnels to transfer unencrypted traffic over a network through an encrypted channel. For example, Microsoft Windows machines can share files using the Server Message Block (SMB) protocol, a non-encrypted protocol. If one were to mount a Microsoft Windows file-system remotely through the Internet, someone snooping on the connection could see transferred files. To mount the Windows file-system securely, one can establish a SSH tunnel that routes all SMB traffic to the remote fileserver through an encrypted channel. Even though the SMB protocol itself contains no encryption, the encrypted SSH channel through which it travels offers security.

Reverse ssh tunnel

To set up an SSH tunnel, one configures an SSH client to forward a specified local port to a port on the remote machine. Once the SSH tunnel has been established, the user can connect to the specified local port to access the network service. The local port need not have the same port number as the remote port.
SSH tunnels provide a means to bypass firewalls that prohibit certain Internet services — so long as a site allows outgoing connections. For example, an organization may prohibit a user from accessing Internet web pages (port 80) directly without passing through the organization's proxy filter (which provides the organization with a means of monitoring and controlling what the user sees through the web). But users may not wish to have their web traffic monitored or blocked by the organization's proxy filter. If users can connect to an external SSH server, they can create a SSH tunnel to forward a given port on their local machine to port 80 on a remote web-server. To access the remote web-server, users would point their browser to the local port at http://localhost/.
Some SSH clients support dynamic port forwarding that allows the user to create a SOCKS 4/5 proxy. In this case users can configure their applications to use their local SOCKS proxy server. This gives more flexibility than creating a SSH tunnel to a single port as previously described. SOCKS can free the user from the limitations of connecting only to a predefined remote port and server. If an application doesn't support SOCKS, one can use a "socksifier" to redirect the application to the local SOCKS proxy server. Some "socksifiers", such as Proxycap, support SSH directly, thus avoiding the need for a SSH client.

Reference :  http://en.wikipedia.org/wiki/Tunneling_protocol

Practical Situation :
Suppose your machine IP is :  A and you have access to server B but not in server C . But ,  you can connect to server C from server B .

 Now , you want to connect your local sql developer to oracle server which is in C through B.

Download Putty (http://www.putty.org/)

open it and you will find something like :

Now , in read marked section put machine address or ip address of server B .

Now , take a look to left section of the window and select Tunnels , after that check the read box which will allow local port to connect to other host .In source port put any custom port and in Destination put remote machine / server ip or as example ip of machine C with oracle port , Example : 10.20.20.21:1521 and click add button .
 

Now, you are ready to open the session , click to session section and click to open :

now , a command window will open , now log in to server B with your user name and password , this will open the required session and tunnel for us as configured before,and you are ready to connect your SQL developer with remote oracle namely in server C .

Now, open sql server and put user name , password of remote oracle server .
In  hostname put : localhost
In port number : source port as given earlier
In SID : SID of remote oracle
  

 Finally click to connect . If you configure everything as above you will get connected to remote oracle server from your local machine . Good Luck .