Re: to_char function

Frank and Elaine Verhelst (frank.verhelst_at_sasknet.sk.ca) wrote:
: Has anybody else run into the problem where the to_char function does NOT
: work when a parameter is specified in a function, i.e.,
 

: to_char(23,'0009')
 

: This should come back with '0023'. It only blows up when you put it in a
: function. Putting it in a normal select statement works fine using
: SQL*Plus but it doesn't work when you put it in a function.
 

: Any thoughts?

I made a small plsql script to test the to_char function:

  CREATE TABLE test (f1 CHAR(5), f2 NUMBER);

  DECLARE
    temp char(5);
  BEGIN
    SELECT TO_CHAR(23, '0009') INTO temp FROM dual;     INSERT INTO test VALUES(temp, LENGTH(temp));   END;
  .
  run

  SELECT * FROM test;

  EXIT and got this output

  F1 F2

• ---------- 0023 5

It appears that to_char prepends a blank to the converted number string.

Did you make your char variables/columns long enough to contain the result string ?

I'm using

  ORACLE RDBMS V6.0.36.1.1
  SQL*Plus: Version 3.0.12.1.1
  PL/SQL V1.0.34.5.1 on ULTRIX 4.4

--
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+      Christian Mondrup                                                      +
+      UNI-C Aarhus, Danish Computing Centre for Research and Education.      +
+      Olof Palmes Alle 38, DK 8200 Aarhus N.                                 +
+                                                                             +
+      Phone:   +45 86 78 44 44                                               +
+      Telefax: +45 86 78 44 55                                               +
+      E-Mail:  Christian.Mondrup_at_uni-c.dk                                    +
+                                                                             +
+      Opinions expressed are mine and do not reflect those of my employer.   +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++