OraSFTP setup

This chapter discusses the setup process of DidiSoft OraSFTP package. It assumes that you are familiar with Oracle(c) Database 11 and above and how to execute commands on the command shell of your operating system.

System Requirements:  Oracle Database platforms Enterprise Edition (version 11 or above), Standard Edition (version 11 or above), Standard One (version 11 or above)

Table of Contents
1. Setup

• Loading the JAR files
• Creating ORA_SFTP package
• Network permissions

2. Upgrade
3. From trial to production
4. Uninstall
5. The user/pass parameter
6. Oracle Server Process

1. Setup

Prerequisites

This product is a Java stored procedure package, and as such, resides in a database schema. You can use any schema (referred in the example calls below as ‘smithj’), but if you want to have a dedicated schema for this package, the minimum privileges needed are shown below:

CREATE USER smithj IDENTIFIED BY pass;
 
GRANT CREATE TABLE TO smithj;
GRANT CREATE ANY PROCEDURE TO smithj;
GRANT CREATE TYPE TO smithj;
GRANT CREATE session TO smithj; -- this can be skipped, read point 1.1 below
 
-- this is needed in order to be able to store the JAR file contents
ALTER USER smithj quota unlimited ON USERS;

Setup steps

Step 1.0) Extract the distribution ZIP archive to a folder, referred below as [extraction folder]

Step 1.1) Load the JAR files

Load the JAR files using the loadjava.sh/.bat utility located in the Oracle Database instance $ORACLE_HOME/bin/ folder.

Note:
Replace below smithj/pass with a real database user (schema) and password applicable for your Oracle Database and host details if needed. Read below for more details regarding the user/pass parameter and database server located on a remote machine.
[Windows environment]

loadjava.bat -resolve -verbose -u smithj/pass [extraction folder]\SetupFiles\ora-sftp-1.4.jar

[Unix environment]

loadjava.sh -resolve -verbose -u smithj/pass [extraction folder]/SetupFiles/ora-sftp-1.4.jar

An alternative method of loading:
The loadjava utility requires that the SESSION rights are granted to the destination schema.  This grant can be skipped and you can load the package JAR file from PL/SQL with:

call dbms_java.grant_permission('SMITHJ', 'java.io.FilePermission', '{extraction folder}/SetupFiles/ora-sftp-{version}.jar', 'read');
commit;
call dbms_java.loadjava('-r -v {extraction folder}/SetupFiles/ora-sftp-{version}.jar');

Step 1.2) Register the ORA_SFTP 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/OraSFTP_Package.sql

Step 1.3) Permissions

For each SFTP 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', 'sftp_host_name_or_IP_address', 'connect,resolve' );
commit;

Note: don’t forget to replace ‘smithj‘ and ‘sftp_host_name_or_IP_address‘ with the DB user/scheme name where the OraSFTP package was loaded and the IP/hostname where the SFTP server is.

Step 1.4) Check that everything is working:
Execute in your PL/SQL command environment:

SELECT ORA_SFTP.VERSION FROM dual

2. Upgrade

1) first Uninstall the trial version
2) then download the new version from our customers’ section
3) execute the Setup steps using the new version

3. From trial to production

To switch from an evaluation (trial) version of the software to a licensed production version:

1) first Uninstall the trial version
2) then download the production version from our customers’ section
3) execute the Setup steps using the licensed production version

4. Uninstall

The uninstall process is similar to the Upgrade and requires first to unload the old version JAR files from the Oracle© database:

dropjava.sh/.bat -resolve -verbose -user smithj/pass [extraction folder]\SetupFiles\ora-sftp-<version>.jar

and afterwards to drop the ORA_SFTP package using your favorite PL/SQL execution environment (SQL*Plus, Oracle© SQLDeveloper©, etc.

DROP package ORA_SFTP;

Note: Replace above user/password with the database username(schema) and password used in the initial Setup.

5. The user/password parameter

The -user parameter of the loadjava and dropjava commands can be simply:

username/password – where username is a user(schema) name in the Oracle Database and password is its password.

or

username/password@database – in this case, database can be a TNS name or Net8 name-value list

If we want to install the script into a remote machine specified with @host:port:SID then the additional -thin parameter must also be used:

loadjava -resolve -verbose -thin -u username/password@host:port:SID ...

for example

loadjava -resolve -verbose -thin -u ASIO/ASIOpassword@localhost:1521:ORCL2 ./SetupFiles/ora-sftp-1.4.jar

Oracle Server Process

Oracle Database creates dedicated and shared server processes (connections) to handle the requests of user processes connected to an instance.

OraSFTP is more suitable to be invoked through a Dedicated server process because of large file operations (Upload/Download) through a slow network connection are time-consuming and allow little to no idle time to the server process.

Possible unexpected behavior when using a Shared server connection

The PL/SQL script may hang unexpectedly at some routines of the ORA_SFTP package.