3 Replies Latest reply: Jun 29, 2012 6:51 AM by 917625 RSS

    Division operation in PL/SQL

    917625
      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
          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
            >
            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
              Thanks rp0428 and *943428* for your answers.