4

I have below code in my PL/SQL procedure, which I called in API_XXX.put(it calls utl_file.put) in a while loop. And the l_xmldoc is CLOB from a function of getReportXML, which returns the xml clob.

the code I write to write xml into a file is like:

l_offset := 1;
    WHILE (l_offset <= l_length)
    LOOP
    l_char := dbms_lob.substr(l_xmldoc,1,l_offset);

    IF (l_char = to_char(10))  ---I also tried if (l_char=chr(10)) but it did not work 
    THEN
        API_XXXX.new_line(API_XXX.output, 1);
    ELSE
        API_XXXX.put(fnd_API_XXX.output, l_char);
    END IF;

    l_offset := l_offset + 1;
    END LOOP;

Please note that the API_XXX is the existing package which I am not able to modify, and this api calls fflush in the end of put.

API_XXX.put's part is like below("WHICH" is the first param):

         elsif WHICH = API_XXX.OUTPUT then
           temp_file := OUT_FNAME;
           utl_file.put(F_OUT, BUFF);
           utl_file.fflush(F_OUT);

API_XXX.new_line is like(LINES is the number of lines to write):

         elsif WHICH = API_XXX.OUTPUT then
           temp_file := OUT_FNAME;
           utl_file.new_line(F_OUT, LINES);
           utl_file.fflush(F_OUT);

I notice a that the put/new_line procedure in my customer's side will sometimes raise UTL_FILE.WRITE_ERROR for unknown reason(maybe due to the l_length is too large(up to 167465)) in the while loop from my customer.

I read Oracle PL/SQL UTL_FILE.PUT buffering . And I found that this is the same cause, my l_xmldoc is really large and when I loop it, I found that it is without a new line terminator so the buffer is up to 32767 even though I fflush every time.

So, how should I convert the l_xmldoc into a varchar with new line terminator.

PS: I confirmed that my customer is using Oralce 11g

Improve this question
8
  • 1
    I suggest to check a file content and encoding or better use UTL_FILE.PUT_RAW. In past I had problems, where I tried to import excel files and import crashed for no reason, then I figured that the problem is when symbols are unknown. Bad encoding also can corrupt file too. Commented Aug 13, 2014 at 7:18
  • 1
    Hi @DARK_A , was you problem is UTL_FILE.WRITE_ERROR? Commented Aug 13, 2014 at 8:21
  • 1
    I'm also using utl_file.put_raw to write my files. In general I first convert a clob to a blob and only then write it to a file. Commented Aug 13, 2014 at 9:39
  • 1
    @user272735 , did you ever encounter my problem? Do you know why? are you sure that your solution works, and please also share your code in the answer. Commented Aug 13, 2014 at 9:42
  • 2
    I haven't tested this, but I suspect that when calling UTL_FILE.PUT or PUT_LINE the input length can't exceed the max allowed for a VARCHAR2 in PL/SQL (32767). Commented Aug 13, 2014 at 14:18

2 Answers 2

Reset to default
2

  1. Post the Oracle Version you are using! Or we can just guess around...

  2. Your fflush will not work as you expect - From the documentation:

    FFLUSH physically writes pending data to the file identified by the file handle. Normally, data being written to a file is buffered. The FFLUSH procedure forces the buffered data to be written to the file. The data must be terminated with a newline character.

  3. tbone is abolutely right the line TO_CHAR(10) is wrong! Just try SELECT TO_CHAR(10) FROM DUAL; you will get 10 which you then compare to a single character. A single character will never be '10' since 10 has two characters!

  4. Your problem is most likely a buffer-overflow with too large XML-Files, but keep in mind, also other problems on the target system can lead to write_errors, which should be handled.

Solutions

  • Quick&Dirty: Since you don't seem to care about performance anyways you can just close the file every X byte and reopen it with A for append. So just add to the loop:

    IF MOD( l_offset, 32000 ) = 0
THEN
  UTL_FILE.FCLOSE( f_out );
  UTL_FILE.FOPEN( out_fpath, out_fname, f_out, 'a', 32767 );
END IF;

  • Use the right tool for the right job: UTL_FILE is not suited for handling complex data. The only usecase for UTL_FILE are small newline-separated lines of text. For everything else you should write RAW bytes! (Which will also allow you porper control over ENCODING, which is currently just mini-vanilly-lucky-guess)

  • Write a Java-Stored-Procedure with NIO-Filechannels - fast, safe, nice... But be careful, your program might run 10 times as fast!

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

So, if FFLUSH works fine if I only put a short varchar without a new line terminator? And your solution is not works for me although I agree that. 1. I have no idea which file is opened since my api created a random name for this file and open it for me, So I cannot reopen the file. 2. I can't put_raw since the API did not give me that(again, I have to use the api to open the file). 3. Java procedure is not the choice, I have to write in the plsql.
"Normally, data being written to a file is buffered. The FFLUSH procedure forces the buffered data to be written to the file. The data must be terminated with a newline character." Also, this statements from Oracle Doc is really confusing to me. It says when I fflush, it forces the data to be written to the file. But I ffulsh every single char, and this char does not have new line terminator. Why no problem exists for past tens of years?
@Jaskey Please understand the Statement: FFLUSH DOES NOTHING without a NEWLINE TERMINATOR. - IF and only IF there is a newline terminator, then fflush will flush everything in your buffer up to and including this newline terminator! - With Short varchar you don't get a problem, because FCLOSE will flush everything eventually! But with long text your buffer will run full!
@Jaskey If you just want to fix your problem and cannot change anything in other API, you have to change your code like so: IF (l_char = char(10) OR MOD( l_offset, 32000 ) = 0 ) Then you will just insert a newline every 32K chars and it will work, but you will insert newlines into the file...
will this influnce the xml interpretation？
|
1

Just a guess, but instead of "to_char(10)" you might try chr(10) to determine/write a newline. Not sure if this will solve your problem, but sometimes very long lines (without newlines) can cause issues.

For example:

declare
    l_clob clob;
    l_char char;
begin
    l_clob := 'Line 1' || chr(10) || 'Line 2' || chr(10);

    for i in 1 .. DBMS_LOB.GETLENGTH(l_clob)
    loop
        l_char := dbms_lob.substr(l_clob, 1, i);

        if (l_char = chr(10)) then
        --if (l_char = to_char(10)) then
            dbms_output.put_line('Found a newline at position ' || i);
        end if;
    end loop;

end;

Notice the difference between chr(10) and to_char(10). Easy enough to test if this solves your problem anyway.

Improve this answer

8 Comments

Would you please share a code sample？ also, my api calls fflush every time after put a single char . So we do not have write a line for now
I saw somebody upvoted this answer, do you mean that I replace to_char(10) into chr(10) will help? What is the difference? Would you please help to explain a little bit?
TO_CHAR(10) converts 10 (number) to 10 (character). CHR(10) converts decimal in to character, in your case new line symbol.
chr(10) is a newline. Its not the same as to_char(10), which converts the number 10 to the string '10'. So your code will never find a newline, and will never write a newline (your API code that calls utl_file.newline will never be called). And the fflush needs the newline terminator I believe. Its simple enough to fix your code and test anyway.
@tbone thank you！since i cannot easily test the issue , i will wait for the customer's feedback. I am amazed for your answer since this worked for years………but i will still try that.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.