TO_CHAR works differently in SQL and PLSQL
Hi,I discover that calling TO_CHAR function from SQL and PLSQL gets different results. Try this - create plsql function:
CREATE FUNCTION FormatNumber(aValue NUMBER) RETURN VARCHAR2 IS BEGIN BEGIN RETURN TO_CHAR(aValue, '999G999G999G990', 'NLS_NUMERIC_CHARACTERS = '', '''); EXCEPTION WHEN OTHERS THEN RETURN NULL; END; END;
and then call this SELECT:
select FormatNumber(7) A, TO_CHAR(7, '999G999G999G990', 'NLS_NUMERIC_CHARACTERS = '', ''') B from dual;
CREATE FUNCTION FormatNumber(aValue NUMBER) RETURN VARCHAR2 IS BEGIN BEGIN RETURN TO_CHAR(aValue, '999G999G999G990', 'NLS_NUMERIC_CHARACTERS = '', '''); EXCEPTION WHEN OTHERS THEN RETURN NULL; END; END;
and then call this SELECT:
select FormatNumber(7) A, TO_CHAR(7, '999G999G999G990', 'NLS_NUMERIC_CHARACTERS = '', ''') B from dual;
The result for column A is "7" and for column B is " 7" (leading spaces in column .
Does anybody know if this is a bug or documented behaviour?
Does anybody know if this is a bug or documented behaviour?
1