SFTP from PLSQL

In this blog I am going to use a simple program to SFTP files from my database server to any target machine using SFTP. To demonstrate this I am using two servers one where I have Oracle installed and another server where SFTP is enabled.

SFTP basically means a protocol in which we can move one file to any other server in secured way. The full form is “Secure File Transfer Protocol” , this is protocol which is packaged with SSH.

To achieve this we have to do some prerequisites like creating one shell script , and follow to create a function called “msi_extprocsh” from my earlier blog.

Link as to follow the steps to create the function:

https://slobaexpert.wordpress.com/2015/09/10/executing-linux-command-from-oracle-plsql/

Once you have complete the steps shown on the link , create a folder called “sftp”  on your database server “/home/oracle/app/” .

And create a file called “sftp.sh” under path “”  with the below code:

#!/usr/bin/expect

set timeout -1

if { $argc != 6 } {
    puts "Usage $argv0 host user pass cd lcd file "
    exit 1
}

set host [lindex $argv 0]
set user [lindex $argv 1]
set pass [lindex $argv 2]
set cd [lindex $argv 3]
set lcd [lindex $argv 4]
set file [lindex $argv 5]

spawn sftp -oStrictHostKeyChecking=no -oCheckHostIP=no $user@$host
expect *assword:

send "$pass\r"
expect sftp>

send "cd $cd\r"
expect sftp>

send "lcd $lcd\r"
expect sftp>
send "put $file\r"

send "exit\r"
expect eof

The above file contains 6 parameters where we need to pass the host name of target server where we want to move the file along with username & password .  Set the local and report directory where we want to place the file.

Now under path “/home/oracle/app/sftp” create a sample file called “Hello.txt” and save the file with some sample text like :


[oracle@DBserversftpsftp]$ pwd
/home/oracle/app/sftp
[oracle@DBserversftp]$ cat Hello.txt
This is a sample file[oracle@DBserversftp]$

Now lets login to our target server where we want to place the file :

login as: root
root@TargetServer's password:
Last login: Thu Sep 10 11:27:44 2015 from TargetServer
[root@TargetServer ~]# cd Desktop/sftp/
[root@TargetServer sftp]# pwd
/root/Desktop/sftp
[root@TargetServer sftp]# ls
[root@TargetServer sftp]#

We can see that there is no file present on the location

Now let’s run the PLSQL block to place the “Hello.txt” file on the target server.

DECLARE

        l_file_name       VARCHAR2(4000) := 'Hello.txt'; --file name 
        retval            NUMBER;
        l_sftp_prog       VARCHAR2(100) := 'cd /home/oracle/app/sftp; ./sftp.sh';
        l_sftp_host       VARCHAR2(100) := 'hostname';
        l_sftp_user       VARCHAR2(100) := 'username';
        l_sftp_pass       VARCHAR2(100) := 'password';
        l_sftp_path       VARCHAR(500) := '/root/Desktop/sftp/'; -- target directory to put the file
        l_local_file_path VARCHAR2(500) := '/home/oracle/app/sftp/'; -- local file path 
        l_sftp_command    VARCHAR2(500);

BEGIN

        l_sftp_command := l_sftp_prog || ' ' || l_sftp_host || ' ' || l_sftp_user || ' ' ||
                          l_sftp_pass || ' ' || l_sftp_path || ' ' || l_local_file_path || ' ' ||
                          l_file_name;

        dbms_output.put_line(l_sftp_command);

        retval := msi_shell(l_sftp_command);

        dbms_output.put_line(retval);

END;

Check the target server :

[root@TargetServer sftp]# ls
[root@TargetServer sftp]# ls
Hello.txt
[root@TargetServer sftp]# pwd
/root/Desktop/sftp
[root@TargetServer sftp]#
[root@TargetServer sftp]# cat Hello.txt
This is a sample file[root@TargetServer sftp]#

Now we can see that the file is moved using SFTP protocol through PLSQL code. Basically we are calling the bash script to do the SFTP and PLSQL code is calling the script by passing right set of parameter from Oracle.

Thank you for reading my blog , the reference is taken from OTN . Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s