This discussion is archived
3 Replies Latest reply: Jun 29, 2012 4:51 AM by 917625 RSS

Division operation in PL/SQL

917625 Newbie
Currently Being Moderated
Hi,
I have a small query regarding the division operation in PL/SQL

select 1/2 from dual;*

Result: *0.5*

declare*
l_value1  number:=1;*
l_value2  number:=0;*
begin*
select l_value1/2  into*
l_value2 from dual;*
dbms_output.put_line(l_value2);*
end;*

Result: *.5*

But I want to display the result as 0.5 only. I can write the logic to append zero before the result. But I wanted to know the reason behind it and is there any other standard function to display the result as required. Can some one help me in this.
Thanks in Advance.
  • 1. Re: Division operation in PL/SQL
    946431 Newbie
    Currently Being Moderated
    Hi try this

    declare
    l_value1 number:=5;
    l_value2 number:=0;
    begin
    select l_value1/2 into
    l_value2 from dual;
    dbms_output.put_line(to_char(l_value2,'fm0.9'));
    end;
  • 2. Re: Division operation in PL/SQL
    rp0428 Guru
    Currently Being Moderated
    >
    I wanted to know the reason behind it and is there any other standard function to display the result as required.
    >
    Oracle stores data for all datatypes in an internal format. Their is no concept of what data 'looks like'. Formatting functions are provided, and need to be used, to control formatting.

    To 'see' a number it needs to be converted to a string. The TO_CHAR function is provided for that purpose and a format model can be provided to instructs the function how to format the data.

    Different tools may use different format models when displaying the data.

    So if you execute 'select 1/2 from dual;' in sql*plus it will display .5 but using Toad will display 0.5

    See TO_CHAR in the SQL Reference
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions189.htm

    And Format Models
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#i34510

    Table 2-13 describes each of the Number Format Elements that can be used in the format model.
  • 3. Re: Division operation in PL/SQL
    917625 Newbie
    Currently Being Moderated
    Thanks rp0428 and *943428* for your answers.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points