Solved

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

Posted on 2014-01-20
16
1,766 Views
Last Modified: 2014-06-11
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.
0
Comment
Question by:nssudha
  • 7
  • 7
16 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39794151
try

DBMS_XSLPROCESSOR.clob2file(your_clob, some_directory_object, some_file_name);
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39794180
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
 

Author Comment

by:nssudha
ID: 39794298
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39794461
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
 

Author Comment

by:nssudha
ID: 39794489
Thank you! I will try that.

Thanks,
Raj.
0
 

Author Comment

by:nssudha
ID: 39795368
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39795428
Can you post your code that fails and code that works for the same directory?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:nssudha
ID: 39804139
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39804388
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
 

Author Comment

by:nssudha
ID: 39804469
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 39804538
>>> 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
 

Author Comment

by:nssudha
ID: 39804580
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39804650
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
 

Author Comment

by:nssudha
ID: 39804787
I will check it out. Thanks for the help sdstuber!

Thanks,
Raj.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40126649
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now