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

FTP package p_nlst function

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Eric G
Member
Posts: 3
Joined: Mon Aug 05, 2013 2:21 pm

FTP package p_nlst function

Postby Eric G » Mon Aug 05, 2013 2:51 pm

Tim,

We have been using your ftp package (http://www.oracle-base.com/dba/miscellaneous/ftp.pkb) for some time. Last week we came across a problem with the p_nlst function.

Code: Select all

   PROCEDURE p_nlst (p_conn   IN OUT NOCOPY UTL_TCP.connection,
                     p_dir    IN            VARCHAR2,
                     p_list      OUT        t_string_table)
   AS
      -- --------------------------------------------------------------------------
      l_conn         UTL_TCP.connection;
      l_list         t_string_table := t_string_table ();
      l_reply_code   VARCHAR2 (3) := NULL;
   BEGIN
      l_conn := f_get_passive (p_conn);
      p_send_command (p_conn, 'NLST ' || p_dir, TRUE);

      BEGIN
         LOOP
            l_list.EXTEND;
            l_list (l_list.LAST) := UTL_TCP.get_line (l_conn, TRUE);
            p_debug (l_list (l_list.LAST));

            IF l_reply_code IS NULL
            THEN
               l_reply_code := SUBSTR (l_list (l_list.LAST), 1, 3);
            END IF;

            IF SUBSTR (l_reply_code, 1, 1) IN ('4', '5')
            THEN
               RAISE_APPLICATION_ERROR(-20000, l_list(l_list.last));
               NULL;
            ELSIF (    SUBSTR (g_reply (g_reply.LAST), 1, 3) = l_reply_code
                   AND SUBSTR (g_reply (g_reply.LAST), 4, 1) = ' ')
            THEN
               EXIT;
            END IF;
         END LOOP;
      EXCEPTION
         WHEN UTL_TCP.end_of_input
         THEN
            NULL;
      END;

      l_list.delete (l_list.LAST);
      p_list := l_list;

      UTL_TCP.close_connection (l_conn);
      p_get_reply (p_conn);
   END;


The problem is that the file that we are using are number generated that increment by 1 every time a new file is created starting with 10000000 (10000001, 10000002, 10000003, etc...). The files have finally made it to 12500000 and this is where the problem starts.

125 is also the code for "Data connection already open; Transfer starting."

What is happening is that during the function g_reply is stored with "125 Data connection already open; Transfer starting" when calling p_send_command that calls p_get_reply.

The code then begins to read the files and puts them in l_list and outputs the data. The first time that the code is "walked" through l_reply_code is null so it stores the first three characters of the file name into l_reply_code, which is 125. It then checks g_reply first three characters against l_reply_code and they match as well as g_reply fourth character equaling ' '. It then exits the loop and deletes the last entry into the l_list array (that contains the file name) and exits the function.

The function would only "not work" when the first file read started with 125. When a file name was lower then 125, example 12400850, it would work because l_reply_code would have 124 and it would not be equal to 125.

For a quick fix we added one (1) more check to the function:

Code: Select all

ELSIF (    SUBSTR (g_reply (g_reply.LAST), 1, 3) = l_reply_code
                   AND SUBSTR (g_reply (g_reply.LAST), 4, 1) = ' ')
                   AND INSTR(l_reply_code,'already open') > 0


If you need more information please let me know.

Thank you,
Eric

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

Re: FTP package p_nlst function

Postby Tim... » Wed Aug 07, 2013 4:16 pm

Hi.

Thanks for the feedback. I'm away from home at the moment, so I will check this out when I get back to the UK.

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

Eric G
Member
Posts: 3
Joined: Mon Aug 05, 2013 2:21 pm

Re: FTP package p_nlst function

Postby Eric G » Thu Aug 08, 2013 2:14 pm

Thank you for getting back to me.

After looking at it some more if the file name begins with a 4 or 5 you will get an error

Code: Select all

IF SUBSTR (l_reply_code, 1, 1) IN ('4', '5')
            THEN
               RAISE_APPLICATION_ERROR(-20000, l_list(l_list.last));
               NULL;

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

Re: FTP package p_nlst function

Postby Tim... » Thu Aug 08, 2013 4:42 pm

OK. I've put this into my to-do list for when I get home.

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

Eric G
Member
Posts: 3
Joined: Mon Aug 05, 2013 2:21 pm

Re: FTP package p_nlst function

Postby Eric G » Fri Oct 04, 2013 6:40 pm

Tim,

Did you have a chance to look into this yet?

Eric

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

Re: FTP package p_nlst function

Postby Tim... » Sat Oct 05, 2013 3:53 pm

Hi.

I was able to replicate your issue and I believe the code is fixed now. You can get the latest package body with the fix here.

http://www.oracle-base.com/dba/miscellaneous/ftp.pkb

The test for a reply code was flawed.

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