Skip to Main Content
  • Questions

  • How to send file Using SFTP (Secure FTP )

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, shashi.

Asked: April 14, 2010 - 10:37 pm UTC

Last updated: February 28, 2012 - 7:11 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

In my application there is a requirement of generating csv file from the data available in the Database and send it to another server.

I know How to generate the file and transfer using FTP.

But requirement is to transfer the file using SFTP.

Is there any to achieve it.


Thanks.

and Tom said...

You would have to either

a) implement the sftp protocol in plsql much like we did with ftp - you know the "template" - you'd have to learn the sftp protocol and do it. I do not recommend this, it would be very hard.

b) get a java routine that does it and load it and all supporting libraries into the database using loadjava. I do not recommend this either really - you'd have to worry about how to store the credentials.

c) do it using API's available to you on the middle tier. I don't really recommend this either


In short, I would not recommend using sftp in your application at all - why not networked disks? or have the remote guys connect to your database and grab the data? Using an external program would be "messy" and error prone (handling errors is really hard). Only if the API in (c) is a totally "in the program" API (no command line use) would this have a chance of being robust.


ftp is a trivial, simple (and very very insecure) protocol - sftp is quite the opposite.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why doesn't oracle natively support SFTP?

Bryan, February 01, 2011 - 9:40 am UTC

I have observed that the majority of companies have set in IT policy stone that new batch process development utilize SFTP or SCP versus FTP.

So why is Oracle behind the eight ball on this one and not released a PL/SQL implementation of SFTP?

I recognize that it might not be trivial to implement, but the protocol has been around for years now, and its not like the Oracle development team is incapable of building this kind of support into Oracle.

This is a feature which many oracle developers have been asking for, so why won't the powers that be at Oracle make it happen for us?

Pass that up the chain if you can...
Tom Kyte
February 01, 2011 - 5:13 pm UTC

we never released a plsql version of ftp - so not sure what you mean???

When I said above "like we did with ftp" - I meant "we as in you and me". Someone wrote a plsql routine to implement ftp - Oracle never did.

If you want something out of the box, use a java stored procedure - many sftp implementations around.


sFTP

sam, February 01, 2011 - 5:47 pm UTC

Tom:

If you have a java stored procedure that does SCP or SFTP I would like to see it. It is handy tool.

But also why not having pl/sql call a shell scrip to copy the file over from one machine to another.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:16212348050
Tom Kyte
February 01, 2011 - 6:33 pm UTC

sam:

http://www.google.com/search?rlz=1C1CHFX_enUS406US407&sourceid=chrome&ie=UTF-8&q=sftp+java

it isn't hard to find them.


... But also why not having pl/sql call a shell scrip to copy the file over from
one machine to another. ...

because that is highly unreliable in practice and doesn't need to be done. Scripting sftp is hard as well - managing the credentials is really hard.

Frankly......

CLG, February 02, 2011 - 4:05 pm UTC

...... SFTP should not be completely out the box in Oracle.

I think the s in sftp means the primary responsibility goes to DBA/LAN/WAN team.

They should own certs/authentication standards.

MAYBE. I SAID MAYBE.

Oracle should provide a java based solution with an PL/SQL wrapper.

But actually there should be "something" else to handle the sftp management on your LAN.

My two cents.

David Aldridge, February 03, 2011 - 9:36 am UTC

A DBMS_Scheduler job is pretty handy for invoking scp, or any other os command. You do really want to do that whole key swapping thing first to keep it straightforward, but it's working nicely for me.

Code for SFTP from pl\sql program

Developer, February 27, 2012 - 9:13 am UTC

I have a pl\sql procedure thatr creats the csv file using utl_file. now i need to transfer the files to the EFT server .I am thinking to extend the existing procedure to transfer the files to EFT lcoation . Kindly give me the code to do the sftp transfer
Tom Kyte
February 28, 2012 - 7:11 am UTC

Kindly give me the code to do
the sftp transfer

neat. Wouldn't life be grand if it worked that way?


Kindly reread the original answer above. And then do what programmers are supposed to do - analyze the possible approaches, decide which one makes the most sense, and then implement it.

Calling ksh from oracle

Developer, February 27, 2012 - 9:17 am UTC

my goal is to transfer the csv files from db server to another server from Pl\sql procedure. I have two options
1. invoke ksh script from pl\sql or
2. ftp it to EFT location directly .

Kindly provide code to either of the ways possiable.

Read Above

BillC, February 28, 2012 - 8:22 am UTC

You should read Tom's answer directly above your request. All the required information is contained there.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.