4 Replies Latest reply on Oct 25, 2018 1:38 AM by Allen Sandiego

    Unable to return CLOB properly.

    Allen Sandiego

      Hi,

       

      I have a file (78c879924c020812e053748216ac354f.tmp) stored in $APPLTMP which contains the ff:

       

      "JOB_TEMPLATE_NUMBER"
      "5596"
      "5581"
      "5576"
      "5575"
      "5597"
      "5598"
      "5552"
      "5535"
      "5577"
      "5551"
      "5579"
      "5582"
      

       

      When trying return the output of the file, I do the ff:

       

      declare
        l_bfile bfile;
        l_clob clob;
        
        procedure print_clob( p_clob in clob ) is
          v_offset number := 1;
          v_chunk_size number := 10000;
        begin
          loop
            exit when v_offset > dbms_lob.getlength(p_clob);
            dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
            v_offset := v_offset + v_chunk_size;
          end loop;
        end print_clob;
        
      begin
        dbms_output.enable(null);
          dbms_lob.createtemporary ( l_clob, true );
          l_bfile := bfilename ( 'APPLTMP', '78c879924c020812e053748216ac354f.tmp' );
          dbms_lob.fileopen ( l_bfile );
          dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ) );
          dbms_lob.fileclose( l_bfile );
          
          print_clob(l_clob);
      end;
      

       

      However, the result is not correct as I get different characters.

       

      APPS@hrdev:SQL>set serveroutput on
      APPS@hrdev:SQL>declare
        2    l_bfile bfile;
        3    l_clob clob;
        4
        5    procedure print_clob( p_clob in clob ) is
        6      v_offset number := 1;
        7      v_chunk_size number := 10000;
        8    begin
        9      loop
       10        exit when v_offset > dbms_lob.getlength(p_clob);
       11        dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
       12        v_offset := v_offset + v_chunk_size;
       13      end loop;
       14    end print_clob;
       15
       16  begin
       17    dbms_output.enable(null);
       18      dbms_lob.createtemporary ( l_clob, true );
       19      l_bfile := bfilename ( 'APPLTMP', '78c879924c020812e053748216ac354f.tmp' );
       20      dbms_lob.fileopen ( l_bfile );
       21      dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ) );
       22      dbms_lob.fileclose( l_bfile );
       23
       24      print_clob(l_clob);
       25  end;
       26  /
      ≊佂彔䕍偌䅔䕟乕䵂䕒∊∵㔹㘢ਢ㔵㠱∊∵㔷㘢ਢ㔵㜵∊∵㔹㜢ਢ㔵㤸∊∵㔵㈢ਢ㔵㌵∊∵㔷㜢
      ਢ㔵㔱∊∵㔷㤢ਢ㔵㠲∊
      
      
      PL/SQL procully completed.
      

       

      Does anyone know why this is happening?

       

      Regards,

      Allen

        • 1. Re: Unable to return CLOB properly.
          John_K

          Any particular reason you need to do it like this, rather than just read the data using an external table?

          • 2. Re: Unable to return CLOB properly.
            Allen Sandiego

            Multiple users can run the program that runs the stored procedure in parallel and each run generates a random uuid filename in $APPLTMP.

             

            Also, the data changes in format. It can be an xml data, csv data or html data.

            • 3. Re: Unable to return CLOB properly.
              John_K

              Ah ok; so I presume you have some other process which is writing these files etc;

               

              However - I've just tried your process on a system I have here, and it works fine. I read a text file that's about 31k in length, all worked fine. Are you sure you process that is writing the files isn't writing some control characters in there or anything? You check that with the following unix command:

               

              od -a 78c879924c020812e053748216ac354f.tmp
              
              • 4. Re: Unable to return CLOB properly.
                Allen Sandiego

                I tried the command as suggested. Here's what I got.

                 

                 

                [las@lvdverp ~]$ od -a $APPLTMP/78c879924c020812e053748216ac354f.tmp
                0000000   "   J   O   B   _   T   E   M   P   L   A   T   E   _   N   U
                0000020   M   B   E   R   "  nl   "   5   5   9   6   "  nl   "   5   5
                0000040   8   1   "  nl   "   5   5   7   6   "  nl   "   5   5   7   5
                0000060   "  nl   "   5   5   9   7   "  nl   "   5   5   9   8   "  nl
                0000100   "   5   5   5   2   "  nl   "   5   5   3   5   "  nl   "   5
                0000120   5   7   7   "  nl   "   5   5   5   1   "  nl   "   5   5   7
                0000140   9   "  nl   "   5   5   8   2   "  nl
                0000152
                

                 

                I also tried the following:

                 

                declare
                  l_file utl_file.file_type;
                  l_line varchar2(32767);
                begin
                  l_file := utl_file.fopen ( 'APPLTMP','78c879924c020812e053748216ac354f.tmp', 'r' );
                  loop
                    utl_file.get_line ( l_file, l_line );
                    dbms_output.put_line ( l_line );
                  end loop;
                  utl_file.fclose ( l_file );
                exception
                  when no_data_found then
                    utl_file.fclose ( l_file );
                end;
                /
                

                 

                And correctly got the content.

                 

                "Line","Pos","Text"
                1,,"PL/SQL block, executed in 0 ms"
                ,,"""JOB_TEMPLATE_NUMBER"""
                ,,"""5596"""
                ,,"""5581"""
                ,,"""5576"""
                ,,"""5575"""
                ,,"""5597"""
                ,,"""5598"""
                ,,"""5552"""
                ,,"""5535"""
                ,,"""5577"""
                ,,"""5551"""
                ,,"""5579"""
                ,,"""5582"""
                ,,Total execution time 67 ms
                

                 

                So I'm thinking the problem is with the bfile.