Home » SQL & PL/SQL » SQL & PL/SQL » Anonymous PLSQL - need output with 0. (Oracle 10g)
Anonymous PLSQL - need output with 0. [message #582342] Wed, 17 April 2013 05:46 Go to next message
srinivas.k2005
Messages: 283
Registered: August 2006
Senior Member
Hi,

I have a code which populates the data to a flat file, in DB i have value as 0.10 but once it moves to text file through UTIL_FILE its shows as .10. i need to show as 0.10 in notepad also.

I have taken the code which changes my the value from 0.10 to .10, let me know how can i get the value as 0.10

declare
CURSOR c_test IS
SELECT
 0.10
FROM
  dual;
  v_number number;
  begin
OPEN c_test ;
 FETCH
    c_test
  INTO 
	v_number;
 close c_test;
    dbms_output.put_line ('hi' ||v_number);
  end;


Output:
hi.10
Re: Anonymous PLSQL - need output with 0. [message #582343 is a reply to message #582342] Wed, 17 April 2013 05:49 Go to previous messageGo to next message
Littlefoot
Messages: 18846
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try with TO_CHAR and appropriate format mask, such as
SQL> select 0.10 val, to_char(0.10, '990D00') val1 from dual;

       VAL VAL1
---------- -------
        ,1    0,10

SQL>
Re: Anonymous PLSQL - need output with 0. [message #582353 is a reply to message #582343] Wed, 17 April 2013 07:07 Go to previous message
Solomon Yakobson
Messages: 1802
Registered: January 2010
Senior Member
That would leave leading blanks. OP needs to add fm modifier:

SQL> declare
  2  CURSOR c_test IS
  3  SELECT
  4   0.10
  5  FROM
  6    dual;
  7    v_number number;
  8    begin
  9  OPEN c_test ;
 10   FETCH
 11      c_test
 12    INTO
 13   v_number;
 14   close c_test;
 15      dbms_output.put_line ('hi' || to_char(0.10,'990D00'));
 16    end;
 17  /
hi   0.10

PL/SQL procedure successfully completed.

SQL> declare
  2  CURSOR c_test IS
  3  SELECT
  4   0.10
  5  FROM
  6    dual;
  7    v_number number;
  8    begin
  9  OPEN c_test ;
 10   FETCH
 11      c_test
 12    INTO
 13   v_number;
 14   close c_test;
 15      dbms_output.put_line ('hi' || to_char(0.10,'fm990D00'));
 16    end;
 17  /
hi0.10

PL/SQL procedure successfully completed.

SQL> 


SY.
Previous Topic: Functions within a Package
Next Topic: Help with a select statement
Goto Forum:
  


Current Time: Thu Apr 24 10:01:35 CDT 2014

Total time taken to generate the page: 0.11010 seconds