ORA 29260: network error: TNS:connection closed

Question

When i try to connect an sftp location from my oracle machine using a PL/SQL procedure, i got the ORA-29260: network error: TNS:connection closed error.

No problem with telnet connection
Target sftp machine is added to ACL
No problem on file transfer using ssh
Can manually connect to sftp location

DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('xx.xx.xx.xx', '22', 'myuser', 'mypass');
--  ftp.ascii(p_conn => l_conn);
--  ftp.put(p_conn      => l_conn,
--          p_from_dir  => 'MY_DOCS',
--          p_from_file => 'test_get.txt',
--          p_to_file   => '/app/test_put.txt
dbms_output.put_line('connection done');
ftp.logout(l_conn);
END;

is there any idea ?

Thanks in advance.

score 1 · Answer 1 · answered Oct 25 '21 at 11:50

Your program will never work. You are using port 22, which means that you are using SFTP, which is built on top of SSH. PLSQL does not provide an API against SSH directly from UTL_TCP.

However, it can be implemented using dbms_scheduler with external_script

To use SFTP from PL/SQL I'd do the following.

Use keypair authentication between the database server and the remote SSH service, allowing passwordless connections.
Write a shell script to perform the SFTP command.
Call the shell script from a job using the scheduler.

So, you have a script bash which runs the sftp, assuming you have exchange keys between the hosts that no passwords are needed.

#!/bin/bash 

cd /directoryfiles
sftp user@host << eof 
mput *.* -- or mget 
eof

This is mysftp.sh. Then you need a credential and a job in dbms_scheduler

BEGIN
  DBMS_CREDENTIAL.create_credential(
    credential_name => 'my_cred_sftp',
    username        => 'myuser',
    password        => 'mypassword'
  );
END;
/

BEGIN
 DBMS_SCHEDULER.create_job(
    job_name        => 'MY_RUN_SFTP',
    job_type        => 'EXTERNAL_SCRIPT',
    job_action      => '/mydirectory/mysftp.sh',
    credential_name => 'my_cred_sftp',
    enabled         => TRUE , 
    auto_drop       => TRUE
  );
END;
/

ORA 29260: network error: TNS:connection closed

1 Answers1