FTP and SFTP

1. Run ftp using a specific account interactively

$ ftp -inv remote_host.domain.com
Connected to remote_host.domain.com (10.133.67.38).
220 Microsoft FTP Service
Remote system type is Windows_NT.
ftp> user ftpuser
331 Password required for ftpuser.
Password:
230 User logged in.
ftp>

Note below line does not work:
$ ftp ftpuser@remote_host.domain.com
ftp: ftpuser@remote_host.domain.com: Name or service not known
ftp> exit

2. Run FTP in a shell script

Assume FTP login info is saved in file $HOME/.netpw in format:

machine WintEdi.domain.COM login ANET/userID password userPWD

REMOTE_PATH=......
LOCAL_PATH=......
REMOTE_MACHINE=WintEdi.domain.COM

function ftp_to_remote

{

ftpuserid=`grep $REMOTE_MACHINE $HOME/.netpw|cut -f4 -d' '`

ftpuserpwd=`grep $REMOTE_MACHINE $HOME/.netpw|cut -f6 -d' '`

ftp -inv <<EndFTP

open $REMOTE_MACHINE

user $ftpuserid $ftpuserpwd

cd $REMOTE_PATH
put $LOCAL_PATH/output.txt ouput1.txt
bye
EndFTP
}

3. Try "man ftp" for help.

4. SFTP options:

$ sftp -oport=2222 ftpuser@remote_host.domain.com
Connecting to remote_host.domain.com...
WARNING: Logon attempts are audited. Access and use allowed for authorized purposes only. Violators will be prosecuted

$ sftp -oIdentityFile=/path/to/sftp_key/id_dsa ftpuser@remote_host.domain.com
Also see https://erpondb.blogspot.com/2015/11/run-scp-or-sftp-without-password.html

Troubleshhot UTL_FILE error

Ran a PL/SQL code and got error message: 

ORA-06512: at
"SYS.UTL_FILE", line 536
ORA-29283: invalid file ope
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 90

Two steps to troubleshoot the error:

A. Find which directory it tries to write the file to, and verify it is defined in the database:
SQL> select * from dba_directories where directory_name='FTPOUT';

The directory could be owned by SYS. But APPS should be granted READ and WRITE.
SQL> SELECT * FROM dba_tab_privs WHERE table_name = 'FTPOUT';

B. Run a short code as APPS to test it. If it works, file test_UTL.txt shall create in the directory.

DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
  v_error_msg  varchar2(1000);
BEGIN
  fileHandler := UTL_FILE.FOPEN('FTPOUT', 'test_UTL.txt', 'W');
  UTL_FILE.PUT_LINE(fileHandler, 'Writing TO a test file\n');
  UTL_FILE.PUTF(fileHandler, 'Writing 2nd line to test file\n');
  UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20001, 'ERROR: Invalid PATH for the file.');
  when others then
     v_error_msg := substr(sqlerrm,1,100);
     utl_file.fclose(fileHandler);
     dbms_output.put_line('Error message is - ' || v_error_msg);
     raise;
END;
/

A MAX linesize written into file test_UTL.txt can be specified in the call:
UTL_FILE.FOPEN('FTPOUT', 'test_UTL.txt', 'W', 32767)

In EBS environment, the path for $APPLPTMP is not necessary to be in DBA_DIRECTORIES.

UPDATES in 2018:
During a database 12c upgrade, somehow the OS env variable ORA_NLS10 was unset. That made " utl_file.fcopy ('FTPOUT','test.txt','FTPOUT','test_copy.txt'); ", which is used by Export of AAD rules using Oracle Forms Lock/Unlock option, generate file test_copy.txt 0 in size and UTL_FILE.PUT_LINE fail if output file test_UTL.txt is larger than 1 MB with below errors:

declare
*
ERROR at line 1:
ORA-29282: invalid file ID
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 690
ORA-06512: at line 256
ORA-29285: file write error

or

Also check the init parameter file and value for
utl_file_dir in v$parameter table.
declare
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at line 258

The fix is to set env variable ORA_NLS10 on database server to $ORACLE_HOME/nls/data/9idata

By the way, the quick way to test $APPLPTMP and UTL_FILE_DIR in EBS is to run one line:

SQL> exec FND_FILE.PUT_LINE(fnd_file.log, 'Hello World!');

A new file with a name like l0009966.tmp shall be created in the first directory specified in the db parameter utl_file_dir, containing 'Hello World!'.