Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2008
    Posts
    1

    Unanswered: How to format this field

    Hi,



    I have an oracle package body with a cursor. The resultset from this cursor is written to a text file which is then FTP ed to mainframe.



    The cursor has an amount field of length (5,2). I need to format this field to make it compatible with the mainframe specification. The corresponding mainframe variable has a length of 8. So if the cursor returns a value of 100, the value moved to mainframe should be 00100.00. This is where I am facing problem.



    If the data is 100.20, I can use LPAD function to format it.

    LPAD(AMOUNT, 7, 0) would return 00100.20

    But if the data is 100,

    LPAD(AMOUNT,7,0) would return 0000100



    Can anyone help me on this? Thanks.



    Regards,

    Aravind.

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    Hi
    Use the TO_CHAR function
    TO_CHAR(your_value,'00000000')

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd say that you have another problem. Column (or a variable or whatever) which has a size of (5, 2) will perhaps NOT be able to keep all the values you'd want it to. Here's an example:
    Code:
    SQL> create table test (amount number(5, 2));
    
    Table created.
    
    SQL> insert into test values (10.4);
    
    1 row created.
    
    SQL> insert into test values (923.528);
    
    1 row created.
    
    SQL> insert into test values (1234);
    insert into test values (1234)
                             *
    ERROR at line 1:
    ORA-01438: value larger than specified precision allowed for this column
    See? NUMBER(5, 2) is to be read as "5 positions, and 2 of them are decimal" (NOT "5 positions PLUS 2 decimal = 7").

    OK, here's what we have:
    Code:
    SQL> select * From test;
    
        AMOUNT
    ----------
          10.4
        923.53
    
    SQL> select amount, to_char(amount, '00000.00') formatted_amount from test;
    
        AMOUNT FORMATTED
    ---------- ---------
          10.4  00010.40
        923.53  00923.53
    
    SQL>

Posting Permissions

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