[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Secure ftp

Posted on 2016-10-26
Medium Priority
Last Modified: 2016-11-13
I have a followup question to this Getting network error using utl tcp package.

I was able to implement file transferring to FTP site using Oracle. But in our production system ftp is not allowed. It has to be via sftp. Is there a way to sftp using oracle using the same program?

Question by:gs79
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 23

Expert Comment

ID: 41861218
Have you tried it, following the similar setup for ACL rights, etc? I'd suggest you first ensure that an O/S user can sftp to the remote host, then perhaps as the Oracle account that's running in the application. Leave off the actual application at first, to keep this simple. It's an interesting and unique approach, I'll be curious to learn if this works for you.
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 41861233
the ftp package used in the previous question won't support sftp and it would be a significant rewrite to implement.

an easier solution would be to use a java stored procedure with a pl/sql front end.

Author Comment

ID: 41861342
Is there a documentation to use java stored procedure

I tried using external c program to execute shell commands and I get the following error

ORA-28576: lost RPC connection to external procedure agent

Not sure if there is anything wrong in the setup but it looks ok.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 41862897
I compiled c program into a shared object which can execute shell commands. I have wrapper plsql function which executes the library.

Now I can execute the following unix command with out any issues

 retval := msi_extprocsh('/bin/ls /home/oracle/ > /home/oracle/output.txt');

but when i try to execute following it fails though the string executes fine in the terminal:

retval := msi_extprocsh('/bin/osftp ftp.ftpsite.com ftpuser ftppass test /home/oracle/myftp xyz.csv');

/bin/osftp  is a soft link to a wrapper shell script secureftp.sh which accepts following six parameters

1. host
2. user
3. password
4. Change directory to in the target
5. LCD
6. file name

I get the following error from plsql:

ORA-28576: lost RPC connection to external procedure agent

Not sure why it should not execute /bin/osftp command when it can execute /bin/ls command.

Please let me know
LVL 74

Expert Comment

ID: 41862913
diagnosing that would be a completely new question pursuing the c, the listener, extproc setup etc.

if you don't mind using os commands, then try using dbms_scheduler to call ftp or your osftp script with external programs

Author Comment

ID: 41862951
Could it be C program or the setup? Because the program executes just fine for the command copying the contents of the file to a directory.

We use a 3 party scheduler  which can execute shell script. But its nice to generate the file and immediately ftp it with in plsql program
LVL 74

Expert Comment

ID: 41863364
>> Could it be C program or the setup?


>>  ftp it with in plsql program

using dbms_scheduler you don't have to actually "schedule" the process, simply use it to run the external sftp command.  Doing that you can invoke it from pl/sql

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question