gs79
asked on
ORA-29260/ORA-24247
I am trying to read a file on a ftp server..
I compiled the package provided in sdstuber's blog..
https://www.experts-exchange.com/Database/Oracle/PL_SQL/A_3043-How-to-FTP-with-Oracle-PL-SQL.html
when I execute the following piece of code to test it..I get the following error
DECLARE
v_conn sdsftp.connection;
BEGIN
sdsftp.clear_log;
sdsftp.set_log_options(1);
v_conn := sdsftp.open('localhost','<user_name>', '<pwd>');
DBMS_OUTPUT.put_line(
'------------------------- ---------- ---------- ---------- ---------- ---------- -'
);
DBMS_OUTPUT.put_line(sdsftp.get_clob(v_conn, 'test.txt'));
DBMS_OUTPUT.put_line(
'------------------------- ---------- ---------- ---------- ---------- ---------- -'
);
sdsftp.close(v_conn);
EXCEPTION
WHEN OTHERS THEN
sdsftp.close(v_conn);
RAISE;
END;
/
ORA-29260: network error: not connected
ORA-06512: at "HR.SDSFTP", line 432
ORA-06512: at "HR.SDSFTP", line 236
ORA-06512: at line 17
ORA-24247: network access denied by access control list (ACL)
all the privs have been given to the package owner ie HR..
The FTP server and the database reside on the same machine..(ie windows xp)
I am able to access the ftp server using ftp://localhost or ftp://<hostname>
not sure why I am getting the errors..
Please let me know if you have any ideas to trouble shoot this..
thanks
I compiled the package provided in sdstuber's blog..
https://www.experts-exchange.com/Database/Oracle/PL_SQL/A_3043-How-to-FTP-with-Oracle-PL-SQL.html
when I execute the following piece of code to test it..I get the following error
DECLARE
v_conn sdsftp.connection;
BEGIN
sdsftp.clear_log;
sdsftp.set_log_options(1);
v_conn := sdsftp.open('localhost','<user_name>', '<pwd>');
DBMS_OUTPUT.put_line(
'-------------------------
);
DBMS_OUTPUT.put_line(sdsftp.get_clob(v_conn, 'test.txt'));
DBMS_OUTPUT.put_line(
'-------------------------
);
sdsftp.close(v_conn);
EXCEPTION
WHEN OTHERS THEN
sdsftp.close(v_conn);
RAISE;
END;
/
ORA-29260: network error: not connected
ORA-06512: at "HR.SDSFTP", line 432
ORA-06512: at "HR.SDSFTP", line 236
ORA-06512: at line 17
ORA-24247: network access denied by access control list (ACL)
all the privs have been given to the package owner ie HR..
The FTP server and the database reside on the same machine..(ie windows xp)
I am able to access the ftp server using ftp://localhost or ftp://<hostname>
not sure why I am getting the errors..
Please let me know if you have any ideas to trouble shoot this..
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
I am using 11g r2
ASKER
upon executing the following code..i am getting the above error..
BEGIN
--DBMS_NETWORK_ACL_ADMIN.d rop_acl('m y_ip_looku p_acl.xml' );
DBMS_NETWORK_ACL_ADMIN.cre ate_acl(
acl => 'my_ip_lookup_acl.xml',
description => 'ACL that lets users do ip/name look ups for any host',
principal => 'HR',
is_grant => TRUE,
privilege => 'resolve'
);
DBMS_NETWORK_ACL_ADMIN.ass ign_acl('m y_ip_looku p_acl.xml' , '*');
COMMIT;
END;
BEGIN
--DBMS_NETWORK_ACL_ADMIN.d
DBMS_NETWORK_ACL_ADMIN.cre
acl => 'my_ip_lookup_acl.xml',
description => 'ACL that lets users do ip/name look ups for any host',
principal => 'HR',
is_grant => TRUE,
privilege => 'resolve'
);
DBMS_NETWORK_ACL_ADMIN.ass
COMMIT;
END;
you must not have access to execute dbms_network_acl_admin
you'll have to ask your dba to create the acl for you and assign it to the hosts you need
you'll have to ask your dba to create the acl for you and assign it to the hosts you need
ASKER
its a machine on my local desktop..
as sys, i granted execute permission to hr
now i am getting following error..
ORA-31003: Parent /sys/acls/ already contains child entry my_ip_lookup_acl.xml
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMI N", line 252
ORA-06512: at line 3
Thanks
as sys, i granted execute permission to hr
now i am getting following error..
ORA-31003: Parent /sys/acls/ already contains child entry my_ip_lookup_acl.xml
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMI
ORA-06512: at line 3
Thanks
ASKER
i executed the open connection now..and i am able to read the file now..Sweet!
This is fantastic!
This is fantastic!
>>> ORA-31003: Parent /sys/acls/ already contains child entry my_ip_lookup_acl.xml
if you ran multiple examples this could happen since you can only create an acl once. If you try again, you'll get the error above, simply ignore it if you want to keep any assignments and privileges already associated with it, or drop and recreate if you want to start fresh.
>>> i executed the open connection now..and i am able to read the file now..Sweet!
glad I could help
if you want to explore ACL's more, I recommend reading both part 1 linked above and part 2.
if you ran multiple examples this could happen since you can only create an acl once. If you try again, you'll get the error above, simply ignore it if you want to keep any assignments and privileges already associated with it, or drop and recreate if you want to start fresh.
>>> i executed the open connection now..and i am able to read the file now..Sweet!
glad I could help
if you want to explore ACL's more, I recommend reading both part 1 linked above and part 2.
ASKER
Do we hit a limitation of reading/writing files more than 32 KB here?
Thanks
Thanks
Not if using the clob/blob transfers.
However, the ftp server you are connecting to may have limitations, this client package can't circumvent those, but I would surprised at one that had a limit as small as 32K
However, the ftp server you are connecting to may have limitations, this client package can't circumvent those, but I would surprised at one that had a limit as small as 32K
ASKER
Unfortunately the solution doesnt work in our production database as it cannot connect to any outside ftp sites..there is just one box with which prod db can talk and the files are picked from that box or transferred to that box for any data exchange with external world..Not sure how to implement the above solution in our scenario..Please share with me if there is any work around..
I truely appreciate your help..I voted all the articles I have read from you that helped me..
Thanks..
I truely appreciate your help..I voted all the articles I have read from you that helped me..
Thanks..
if your server is blocked then there isn't much you could do unless you could possibly connect to the server where external communication is allowed, let it do the ftp transfers and you simply shuttle data to/from it with db links. I don't know if that's a possibility or not. First, does the other server have a database on it? and second would you be able to install the ftp package on it? if either answer is no, then you'll probably have to resort to local file transfers and then scripting but at that point it's not really a database solution at all.
ASKER
For now I think we cannot have a dbms solution atleast in our production environment..
Thanks
Thanks
ASKER
ORA-06550: line 3, column 5:
PLS-00201: identifier 'DBMS_NETWORK_ACL_ADMIN' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored
ORA-06550: line 11, column 5:
PLS-00201: identifier 'DBMS_NETWORK_ACL_ADMIN' must be declared
ORA-06550: line 11, column 5:
PL/SQL: Statement ignored
Thanks