[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

Secure ftp

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
gs79
Asked:
gs79
  • 3
  • 3
1 Solution
 
DavidSenior Oracle Database AdministratorCommented:
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
 
sdstuberCommented:
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
 
gs79Author Commented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
gs79Author Commented:
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
 
sdstuberCommented:
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
 
gs79Author Commented:
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
 
sdstuberCommented:
>> 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now