Solved

Secure ftp

Posted on 2016-10-26
7
51 Views
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?

Thanks
0
Comment
Question by:gs79
[X]
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
7 Comments
 
LVL 23

Expert Comment

by:David
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.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 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.
0
 

Author Comment

by:gs79
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.

Thanks
0
Industry Leaders: 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!

 

Author Comment

by:gs79
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

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

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
0
 
LVL 74

Expert Comment

by:sdstuber
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
0
 

Author Comment

by:gs79
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
0
 
LVL 74

Expert Comment

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

sure

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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle RMAN - trying to duplicate a database 5 52
Email query results in HTML 6 38
update using pipeline function 3 32
pl/sql parameter is null sometimes 2 24
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

756 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