Solved

ORA-29260/ORA-24247

Posted on 2012-03-11
13
3,298 Views
Last Modified: 2012-03-11
I am trying to read a file on a ftp server..

I compiled the package provided in sdstuber's blog..

http://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
0
Comment
Question by:gs79
  • 8
  • 5
13 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37706331
I'm glad you found the FTP article helpful! Thanks for reading!
 I hope you voted.

You must be running in 11g.

Check out one of my other articles...


http://www.experts-exchange.com/A_8429.html

I discuss this error explicitly as well as show how to address it
0
 

Author Comment

by:gs79
ID: 37706378
I tried both "open access" and target user acl program in the link..Its throwing the following error:

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
0
 

Author Comment

by:gs79
ID: 37706380
I am using 11g r2
0
 

Author Comment

by:gs79
ID: 37706383
upon executing the following code..i am getting the above error..

BEGIN
    --DBMS_NETWORK_ACL_ADMIN.drop_acl('my_ip_lookup_acl.xml');
    DBMS_NETWORK_ACL_ADMIN.create_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.assign_acl('my_ip_lookup_acl.xml', '*');
    COMMIT;
END;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37706399
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
0
 

Author Comment

by:gs79
ID: 37706406
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_ADMIN", line 252
ORA-06512: at line 3


Thanks
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:gs79
ID: 37706407
i executed the open connection now..and i am able to read the file now..Sweet!

This is fantastic!
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37706410
>>>  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.
0
 

Author Comment

by:gs79
ID: 37706437
Do we hit a limitation of reading/writing files more than 32 KB here?

Thanks
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37706960
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
0
 

Author Comment

by:gs79
ID: 37707705
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..
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37707856
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.
0
 

Author Comment

by:gs79
ID: 37708138
For now I think we cannot have a dbms solution atleast in our production environment..

Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now