How to SFTP a CSV file from PLSQL on a Oracle 10g database (10.2.0.3)

Question

My Application generates a CSV file using UTL_FILE and writes the file to the DB server location,then the SFTP should transfer that file to a desired shared location.

First part is done,need help in the second one i.e SFTP using PLSQL

Thanks

Answer 1

While it is entirely possible to write a SFTP client in PL/SQL using the UTL_TCP package, that is unlikely to be a practical approach. In general, you have a couple options

1. Create a Java stored procedure using one of the many Java SFTP libraries and call that Java stored procedure from PL/SQL.
2. Create a shell script that does the SFTP using the server's command-line utililties and call that shell script either using DBMS_SCHEDULER or via a Java stored procedure.

If your Oracle database is running on Windows, you could also write a .Net stored procedure rather than a Java stored procedure in either of the two options above. A Java stored procedure, however, would be much more common.

Answer 2

If you would like to try a commercial offering you can check ORA_SFTP

You can upload a file with it with a code block similar to this:

DECLARE 
 connection_id NUMBER; 
 private_key_handle BFILE;
 private_key BLOB; 
 PRIVATE_KEY_PASSWORD VARCHAR2(500); 
BEGIN
 DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
 private_key_handle := BFILENAME('PGP_KEYS_DIR', 'test_putty_private.ppk'); -- directory name must be Upper case
 
 DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY);
 DBMS_LOB.LoadFromFile( private_key, private_key_handle, DBMS_LOB.GETLENGTH(private_key_handle) );
 DBMS_LOB.CLOSE(private_key_handle);
 PRIVATE_KEY_PASSWORD := 'changeit';
 
 connection_id := ORA_SFTP.CONNECT_HOST('localhost', 22, 'nasko', private_key, private_key_password);
 
 -- upload the private key just for a demo
 ORA_SFTP.UPLOAD(connection_id, private_key, 'data.csv');
 
 ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

Disclaimer: I work for DidiSoft