#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0

    Can someone explain the below code for me..please..


    v_sep VARCHAR2(1) := '|';
    v_buffer VARCHAR2(32767);
    v_line_cnt NUMBER;
    v_bufr_cnt NUMBER;
    c_eol CONSTANT VARCHAR2(1) := CHR(10);

    -- this line below, does it mean anything? isn't LENGTH(c_eol) always 1?

    c_eollen CONSTANT PLS_INTEGER := LENGTH(c_eol);
    c_maxline CONSTANT PLS_INTEGER := 32767;

    Begin
    utl_file_handle := UTL_FILE.FOPEN(utl_dir_path, utl_file_name, 'W', 32767);
    v_line_cnt := 0;
    v_bufr_cnt := 0;
    v_buffer := NULL;
    FOR r IN (query)
    LOOP
    IF LENGTH(v_buffer) + c_eollen + LENGTH(r.rec) <= c_maxline THEN
    v_buffer := v_buffer || c_eol || r.rec;
    ELSE
    IF v_buffer IS NOT NULL THEN
    UTL_FILE.PUT_LINE(utl_file_handle, v_buffer);
    v_bufr_cnt := v_bufr_cnt + 1;
    END IF;
    v_buffer := r.rec;
    END IF;
    v_line_cnt := v_line_cnt + 1;
    END LOOP;
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    834
    Rep Power
    387

    Wink


    Originally Posted by nutjob
    -- this line below, does it mean anything? isn't LENGTH(c_eol) always 1?

    c_eollen CONSTANT PLS_INTEGER := LENGTH(c_eol);

    - - - E t c - - -
    Yes.
    Apparently the author of this code intended to create files with the lines terminated with either the "CR/LF (chr 13)+(chr 10)" or just the "LF (chr 10)" depending on the target server (windoze/unix), therefore the length of the "end line" characters would be needed.
    Seems he/she never got to it.

    PS: Also seems there is a bug in the code because DBMS_OUTPUT.PUT_LINE already adds a LF; therefore every approx. 32767 characters there will be a line terminated with two "LF"s.
    Last edited by LKBrwn_DBA; November 5th, 2013 at 01:36 PM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    Yes.
    Apparently the author of this code intended to create files with the lines terminated with either the "CR/LF (chr 13)+(chr 10)" or just the "LF (chr 10)" depending on the target server (windoze/unix), therefore the length of the "end line" characters would be needed.
    Seems he/she never got to it.

    PS: Also seems there is a bug in the code because DBMS_OUTPUT.PUT_LINE already adds a LF; therefore every approx. 32767 characters there will be a line terminated with two "LF"s.
    Thank you very much for the quick reply. Much appreciated. I thought I was missing something. Also the below part is slightly confusing to me. Looks like the rec is written into the buffer in both if and else part.

    IF LENGTH(v_buffer) + c_eollen + LENGTH(r.rec) <= c_maxline THEN
    v_buffer := v_buffer || c_eol || r.rec;
    ELSE
    IF v_buffer IS NOT NULL THEN
    UTL_FILE.PUT_LINE(utl_file_handle, v_buffer);
    v_bufr_cnt := v_bufr_cnt + 1;
    END IF;
    v_buffer := r.rec;
    END IF;

    To the other point you mentioned about the bug, I tested and its not adding duplicate LF. Is it because the code is using utl_file.put_line and not dbms_output.put_line?

    Thanks again!
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    834
    Rep Power
    387

    Cool


    Originally Posted by nutjob
    . . .. Looks like the rec is written into the buffer in both if and else part.
    . . .
    If you would have formated the code, you would have noticed that is not the case:
    Code:
      IF LENGTH ( v_buffer) + c_eollen + LENGTH ( r.rec) <= c_maxline
      THEN
        v_buffer   := v_buffer || c_eol || r.rec;
      ELSE
        IF v_buffer IS NOT NULL
        THEN
          UTL_FILE.put_line ( utl_file_handle, v_buffer);
          v_bufr_cnt   := v_bufr_cnt + 1;
        END IF;
    
        v_buffer   := r.rec;
      END IF;
    . . .
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0
    I get it now! Solutions can be as simple as formatting the code

    Thank you so much!



    Originally Posted by LKBrwn_DBA
    If you would have formated the code, you would have noticed that is not the case:
    Code:
      IF LENGTH ( v_buffer) + c_eollen + LENGTH ( r.rec) <= c_maxline
      THEN
        v_buffer   := v_buffer || c_eol || r.rec;
      ELSE
        IF v_buffer IS NOT NULL
        THEN
          UTL_FILE.put_line ( utl_file_handle, v_buffer);
          v_bufr_cnt   := v_bufr_cnt + 1;
        END IF;
    
        v_buffer   := r.rec;
      END IF;
    . . .

IMN logo majestic logo threadwatch logo seochat tools logo