SFTP file using PL/SQL

[Lucky A]

Hi folks,

I am running Oracle 12.1.0.2 on Redhat 6.
I need your assistance on how can I execute sftp file transfer directly from the oracle database using pl/sql.

Any assistance will be helpful.

Thank you,

Lucky A

[gandolf989]

You can use utl_file to write a file to disk, and then have a process running in cron
that picks up files older than a few minuted and sftp, or scp's the file to another server.
If you use public/private key authentication you won't need a password.

[Lucky A]

You can use utl_file to write a file to disk, and then have a process running in cron
that picks up files older than a few minuted and sftp, or scp's the file to another server.
If you use public/private key authentication you won't need a password.

Thank you for your response. I will really appreciate it if you can provide the sample commands/scripts.

Thank you.

[supp]

Yeah, samples would be nice...)
Get Dixie Chicks tickets at the best price point! Available all the time!

[Lucky A]

Hi Folks,

This is an update to the client's requirement I posted earlier. The client has several thousand XML files to be transferred daily to the SFTP server. They want to be able to read the XML files from the SFTP server directly into the Oracle database using PL/SQL. Your help and expertise will be greatly appreciated in achieving this objective.

Thank you,

Lucky

[gandolf989]

Well you have two problems. First you need to be able to connect from the one server
to the other server without a password. Which probably means using puble key/private
key authentication. Here is a link on how to do that:

https://access.redhat.com/documentat...-keypairs.html

Then you need a utility that will transfer the files. Rsync is ideally suited for that.
And here is a link on how to do that. First the generic Google search:

https://www.google.com/?ion=1&espv=2...+to+copy+files

Followed by more specific examples:

http://www.tecmint.com/rsync-local-r...tion-commands/

Make sure that you use the -av flag. If you also use --delete, then files that exist on the
target server/directory that don't exist in the source directory will be deleted. This helps
if you purge old files on the directory that you are copying from, and want those files
to go away on the server/directory where you are copying to. I say server/directory because
you can use rsync to copy from one server to another, as well as from one directory to another
on the same server. Now start coding and testing.

[Lucky A]

Thank you!