DB-REST access

 ACL(Access control list)
ORDS(Oracle REST Data Service)

  • It is a list of access control entries to restrict the hosts that are allowed to connect to the Oracle database.
  • ACLs are created using dbms_network_acl_admin and dbms_network_acl_utility packages. 

Calling WebServices from Oracle PLSQL: 

  • Links
    • https://www.youtube.com/watch?v=avBgGPw0_sA&t=16s 
    • https://slobaray.com/2015/02/05/calling-web-services-from-oracle-plsql/
    • http://www.dba-oracle.com/t_advanced_utl_http_package.htm 
    • https://gist.github.com/ser1zw/3757715
    • https://www.oradev.com/utlhttp.html
    • https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68001/utl_http.htm
  • In Oracle we have a package called UTL_HTTP. By using this package we can call web services using a POST method and get a response from it.
  • To use any web services we need to register the URL in our DB by assigning it to the Access Control List (ACL). Here is the below code which need to be executed under SYS user, so that we can utilize it based on any primary users on the schema. I am using to demonstrate this with Oracle Database 11g Release 11.2.0.1.0
  •  
  • BEGIN
            dbms_network_acl_admin.create_acl(acl         => 'www.xml'
                                             ,description => 'Test Sample ACL'
                                             ,principal   => 'VDESU'
                                             ,is_grant    => TRUE
                                             ,privilege   => 'connect');
            dbms_network_acl_admin.add_privilege(acl       => 'www.xml'
                                                ,principal => 'VDESU'
                                                ,is_grant  => TRUE
                                                ,privilege => 'resolve');
            dbms_network_acl_admin.assign_acl(acl  => 'www.xml'
                                             ,host => 'reqres.in');
       dbms_network_acl_admin.assign_acl(acl  => 'www.xml'
                                             ,host => 'www.oradev.com');
     
    END;
    /
    COMMIT
    /
     
    grant execute on UTL_HTTP to VDESU;
     
     /
  • SELECT * FROM DBA_NETWORK_ACLS;

    SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

    declare
    t_result
varchar2(4000);
begin
    t_result := utl_http.request('https://www.oradev.com/index.html');
    -- do somthing
end;
/

 

 

Comments

Post a Comment

Popular posts from this blog

Statistics in Machine Learning

Note : In life cycle of data science more then 60% of time goes into data analysis like feature engineering, feature selection.  Feature engineering means Cleaning data, handle missing values, unbalanced data, category features.   Python packages Pandas - read dataset read_csv, head, isnull, getdummies, drop, concat, Numpy - work with arrays matplotlib.pyplot - for visualization Seaborn - for visualization heatmap, countplot, boxplot, Handling Categorical features one hot encoding for nominal variables label encoding for ordinal variables Ways for finding Outliers Scatter plot Box plot  z-score IQR Correlation :  Strength of association between two variables  This is both ways  A and B = B and A Regression If one of the variable is dependent & other is independent variable Regression equation = Average value of 'y' is a function of x R Square Significance of F & P values     Covariance(cov) Quantify relationship between features, rand...
Read more

Cluster Analysis

Clusters Homogenous within Heterogeneous across Based on field of interest There is no Objective function No dependent variable Popularly called subjective segmentation Segmentation develops on its own based on values of the input variables This is called Unsupervised learning How is Cluster different from decision tree techniques Decision tree technique requires a clearly defined objective funtion this is based on identifying independent and dependent variables. Why clustering Find groups which has elements similar to other groups elements of different groups are dissimilar to each other Generate treatment for specific group  Examples/Business Scenarios In Marketing Leading edge shoppers - looking for technological new products Big brand shoppers Seasonal shoppers Value conscious shoppers Fashion consumer groups Group driven by trends Group interested in Fashion Group purchasing when necessary Cluster analysis as part of business strategy...
Read more