asked on

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

David VanZandt

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.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gs79

ASKER

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

gs79

ASKER

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

Sean Stuber

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

gs79

ASKER

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

Sean Stuber

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