ORA_NET

ORA_NET is a companion PL/SQL package that ships with DidiSoft OraSFTP. It provides various Internet related communication methods intended for PL/SQL developers wishing to extend the capabilities of their Oracle® databases.

Table of contents

Setup of ORA_NET
Retrieving Web resources (WGET)


Setup of ORA_NET

Step 1) Register the ORA_NET PL/SQL package
using your favorite PL/SQL development environment (SQL*Plus, Oracle© SQLDeveloper©, etc.) execute the PL/SQL scripts located at:

[extraction folder]/SetupFiles/OraNet/OraNET_Package.sql

Step 2) Permissions

ORA_NET requires the same connect, resolve permissions for the package invoker account as OraSFTP:

Step 3) Permissions
For each Internet host that you would like to communicate with, execute in your PL/SQL environment under a database login account that has the SYSDBA role:

call dbms_java.grant_permission( 'smithj', 'SYS:java.net.SocketPermission', 'host_name_or_IP_address', 'connect,resolve' );
 commit;

Note: don’t forget to replace ‘smithj‘ and ‘host_name_or_IP_address‘ with the DB user/scheme name which will invoke the OraSFTP package subprograms and the IP/hostname where the Internet host you want to access with ORA_NET.

Retrieving Web resources (WGET)

The subprogram ORA_NET.WGET acts in a similar fashion to the Unix/Linux shell command wget. It downloads a file located on the Web. The subprogram is available in two versions, the first one will throw an EXCEPTION if an error occurs and the second one will continue its operation but will return a status code instead.

WGET with EXCEPTION

This sample code will download the Didisoft web site robots.txt file.

DECLARE
 blob_file BLOB;
BEGIN  
 blob_file := ORA_NET.WGET('https://www.didisoft.com/robots.txt');
 DBMS_OUTPUT.put_line(UTL_RAW.CAST_TO_VARCHAR2(blob_file));
END;

WGET with status code

This example is similar to the above one but here instead of EXCEPTION in case of an error, the result status code will be ORA_NET.ERROR and an error message will be posted in the last parameter:

DECLARE
 blob_file BLOB;
 err VARCHAR(2000);
 res INTEGER;
BEGIN  
 res := ORA_NET.WGET('https://www.didisoft.com/robots.txt', blob_file, err);
 
 IF res = ORA_NET.SUCCESS THEN
   DBMS_OUTPUT.put_line(UTL_RAW.CAST_TO_VARCHAR2(blob_file));
 ELSE 
   DBMS_OUTPUT.put_line(err);
 END IF;
END;

Summary

ORA_NET is still a rather small package. If you need some extra capabilities in it, please don’t hesitate to drop us a line.