Hello Great Team,
Hope you are doing Great and keeping great health. I would really appreciate if you can help go past the following issue. Thanks in advance.

I am in the process of creating a text file using utl_file(to be used by SQLLDR later to load into the DB) from a table having 2 columns, a Number and a clob column. While using the dbms_sql.column_value method for reading the value of CLOB, I am getting "ORA-20050: ORA-06502: PL/SQL: numeric or value error". Here is the code snippet..
Create table work (ID Number, source Clob);
Stored Proc to create the file out of the above table:

=====================================================
CREATE OR REPLACE procedure create_flat_file_with_clob(p_err_text out Varchar2)
is
l_output   utl_file.file_type;
l_theCursor   integer default dbms_sql.open_cursor;
l_columnValue   varchar2(4000);
l_status   integer;
l_colCnt   number default 0;
l_separator   varchar2(10) default ',';
l_fileLineCnt   number :=0;
l_descTbl        dbms_sql.desc_tab;
l_query   varchar2(32767);
lb_clob_val      CLOB ;
Begin
  l_query :='select id,source from work';  
  dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
  dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
  For i in 1 .. l_colCnt loop
       If l_descTbl(i).col_type <> '112' Then  --Not a clob column
      dbms_sql.define_column( l_theCursor, i,    l_columnValue, 4000 );
     Else
      dbms_sql.define_column( l_theCursor,i, lb_clob_val );
     End If;
     l_colCnt := i;
  End Loop;
  l_status := dbms_sql.execute(l_theCursor);
  Loop
 Exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
 l_output := utl_file.fopen_nchar( 'WORK_DIR', 'work_data_files.dat', 'W',32767 );
        For i in 1 .. l_colCnt loop
  If l_descTbl(i).col_type <> '112' Then  --Non Clob Data Type
     dbms_sql.column_value( l_theCursor, i,l_columnValue );
     utl_file.put_nchar( l_output, l_separator ||l_columnValue );    
  Else      --for the Clob Data Type
      lb_clob_val := Null;
      dbms_sql.column_value( l_theCursor, i,lb_clob_val );  ---*** read the data into Clob
      ---there is a logic to read this clob in chunks of 32767 in a loop and append it to the l_output
      utl_file.put_nchar( l_output, l_separator ||lb_clob_val );    
  End If;
 End loop;
 utl_file.new_line( l_output );
     utl_file.fclose( l_output );
   End loop;
   dbms_sql.close_cursor(l_theCursor);
   utl_file.fclose( l_output );
Exception When Others Then
   p_err_text := 'Error from create_flat_file_with_clob procedure:'||sqlerrm;
 raise_application_error(-20050,'Error from within create_flat_file_with_clob procedure:'||sqlerrm);
end create_flat_file_with_clob;
/
While executing the above SP, I am getting the following error at line corresponding with ---*** read the data into Clob
-----------------------
SQL>variable err varchar2(4000);
SQL>Begin
  2     create_flat_file_with_clob(:err);
  3  Exception When Others Then
  4     dbms_output.put_line('*****Exception ***'||sqlerrm);
  5  End;
  6  /
*****Exception ***ORA-20050: Error from within create_flat_file_with_clob procedure:ORA-06502: PL/SQL: numeric or value error

----------------------
Please let me know where the issue is..

As always, appreciate the help and the work you guys are doing.
Regards,
Vijay

SQL> Create table work (ID Number, source Clob);

Table created.

SQL>
SQL> insert into work values (1,rpad('x',100));

1 row created.

SQL> insert into work values (2,rpad('x',10000));

1 row created.

SQL> insert into work values (3,rpad('x',100000));

1 row created.

SQL>
SQL> set serverout on
SQL> declare
  2    l_output   utl_file.file_type;
  3    l_theCursor   integer default dbms_sql.open_cursor;
  4    l_columnValue   varchar2(4000);
  5    l_status   integer;
  6    l_colCnt   number default 0;
  7    l_separator   varchar2(10) default ',';
  8    l_fileLineCnt   number :=0;
  9    l_descTbl        dbms_sql.desc_tab;
 10    l_query   varchar2(32767);
 11    lb_clob_val      CLOB ;
 12  Begin
 13    l_query :='select id,source from work';
 14    dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
 15    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 16    For i in 1 .. l_colCnt loop
 17         If l_descTbl(i).col_type <> '112' Then  --Not a clob column
 18        dbms_sql.define_column( l_theCursor, i,    l_columnValue, 4000 );
 19       Else
 20        dbms_sql.define_column( l_theCursor,i, lb_clob_val );
 21       End If;
 22       l_colCnt := i;
 23    End Loop;
 24    l_status := dbms_sql.execute(l_theCursor);
 25    Loop
 26   Exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
 27   l_output := utl_file.fopen_nchar( 'TEMP', 'work_data_files.dat', 'W',32767 );
 28          For i in 1 .. l_colCnt loop
 29    If l_descTbl(i).col_type <> '112' Then  --Non Clob Data Type
 30       dbms_sql.column_value( l_theCursor, i,l_columnValue );
 31       utl_file.put_nchar( l_output, l_separator ||l_columnValue );
 32    Else      --for the Clob Data Type
 33        lb_clob_val := Null;
 34        dbms_sql.column_value( l_theCursor, i,lb_clob_val );  ---*** read the data into Clob
 35        ---there is a logic to read this clob in chunks of 32767 in a loop and append it to the l_output
 36        utl_file.put_nchar( l_output, l_separator ||lb_clob_val );
 37    End If;
 38
 39    dbms_output.put_line('Element done OK');
 40   End loop;
 41   utl_file.new_line( l_output );
 42       utl_file.fclose( l_output );
 43     End loop;
 44     dbms_sql.close_cursor(l_theCursor);
 45     utl_file.fclose( l_output );
 46  end;
 47  /
Element done OK
Element done OK
Element done OK
Element done OK
Element done OK
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 36


SQL>
SQL>