5 Replies Latest reply on Jun 17, 2019 8:24 AM by cormaco

    When calling the convert_to_clob, getting the “plsql numeric or value error”.



      When we call the below procedure, we are getting the ORA-06502 plsql numeric or value error”.

      We are sending data on a pdf file and try to convert to a CLOB output file.




      Below is the source code for the procedure:


      PROCEDURE convert_to_clob(

        p_file_blob IN BLOB

      ,p_output_file OUT NOCOPY CLOB





        ln_blob_length NUMBER;

      ln_amount BINARY_INTEGER := 10000; -- must be <= ~32765.

      ln_offset INTEGER := 1;

      lraw_buffer          RAW(20000);

        lc_text_buffer CLOB; --VARCHAR2(32767);

        ln_charbuff_size     NUMBER;

        lc_output_file CLOB;




           ln_blob_length := DBMS_LOB.GETLENGTH(p_file_blob);

      dbms_lob.createTemporary(lc_output_file, FALSE, dbms_lob.call);


           -- We'll loop through the BLOB as many times as necessary to

           -- get all its data.

           FOR i IN 1..CEIL(ln_blob_length/ln_amount) LOOP


             -- Read in the given chunk of the BLOB.


      , ln_amount

      , ln_offset

      , lraw_buffer);


             -- The DBMS_LOB.READ procedure dictates that its output be RAW.

             -- This next procedure converts that RAW data to character data.

             lc_text_buffer := UTL_RAW.CAST_TO_VARCHAR2(lraw_buffer);


             ln_charbuff_size := length(lc_text_buffer);


      dbms_lob.writeAppend(lc_output_file, ln_charbuff_size, lc_text_buffer);


             -- For the next iteration through the BLOB, bump up your offset

             -- location (i.e., where you start reading from).

             ln_offset := ln_offset + ln_amount;


           END LOOP;


           debug_autonomous.record_details(1,'before assigning attach xml');

      debug_autonomous.record_details(1,'Attach xml ',lc_output_file); 


           p_output_file := TO_CLOB(lc_output_file);

           --RETURN gc_output_file;


      END convert_to_clob