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
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:
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;
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.