Solved

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

Posted on 2014-01-20
16
2,033 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
16 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39794151
try

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

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

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 74

Expert Comment

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

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 74

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 74

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 74

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

724 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