To know SESSION decimal separator, you can use following SQL command:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';
select SUBSTR(value,1,1) as "SEPARATOR"
,'using NLS-PARAMETER' as "Explanation"
from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS'
UNION ALL
select SUBSTR(0.5,1,1) as "SEPARATOR"
,'using NUMBER IMPLICIT CASTING' as "Explanation"
from DUAL;
The first SELECT command find NLS Parameter defined in NLS_SESSION_PARAMETERS table. The decimal separator is the first character of the returned value.
The second SELECT command convert IMPLICITELY the 0.5 rational number into a String using (by default) NLS_NUMERIC_CHARACTERS defined at session level.
The both command return same value.
I have already tested the same SQL command in PL/SQL script and this is always the same value COMMA or POINT that is displayed. Decimal Separator displayed in PL/SQL script is equal to what is displayed in SQL.
To test what I say, I have used following SQL commands:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';
select 'DECIMAL-SEPARATOR on CLIENT: (' || TO_CHAR(.5,) || ')' from dual;
DECLARE
S VARCHAR2(10) := '?';
BEGIN
select .5 INTO S from dual;
DBMS_OUTPUT.PUT_LINE('DECIMAL-SEPARATOR in PL/SQL: (' || S || ')');
END;
/
The shorter command to know decimal separator is:
SELECT .5 FROM DUAL;
That return 0,5 if decimal separator is a COMMA and 0.5 if decimal separator is a POINT.