Skip to Main Content
  • Questions
  • How to send file Using SFTP (Secure FTP )

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, shashi.

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

Answered by: Tom Kyte - Last updated: February 28, 2012 - 7:11 am UTC

Category: Database - 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 we 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.

and you rated our response

  (7 ratings)

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

Reviews

Why doesn't oracle natively support SFTP?

February 01, 2011 - 9:40 am UTC

Reviewer: Bryan from Rochester, NY

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

Followup  

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

February 01, 2011 - 5:47 pm UTC

Reviewer: sam

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

Followup  

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......

February 02, 2011 - 4:05 pm UTC

Reviewer: CLG

...... 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.

February 03, 2011 - 9:36 am UTC

Reviewer: David Aldridge

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

February 27, 2012 - 9:13 am UTC

Reviewer: Developer

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

Followup  

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

February 27, 2012 - 9:17 am UTC

Reviewer: Developer from PA

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

February 28, 2012 - 8:22 am UTC

Reviewer: BillC from Ottawa

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