Error Ora-24247 on send FTP via PL/SQL 11gR2

[Vanjohn]

1. I did run the following sql under sys as sysdba;
Begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( 'ftp.xml', 'Enables Network Permissions for the FTP Server',
'SEATRANSUSER', TRUE, 'connect', Systimestamp );
end;
/

Begin
DBMS_NETWORK_ACL_ADMIN.Add_Privilege( 'ftp.xml', 'SEATRANSUSER', True, 'resolve', Null, Null, Null );
end;
/

BEGIN

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( 'ftp.xml', 'ftp.xxxx.com', 21, 21 );
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( 'ftp.xml', '192.168.168.70', 21, 21 );
END;
/
Commit;

2. I grant UTL_TCP UTL_FILE to common user SeaTransuser as the below;
Grant Execute on UTL_TCP to SeaTransuser;
Grant Execute on UTL_FILE to SeaTransuser;

3. I connect Oracle as other common user SeaTransuser, and I run FTP package that download from your website;

4. Create or Replace Directory FTP_DIR as 'D:\OracleShareFolder';

5. create one test sample procedure as the below

Create or Replace Procedure sp_Test
is
lc_Conn UTL_TCP.connection;
ls_AttachDir VarChar2(200);
ls_BaseFile_Dir VarChar2(100) := 'FTP_DIR';
Begin
lc_Conn := ftp.Login( '192.168.168.70', '21', 'vanjohn', 'xxxxx' );
ftp.Ascii( lc_Conn );
dbms_output.Put_line( 'Start Transfer file to Remote FTP Server' );
ftp.put( lc_Conn, ls_BaseFile_Dir, 'a.xml', 'a.xml' );
dbms_output.Put_line( 'end of test' );
ftp.Logout( lc_Conn );
EXCEPTION
WHEN OTHERS THEN
ftp.Logout( lc_Conn );
Raise;
End;
/

6. exec sp_test;

7. Result:
ORA-29260: network error:not connected
ORA-06512: at "SYS.UTL_TCP", line 212
ORA-06512: at "SYS.UTL_TCP", line 432
ORA-06512: at "SEATRANSUSER.FTP", line 413
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SEATRANSUSER.SP_TEST", line 16


But if I test it under sys as sysdba user it works well, so could you help me check what happens or where I need to grant?

[Tim...]

Hi.

The problem is with your ACL definition. You are currently only opening access to port 21. Unfortunately FTP is not this simple. Port 21 is used for the command connection, but data is transferred using different ports. The potential number of ports is limitless, since FTP will use different ports depending on what is currently available on the machine.

If you retry your test using the ACL defined with lower_port and upper_port set to NULL, you will see it works fine.

You can read more about FTP's port selection here:

http://slacksite.com/other/ftp.html

Your options are:

- Leave the port definition open for this specific FTP server.
- Investigate the most likely range of ports being used when accessing this FTP server. Remember though, this has the potential to change at any time.

Cheers

Tim...

[Vanjohn]

You are expert, already figured it out, thanks so much. please visit Xiamen, China if you have chance or feel free. thanks again.

[Tim...]

No problem.

Cheers

Tim...

[Vanjohn]

Another question, I can use mkdir to create remote directory, but if this directory already exists, it will show error, so has any function to determine remote directory exists or not?

[Tim...]

Hi.

You would have to do one of two things:

1) Create the directory and handle a possible failure if it already exists.
2) Use the list command to check the contents of the directory.

Cheers

Tim...

[Vanjohn]

Dear Tim

Well noted with thanks so much.

Vanjohn

[Tim...]

[Vanjohn]

How to create one physical directory via PL/SQL in 11gR2?

[Tim...]

Hi.

Are you still talking about FTP? If so, then yes, you use the ftp.mkdir procedure.

Cheers

Tim...