UTL_FILE throwing ORA-06502: PL/SQL: Numeric or value error

Hi,

I am trying to write a large file from a CLOB variable to an XML using UTL_FILE dir. I am getting ORA-06502: PL/SQL: numeric or value error at UTL_FILE.put. When I researched I found that UTL_FILE throws an exception for a large CLOB while writing. I am unable to find an alternate solution. Could you please let me know if there is a way to write a large XML using UTL_FILE or any other alternative?

Thanks in advance!

Raj.
nssudhaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
try something like this...


DECLARE
    c_rawlimit CONSTANT INTEGER := 32767;
    v_file              UTL_FILE.file_type;
    v_dest_blob         BLOB;
    v_src_clob          CLOB;

    v_dest_offset       INTEGER := 1;
    v_src_offset        INTEGER := 1;

    v_lang_context      INTEGER := DBMS_LOB.default_lang_ctx;
    v_warning           INTEGER;

    v_blob_offset       INTEGER := 1;
    v_temp              RAW(32767);
    v_amount            INTEGER;
BEGIN
    SELECT text
      INTO v_src_clob
      FROM your_table
     WHERE id = 1234;

    DBMS_LOB.createtemporary(lob_loc => v_dest_blob, cache => TRUE, dur => DBMS_LOB.session);

    DBMS_LOB.converttoblob(
        dest_lob       => v_dest_blob,
        src_clob       => v_src_clob,
        amount         => DBMS_LOB.lobmaxsize,
        dest_offset    => v_dest_offset,
        src_offset     => v_src_offset,
        blob_csid      => DBMS_LOB.default_csid,
        lang_context   => v_lang_context,
        warning        => v_warning
    );

    v_file := UTL_FILE.fopen(location => 'TMP_DIR', filename => 'sds_test_file.txt', open_mode => 'wb');

    LOOP
        BEGIN
            v_amount := c_rawlimit;
            DBMS_LOB.read(
                lob_loc   => v_dest_blob,
                amount    => v_amount,
                offset    => v_blob_offset,
                buffer    => v_temp
            );

            UTL_FILE.put_raw(v_file, v_temp);
            UTL_FILE.fflush(v_file);
            EXIT WHEN v_amount < c_rawlimit;
            v_blob_offset := v_blob_offset + c_rawlimit;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                EXIT;
        END;
    END LOOP;

    UTL_FILE.fclose(v_file);
    DBMS_LOB.freetemporary(v_dest_blob);
END;

Open in new window

0
 
sdstuberCommented:
try

DBMS_XSLPROCESSOR.clob2file(your_clob, some_directory_object, some_file_name);
0
 
sdstuberCommented:
alternately,  use dbms_lob.converttoblob  then loop through the blob, reading 32K at a time and use utl_file.put_raw until you exhaust the data.

The reason you don't want to loop through the clob directly is because text must be written in lines which means you'll get extra line breaks in your file. Possibly corrupting the data if they happen to occur in the middle of a node name for instance.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
nssudhaAuthor Commented:
Thanks sdstbuer! Is it possible to give me an sample code for the second one? I tried something this morning by using the cpos and fflush using while loop. It did not give me an error but I cannot verify it till tomorrow as the person who has access to the directory on server is not in today. If you could give me a sample for dbms_lob.converttoblob, I will try it today in my local.

Thanks in advance,
Raj
0
 
nssudhaAuthor Commented:
Thank you! I will try that.

Thanks,
Raj.
0
 
nssudhaAuthor Commented:
I modified your code to suit my requirement. I am now getting ORA-29280: invalid directory path after about 2-3 minutes of executing the procedure. I gave the same db directory name that I wrote other files to. I am not sure why this error is coming now.

Thanks,
Raj.
0
 
sdstuberCommented:
Can you post your code that fails and code that works for the same directory?
0
 
nssudhaAuthor Commented:
Code that failed is same as yours.

 v_file := UTL_FILE.fopen(location => 'TMP_DIR', filename => 'sds_test_file.txt', open_mode => 'wb');

code that worked is:

DECLARE
v_pos  pls_integer :=1;
v_clob clob;
v_xml utl_file.file_type;

BEGIN
     
    select xml_query into v_clob from table;

-----xml_query is my actual query to write the file.

V_XML:=UTL_FILE.FOPEN('TMP_DIR','XYZ.XML','w',16386);
while cpos < dbms_lob.getlength(v_clob) loop
UTL_FILE.PUT(v_XML,dbms_lob.substr(v_clob,16386,cpos));
cpos:=cpos+16386;
UTL_FILE.fflush(V_XML);
end loop;

UTL_FILE.FCLOSE(V_XML);

exception

...........

end;
0
 
sdstuberCommented:
when you used TMP_DIR successfully in the past, were you using a different user or was TMP_DIR pointing to a different path?
0
 
nssudhaAuthor Commented:
It is the same all the time. I am able to successfully use the same this week also. It was not the path but the length of the XML that was creating the issue.


Thanks,
Raj.
0
 
sdstuberConnect With a Mentor Commented:
>>> but the length of the XML that was creating the issue.  

that was when you were getting the ORA-06502


but now you're getting ORA-29280
I assume on the FOPEN since that's the only line you posted.

If that's not the case, please post exactly the code that failed including the line where it happened.
0
 
nssudhaAuthor Commented:
I overwrote the code now with the one working. My issue is resolved for now. I will post again if I get into a new issue.

Thanks,
Raj.
0
 
sdstuberCommented:
So, the only thing you needed to do was iterate through the lob in chunks?

glad I could help

you should probably check out the xslprocessor method though, it's all-in-one and, even though it's bundled with xml stuff, the clob it writes doesn't have to contain xml
0
 
nssudhaAuthor Commented:
I will check it out. Thanks for the help sdstuber!

Thanks,
Raj.
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.