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

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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

Postby Vanjohn » Wed May 02, 2012 6:22 am

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?
Vanjohn
Member
 
Posts: 5
Joined: Wed May 02, 2012 2:12 am

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

Postby Tim... » Wed May 02, 2012 10:32 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

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

Postby Vanjohn » Wed May 02, 2012 3:42 pm

You are expert, already figured it out, thanks so much. please visit Xiamen, China if you have chance or feel free. thanks again.
Vanjohn
Member
 
Posts: 5
Joined: Wed May 02, 2012 2:12 am

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

Postby Tim... » Wed May 02, 2012 3:47 pm

No problem. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

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

Postby Vanjohn » Thu May 03, 2012 3:07 pm

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?
Vanjohn
Member
 
Posts: 5
Joined: Wed May 02, 2012 2:12 am

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

Postby Tim... » Thu May 03, 2012 4:15 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

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

Postby Vanjohn » Sat May 05, 2012 3:49 am

Dear Tim

Well noted with thanks so much.

Vanjohn
Vanjohn
Member
 
Posts: 5
Joined: Wed May 02, 2012 2:12 am

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

Postby Tim... » Sat May 05, 2012 6:55 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

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

Postby Vanjohn » Mon Jul 23, 2012 9:50 am

How to create one physical directory via PL/SQL in 11gR2?
Vanjohn
Member
 
Posts: 5
Joined: Wed May 02, 2012 2:12 am

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

Postby Tim... » Mon Jul 23, 2012 9:54 am

Hi.

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest