Thursday, March 13, 2014

DBMS Network ACL of HTTP call using UTL_HTTP in oracle


Oracle allows access to external network services using several PL/SQL APIs (UTL_TCPUTL_SMTPUTL_MAILUTL_HTTP and UTL_INADDR), all of which are implemented using the TCP protocol. In previous versions of the database, access to external services was effectively an on/off switch based on whether a user was granted execute permissions on a specific package or not. Oracle 11g introduces fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants.

Access control lists are manipulated using the DBMS_NETWORK_ACL_ADMIN package. reference

Scripts :



begin
dbms_network_acl_admin.create_acl (
   acl          => 'networkacl.xml',
   description  => 'Allow Network Connectivity',
   principal    => 'PUBLIC',
   is_grant     => TRUE,
   privilege    => 'connect',
   start_date   => SYSTIMESTAMP,
   end_date     => NULL);

dbms_network_acl_admin.assign_acl (
   acl         => 'networkacl.xml',
   host        => '*',
   lower_port  => NULL,
   upper_port  => NULL);

commit;
end;

Monday, December 02, 2013

Parse SOAP Message Using PL/SQL

Suppose we want to parse bellow SOAP message :

   
   
      
         SOAP-ENV:Client
         The security token could not be authenticated or authorized; nested exception is org.apache.ws.security.WSSecurityException: The security token could not be authenticated or authorized
      
   


To parse this SOAP we can write script like :
CREATE TABLE XMLMESSAGE(

  message XMLTYPE

  serial  number,
)


--before running bellow script insert the message in previously created table

select atts.faultcode,atts.faultstring 

   from XMLMESSAGE,
        xmltable(xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as
                               "SOAP-ENV"
                       
                           ),
                 '/SOAP-ENV:Envelope/SOAP-ENV:Body/SOAP-ENV:Fault'
                 passing XMLMESSAGE.MESSAGE columns 
                 faultcode varchar2(1000) path ''faultcode',
                 faultstring varchar2(1000) path ''faultstring'
                 ) atts where serial = 1;
  

Saturday, November 30, 2013

PL/SQL Custom Exception Handaling


DECLARE

  v_Return VARCHAR2(5000);

  custom_exception EXCEPTION;

BEGIN

  v_Return := 'A';
  
  
  IF v_Return = 'A' THEN
    RAISE custom_exception;
  END IF;

EXCEPTION
  WHEN custom_exception THEN
    DBMS_OUTPUT.PUT_LINE('error');
  
END;