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”.

    kp2000

      Hello:

      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.

      convert_to_clob(lr_get_po_file.file_data,gc_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

      )

      --RETURN CLOB

      IS

       

        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;

       

      BEGIN

       

           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.

      DBMS_LOB.READ(p_file_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

       

       

       

      Thanks

      kp