Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Unanswered: Space issue in Extract File

    Hi,

    PFA code snippet :-
    Cursor cur_1 Is
    Select lb.*
    From tab1 lb;


    begin
    ................
    For i in cur_1 LOOP

    utl_file.putf(fp,'%s',LTRIM (To_Char(NVL(i.ID,0),'09999999999')));
    utl_file.putf(fp,'%s',To_Char(NVL(i.rate,' '), 'S0999999V9999'));
    utl_file.putf(fp,'%s',To_Char(NVL(i.amount,' '), 'S0999999V9999'));

    end loop;

    end;

    In the output file , if the column value for rate is NULL then it is not adding spaces. As a result the output is not properly alligned.

    Sample output

    000464247078
    5908540 <--- this is not uniform because NVL is replacing null with space.


    cheers
    subhotech
    000000066400

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is the input, what do you expect as output?

    Code you posted ends with an error if the column datatype is NUMBER:
    Code:
    SQL> select ename, To_Char(NVL(comm,' '), 'S0999999V9999') from emp;
    select ename, To_Char(NVL(comm,' '), 'S0999999V9999') from emp
                                   *
    ERROR at line 1:
    ORA-01722: invalid number
    
    
    SQL>
    Perhaps you should provide test case (CREATE TABLE and INSERT INTO sample records). Use [code] tags to keep the formatting; otherwise, it will / might be unclear what is the desired result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •