This forum is currently locked. You can't register or post questions at this time. (read more)

Error in LOAD_DATA; code: -29260 message: ORA-29260: network

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

mailtopiyushk
Member
Posts: 15
Joined: Tue Apr 05, 2011 7:36 am

Error in LOAD_DATA; code: -29260 message: ORA-29260: network

Postby mailtopiyushk » Thu Mar 28, 2013 10:55 am

Hi Tim,
Got below error, Please help :)

org_id 83
database_name PSUP
Directory /db/PSUP/Piyush/Data
Archive Directory /db/PSUP/Piyush/Archive
before l_conn
Error in LOAD_DATA; code: -29260 message: ORA-29260: network error: Connect failed because target host or object does not exist
==============================================================================================================

Code: Select all

CREATE OR REPLACE PROCEDURE LOAD_DATA IS
     
   l_ra_file                 UTL_FILE.FILE_TYPE;
   l_end_of_file             BOOLEAN;
   l_file_id_seq             NUMBER;
   l_file_name               VARCHAR2 (100);
   
   l_conn                    UTL_TCP.connection;
   TYPE t_string_table       IS TABLE OF VARCHAR2(32767);
   l_list1                   t_string_table := t_string_table();
   l_reply_code              VARCHAR2(3) := NULL;
   l_list                    ftp.t_string_table;
   
   p_directory               VARCHAR2(500);
   p_archivedir              VARCHAR2(500);
   l_database_name           VARCHAR2(50);
   l_org_id                  NUMBER;
   
BEGIN
 
   SELECT TO_NUMBER (fnd_profile.VALUE ('ORG_ID'))
   INTO   l_org_id
   FROM   DUAL;
   
   DBMS_OUTPUT.put_line ('org_id '|| l_org_id);
   
   SELECT NAME
   INTO   l_database_name
   FROM   V$DATABASE;
   
   DBMS_OUTPUT.put_line ('database_name '|| l_database_name);
   
   BEGIN
      IF l_database_name = 'PSUP' THEN
        --p_directory := '/mnt/dev_interfaces/inbound/MCS_I49_BANK_O_PCARD_AP/';
         --p_archivedir := '/mnt/dev_interfaces/archive/MCS_I49_BANK_O_PCARD_AP/';    
         p_directory := '/db/PSUP/Piyush/Data';
         p_archivedir := '/db/PSUP/Piyush/Archive';
      ELSIF l_database_name = 'INT' THEN
         p_directory := '/mnt/int_interfaces/inbound/MCS_SCF_I_REMITTANCE_ADV/';
         p_archivedir := '/mnt/int_interfaces/archive/MCS_SCF_I_REMITTANCE_ADV/';
      ELSIF l_database_name = 'INT4_806_BALANCE' THEN
         p_directory := '/mnt/int4_806_balance_interfaces/inbound/MCS_SCF_I_REMITTANCE_ADV/';
         p_archivedir := '/mnt/int4_806_balance_interfaces/archive/MCS_SCF_I_REMITTANCE_ADV/';
      ELSIF l_database_name = 'PROD_806_BALANCE' THEN
         p_directory := '/mnt/prod_806_balance_interfaces/inbound/MCS_SCF_I_REMITTANCE_ADV/';
         p_archivedir := '/mnt/prod_806_balance_interfaces/archive/MCS_SCF_I_REMITTANCE_ADV/';
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         dbms_output.put_line('Database: '|| l_database_name ||
                           ' Directory: ' || p_directory ||
                           ' Archive Directory: ' ||p_archivedir ||
                           ' does not exist ; code: ' || SQLCODE || ',message:' || SQLERRM);
   END;
   
   DBMS_OUTPUT.put_line ('Directory '|| p_directory);
   DBMS_OUTPUT.put_line ('Archive Directory '|| p_archivedir);
   
   DBMS_OUTPUT.put_line ('before l_conn');       
   
       l_conn := ftp.login( 'supp.pspl.com'   -- server name
                           ,'21'                   -- port
                           ,'orapspl'              -- username
                           ,'orapspl'           -- password
                    );
   
     DBMS_OUTPUT.put_line ('connection established');                         
                         
      ftp.ASCII (p_conn => l_conn); 
             
   
      ftp.nlst(p_conn   => l_conn,
               p_dir   => p_directory,
               p_list  => l_list);
               
      --ftp.logout(l_conn);

      IF l_list.COUNT > 0 THEN
         FOR i IN l_list.FIRST .. l_list.LAST
         LOOP   -- file count loop
               DBMS_OUTPUT.put_line(l_list(i));
            --l_file_name := LTRIM(RTRIM(SUBSTR(l_list(i),39,69)));
            l_file_name := l_list(i);
     
            -- Transfer the file to archive directory           
           
            --IF (l_end_of_file) THEN
                ftp.put ( p_conn           => l_conn
                         ,p_from_dir       => p_directory
                         ,p_from_file      => l_file_name
                         ,p_to_file        => p_archivedir || l_file_name || '-' || SYSDATE
                        );         
              --END IF;
       
         END LOOP; -- end file count loop
     
      ELSE  -- l_list.COUNT < 0
         --fnd_file.put_line('Files not present on the server; code: ' || SQLCODE || ',message:' || SQLERRM);
         dbms_output.put_line('Files not present on the server; code: ' || SQLCODE || ',message:' || SQLERRM);
       
      END IF;
     
      ftp.logout(l_conn);
         
EXCEPTION
      WHEN OTHERS THEN
         --fnd_file.put_line ('Error in LOAD_DATA; ' || 'code: ' || SQLCODE || 'message: ' || SQLERRM);
         DBMS_OUTPUT.put_line ('Error in LOAD_DATA; ' || 'code: ' || SQLCODE || ' message: ' || SQLERRM);

END LOAD_DATA;

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Error in LOAD_DATA; code: -29260 message: ORA-29260: net

Postby Tim... » Thu Mar 28, 2013 12:03 pm

Hi.

Not really sure what I can tell you. The error message kinda says it all.

"Connect failed because target host or object does not exist"

So it seems to me the host doesn't exist. :) That means you have to check that the host does actually exist and it is accessible from your server. You need to make sure there are not firewalls blocking it. You also need to make sure the FTP service is running on that host.

The easiest way to test this is to log on to the command line on the database server and make a command line FTP connection. If that works, there shouldn't be any reason why you can't make that connection from PL/SQL. Of course, if it fails from the command line, then it isn't going to work from PL/SQL.

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

mailtopiyushk
Member
Posts: 15
Joined: Tue Apr 05, 2011 7:36 am

Re: Error in LOAD_DATA; code: -29260 message: ORA-29260: net

Postby mailtopiyushk » Thu Mar 28, 2013 12:31 pm

Hi Tim,
Thanks for quick reply :).
I have pinged from command line and look like its working file. Now its fiving another error
ORA-29260: network error: TNS:NO listener

Look like its firewall issue.

Regards,
Piyush

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Error in LOAD_DATA; code: -29260 message: ORA-29260: net

Postby Tim... » Thu Mar 28, 2013 1:51 pm

Hi.

Well, ping isn't really what I said. I said test that FTP works from the command line. Ping just proves the box is there and you can ping it. It doesn't prove that the remote machine has an FTP server running on it and that you have firewall access to use it...

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 4 guests