To use SFTP from PL/SQL I do the following.
I've not seen a SFTP implementation from PL/SQL, but I will explain below how I implement this.
If you need an FTP-like interface, this is probably the best route to go.
PL SQL DEVELOPER ORACLE LOGON SOFTWARE
It feels like FTP, but it is actually just a connection to the SSH service on the server, so no additional software is needed. SFTP : This is basically mimicking FTP through SSH.It is essentially what I've done for FTP, with the certificate and wallet thrown in the mix. Anton Scheffer wrote about accessing FTPS directly from PL/SQL. It requires a suitable FTPS service to be present on the server, so it is not very popular. FTPS : This is FTP, but using a certificate to encrypt the communication.This leaves you with two FTP-like alternatives. If you are using FTP today, you are probably making a very big mistake. The world has changed drastically since this article was first written. L_ftp_server_name VARCHAR2(20) := '' ĭescription => 'Allow connections using UTL_TCP', L_acl_name VARCHAR2(30) := 'utl_tcp.xml' You will need to amend the FTP server details and username details to match your FTP server address and the Oracle username running the FTP API. The examples above work correctly with the following basic ACL. The introduction of Fine-Grained Access to Network Services in Oracle Database 11g Release 1 means you will need to configure an access control list (ACL) to allow UTL_TCP to access the network. Thanks to Hans van Doormalen for noticing I wasn't closing my passive connections. There is no support for ASCII mode in the PUT_DIRECT procedure.The implementation of binary transfers relies on UTL_FILE features only available in Oracle9i Release 2 upwards.The mput and mget operations are not supported directly, but can be implemented using a combination of the list/nlst and get/put operations.The current implementation has the following issues: If a straight forward FTP to, or from, the local filesystem is required it is more efficient to use the GET_DIRECT and PUT_DIRECT procedures as The getĪnd put procedures string these together to form a complete job using all the functions. The basic functions are implemented using LOBs to allow FTP without having to access files on the local filesystem. Remove a directory from a remote FTP server. Create a directory on a remote FTP server. P_to => '/u01/app/oracle/dba/shutdown.old') P_from => '/u01/app/oracle/dba/shutdown', Get a directory listing (file names only) from a remote FTP server. l_list.last LOOPĭBMS_OUTPUT.put_line(i || ': ' || l_list(i)) Get a directory listing from a remote FTP server.įOR i IN l_list.first. Send a binary file to a remote FTP server. Retrieve a binary file from a remote FTP server. Send an ASCII file to a remote FTP server. # Move to appropriate directory on local server The shell script may look like the following. The first method relies on a java stored procedure, described in Shell Commands From PL/SQL, which can be used to trigger a shell script to perform the transfer. This article contains a brief description of the two methods I use. Sometimes it's preferable to trigger FTP jobs directly from PL/SQL rather than rely on CRON or AT. Home » Articles » Misc » Here FTP From PL/SQL
0 kommentar(er)
