gs79
asked on
Getting network error using utl tcp package
Oracle 11g
Linux red hat
New to FTP. Trying out UTL TCP program used in Tim Hall's FTP package. Created an FTP site and I am able to access. The plsql program gives the following error
DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.ftpsite.com', '21', 'uname', 'pwd');
ftp.ascii(p_conn => l_conn);
ftp.get(p_conn => l_conn,
p_from_file => '/ftpfolder/filename.csv',
p_to_dir => 'mydir',
p_to_file => 'xyz.csv');
ftp.logout(l_conn);
END;
/
ORA-29260: network error: not connected
ORA-06512: at "SYS.UTL_TCP", line 235
ORA-06512: at "SYS.UTL_TCP", line 473
ORA-06512: at "HR.FTP", line 267
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "HR.FTP", line 479
ORA-06512: at line 6
29260. 00000 - "network error: %s"
*Cause: A network error occurred.
*Action: Fix the network error and retry the operation.
I have added the ftp site to acl list. Is there anything I should look into..
Thanks,
Linux red hat
New to FTP. Trying out UTL TCP program used in Tim Hall's FTP package. Created an FTP site and I am able to access. The plsql program gives the following error
DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login('ftp.ftpsite.com', '21', 'uname', 'pwd');
ftp.ascii(p_conn => l_conn);
ftp.get(p_conn => l_conn,
p_from_file => '/ftpfolder/filename.csv',
p_to_dir => 'mydir',
p_to_file => 'xyz.csv');
ftp.logout(l_conn);
END;
/
ORA-29260: network error: not connected
ORA-06512: at "SYS.UTL_TCP", line 235
ORA-06512: at "SYS.UTL_TCP", line 473
ORA-06512: at "HR.FTP", line 267
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "HR.FTP", line 479
ORA-06512: at line 6
29260. 00000 - "network error: %s"
*Cause: A network error occurred.
*Action: Fix the network error and retry the operation.
I have added the ftp site to acl list. Is there anything I should look into..
Thanks,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ran this as dba on my sandbox database
BEGIN
DBMS_NETWORK_ACL_ADMIN.dro p_acl (
acl => 'ftp_permissions.xml');
COMMIT;
END;
begin
dbms_network_acl_admin.cre ate_acl (
acl => 'ftp_permissions.xml', -- or any other name
description => 'ftp Access',
principal => 'HR', -- the user name trying to access the network resource
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;
/
commit;
begin
DBMS_NETWORK_ACL_ADMIN.ADD _PRIVILEGE (acl => 'ftp_permissions.xml',
principal => 'HR',
is_grant => true,
privilege => 'connect');
end;
/
commit;
begin
DBMS_NETWORK_ACL_ADMIN.ADD _PRIVILEGE (acl => 'ftp_permissions.xml',
principal => 'HR',
is_grant => true,
privilege => 'resolve');
end;
/
commit;
BEGIN
dbms_network_acl_admin.ass ign_acl (
acl => 'ftp_permissions.xml',
host => 'ftp.ftpsite.com', /*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*/
lower_port => 21,
upper_port => 21
);
END;
/
I see the acl connection I created using below query
SELECT *
FROM dba_network_acls;
ftp.ftpsite.com 21 21
Thanks
BEGIN
DBMS_NETWORK_ACL_ADMIN.dro
acl => 'ftp_permissions.xml');
COMMIT;
END;
begin
dbms_network_acl_admin.cre
acl => 'ftp_permissions.xml', -- or any other name
description => 'ftp Access',
principal => 'HR', -- the user name trying to access the network resource
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;
/
commit;
begin
DBMS_NETWORK_ACL_ADMIN.ADD
principal => 'HR',
is_grant => true,
privilege => 'connect');
end;
/
commit;
begin
DBMS_NETWORK_ACL_ADMIN.ADD
principal => 'HR',
is_grant => true,
privilege => 'resolve');
end;
/
commit;
BEGIN
dbms_network_acl_admin.ass
acl => 'ftp_permissions.xml',
host => 'ftp.ftpsite.com', /*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*/
lower_port => 21,
upper_port => 21
);
END;
/
I see the acl connection I created using below query
SELECT *
FROM dba_network_acls;
ftp.ftpsite.com 21 21
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Bingo, worked!
@sdstuber What do you recommend as best practice. To send a file to third party offsite, do you recommend generating file and use shell program to send the file or use oracle plsql? I think there is lot of flexibility in leveraging utl_tcp/http, will there be security implications. Reading more about active and passive ftp connection
Thanks
@sdstuber What do you recommend as best practice. To send a file to third party offsite, do you recommend generating file and use shell program to send the file or use oracle plsql? I think there is lot of flexibility in leveraging utl_tcp/http, will there be security implications. Reading more about active and passive ftp connection
Thanks
If your data is in the database I'd lean toward pl/sql. Using my package you don't even need to generate a file (although you can if you want/need to.) You can send a clob or blob directly from the db to the remote server as a file without needing to create a physical file locally.
Security is important, but if you control your ACL grants (the principal assignments) and limit access to the package it shouldn't be a problem keeping the sending secure.
As for the acl ports, they are no more or less secure than what you need to do it for a client. Think of it the acl like an extra firewall layer. If your external firewall doesn't allow communication across ports 1024-65535 then your external client won't be able to talk to the remote server either.
If your data is not inside the database then I'd probably use something external to the db.
Security is important, but if you control your ACL grants (the principal assignments) and limit access to the package it shouldn't be a problem keeping the sending secure.
As for the acl ports, they are no more or less secure than what you need to do it for a client. Think of it the acl like an extra firewall layer. If your external firewall doesn't allow communication across ports 1024-65535 then your external client won't be able to talk to the remote server either.
If your data is not inside the database then I'd probably use something external to the db.
ASKER
Thanks