why would you change the length of a string in response to "invalid number"????

Anyway, my line 12 is a "end loop".  what is YOUR line 12 (after putting the string BACK to long so as to get back to the original error, instead of the self induced error of "string too small" you have).

What modifications to my code did you make?  perhaps you should post a complete example we can all run (in the manner of my example)

did you run my example "as is" -- this block:

ops$tkyte@ORA815.US.ORACLE.COM> declare
  2      type array is table of varchar2(50);
  3  
  4      l_dir_names  array := array( 'c:\hrms\docs', 'c:\hrms\forms' );
  5      l_dir_cnt    number default l_dir_names.count;
  6      l_extns      array := array( 'doc', 'xls', 'sql', 'fmb', 'txt' );
  7      l_extn_cnt   number default l_extns.count;
  8  begin
  9      for i in 1 .. 1000
 10      loop
 11          insert into t values
 12          ( l_dir_names(mod( i, l_dir_cnt ) +1 ),
 13            null,
 14            l_extns( mod(i, l_extn_cnt ) +1 ) );
 15      end loop;
 16      commit;
 17  end;
 18  /

will generate test data for you in 815 (i ran this test in 815, my code works fine, must be some modification you added to my code, or some difference in the reality of your tables and the tables you discussed in the question)

 

time for you to do some debugging,  use a procedure like:

create or replace procedure p ( p_str in varchar2 )
is
   l_str   long := p_str;
begin
   loop
      exit when l_str is null;
      dbms_output.put_line( substr( l_str, 1, 250 ) );
      l_str := substr( l_str, 251 );
   end loop;
end;
/

to print out the query and run the query standalone in sqlplus.  I cannot  reproduce.  I just:

ops$tkyte@ORA815.US.ORACLE.COM> create table vc_dir_sts_tbl (directory_name varchar2(10), file_name varchar2(10), extn varchar2(10) );

Table created.

ops$tkyte@ORA815.US.ORACLE.COM> insert into vc_dir_sts_tbl select substr(username,1,10), substr(username,1,10), substr(username,1,3) from all_users;

14 rows created.

ops$tkyte@ORA815.US.ORACLE.COM> Create or Replace package demo_pkg
  2   as
  3       type rc is ref cursor;
  4       procedure get_cursor( p_cursor in out rc );
  5   end;
  6  /

Package created.

ops$tkyte@ORA815.US.ORACLE.COM> Create or Replace package body demo_pkg
  2  as
  3  procedure get_cursor( p_cursor in out rc )
  4  is
  5      l_query LONG := 'select directory_name, count(*)';
  6  begin
  7      for x in ( select distinct extn from VC_DIR_STS_TBL )
  8     loop
  9        l_query := l_query || ',SUM(decode(extn,'''||x.extn||''',1,0)) ' || x.extn || 'fls';
 10     end loop;
 11     l_query := l_query || ' from VC_DIR_STS_TBL group by directory_name';
 12     p(l_query);
 13     open p_cursor for l_query;
 14  end;
 15  end;
 16  /

Package body created.

ops$tkyte@ORA815.US.ORACLE.COM> 
ops$tkyte@ORA815.US.ORACLE.COM> variable x refcursor
ops$tkyte@ORA815.US.ORACLE.COM> exec demo_pkg.get_cursor(:x);
select directory_name, count(*),SUM(decode(extn,'A',1,0)) Afls,SUM(decode(extn,'AUR',1,0)) AURfls,SUM(decode(extn,'B',1,0))
Bfls,SUM(decode(extn,'DBS',1,0)) DBSfls,SUM(decode(extn,'DEV',1,0)) DEVfls,SUM(decode(extn,'GSC',1,0)) GSCfls,SUM(decode(extn,
'OPS',1,0)) OPSfls,SUM(decode(extn,'OUT',1,0)) OUTfls,SUM(decode(extn,'SCO',1,0)) SCOfls,SUM(decode(extn,'SYS',1,0))
SYSfls,SUM(decode(extn,'USE',1,0)) USEfls from VC_DIR_STS_TBL group by directory_name

PL/SQL procedure successfully completed.

ops$tkyte@ORA815.US.ORACLE.COM> print x

DIRECTORY_   COUNT(*)       AFLS     AURFLS       BFLS     DBSFLS     DEVFLS     GSCFLS     OPSFLS     OUTFLS     SCOFLS     SYSFLS
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    USEFLS
----------
A                   1          1          0          0          0          0          0          0          0          0          0
         0         0          0          0          0          0
         0


Take the query produced in text and run it and see what happens.  This method 

a) works
b) works well
c) works in 815

<b>update on this one... just figured it out.  You have an extn that isn't a valid identifier in Oracle...</b>

consider:

ops$tkyte@ORA815.US.ORACLE.COM> insert into vc_dir_sts_tbl values ( 3, 3, 3 );

1 row created.

ops$tkyte@ORA815.US.ORACLE.COM> variable x refcursor
ops$tkyte@ORA815.US.ORACLE.COM> exec demo_pkg.get_cursor(:x);
select directory_name, count(*),SUM(decode(extn,'3',1,0)) 3fls,SUM(decode(extn,'A',1,0)) Afls,SUM(decode(extn,'AUR',1,0))
AURfls,SUM(decode(extn,'B',1,0)) Bfls,SUM(decode(extn,'DBS',1,0)) DBSfls,SUM(decode(extn,'DEV',1,0)) DEVfls,SUM(decode(extn,'GSC
',1,0)) GSCfls,SUM(decode(extn,'OPS',1,0)) OPSfls,SUM(decode(extn,'OUT',1,0)) OUTfls,SUM(decode(extn,'SCO',1,0))
SCOfls,SUM(decode(extn,'SYS',1,0)) SYSfls,SUM(decode(extn,'USE',1,0)) USEfls from VC_DIR_STS_TBL group by directory_name
BEGIN demo_pkg.get_cursor(:x); END;

*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "OPS$TKYTE.DEMO_PKG", line 13
ORA-06512: at line 1


there is your error , it is caused by this "SUM(decode(extn,'3',1,0)) 3fls"

solution:

  9        l_query := l_query || ',SUM(decode(extn,'''||x.extn||''',1,0)) "' || x.extn || 'fls"';


use QUOTED identifiers (with double quotes).   that'll do it.