Oracle – a simple function to format numbers for display

We have requirements to produce Payment Requests through Oracle E-Business suite custom interface, where the users are allowed to select different currencies based on the payment.

Different currencies means different precisions, US $ 2, Indian Rupees 2, while Kuwait, Bahrain have 3 precisions to maintain…

So the amounts displayed on reports needed to be formatted properly and we were doing some hardcoded formatting like

Now we have more currencies to deal with, hence came up with following simple function, which accepts the amount, precisions as inputs and returns a VARCHAR2 string as formatted number!

Function

CREATE OR REPLACE FUNCTION xx_format_number (P_AMOUNT IN NUMBER,
P_DEC_PREC IN NUMBER)
RETURN VARCHAR2
IS
frm_1 VARCHAR2 (30) := 'fm999999999990.';
new_num VARCHAR2 (30);
BEGIN
SELECT TO_CHAR (P_AMOUNT,
RPAD (frm_1, 15 + P_DEC_PREC, '90000000000000000'))
INTO new_num
FROM DUAL;

RETURN (new_num);

END;

You can check the function like following

SET SERVEROUTPUT ON;

DECLARE
l_new_num VARCHAR2 (30);
BEGIN

l_new_num := xx_format_number(.009,3);

DBMS_OUTPUT.PUT_LINE (l_new_num);
END;

Check it out, and let us know whether it worked for you!

for Windows7bugs

admin

2 Responses to Oracle – a simple function to format numbers for display

  1. Iudith Mentzel says:

    Hello Rajesh,

    Just a small remark:

    You don’t need a SELECT … FROM DUAL inside the function, you can simply use PL/SQL only:

    CREATE OR REPLACE FUNCTION xx_format_number (P_AMOUNT IN NUMBER,
    P_DEC_PREC IN NUMBER)
    RETURN VARCHAR2
    IS
    frm_1 VARCHAR2 (30) := ‘fm999999999990.’;
    new_num VARCHAR2 (30);
    BEGIN
    new_num := TO_CHAR (P_AMOUNT,
    RPAD (frm_1, 15 + P_DEC_PREC, ‘90000000000000000’));
    RETURN (new_num);

    END xx_format_number;
    /

    Function created.

    — testing
    DECLARE
    l_new_num VARCHAR2 (30);
    BEGIN
    l_new_num := xx_format_number(.09,3);

    DBMS_OUTPUT.PUT_LINE (l_new_num);
    END;
    /

    Thanks a lot & Best Regards,
    Iudith

    • windows7bugs says:

      Hello Iudith
      Indeed, it was a quick thing, just copied the Select part from a query and pasted into the PL/SQL body. Btw, there are many times I get confused with the silliest stuff :D and a reminder is good, welcomed!

      thanks once again

      rajesh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: