CODEWITHSUNDEEP
sqloracle-databasefunctionplsqlto-char
Young Al Capone
Young Al Capone

Reputation: 399

Oracle - How can I use NLSParams to force change language using TO_CHAR function?

I'm coding a PL/SQL function to display in words a number inserted from parameter between 0 and 5000 but the result displayed is displayed in English (as you can check in the screenshot), but now I want to displayed the result in Spanish and I read about "NLSPARAM" provided in the TO_CHAR function but I don't know how Can I code it. Do you have any idea? Thanks.

CREATE OR REPLACE FUNCTION letras_a_numeros(p_numero NUMBER)
RETURN VARCHAR2
IS
    v_enteros   NUMBER(8);
    v_decimales NUMBER(8, 2);
    v_final_decimales NUMBER(8, 2);
    v_salida    VARCHAR2(1000);
    e_invalido  EXCEPTION;
BEGIN
    IF
        p_numero NOT BETWEEN 0 AND 5000 THEN
            RAISE e_invalido;
    END IF;

    v_enteros := TRUNC(p_numero);

    v_decimales := TRUNC(p_numero);
    v_final_decimales := SUBSTR(p_numero - v_decimales, 2);

    IF
        v_enteros = 0 THEN
            v_salida := 'Cero';
    ELSE
        SELECT TO_CHAR(DATE '-4712-01-01' + (v_enteros - 1), 'JSP')
        INTO   v_salida
        FROM   dual;
    END IF;

    IF
        v_final_decimales > 0 THEN
            RETURN v_salida || ' con ' || v_final_decimales || '/100';
    ELSE
            RETURN v_salida;
    END IF;
EXCEPTION
    WHEN e_invalido THEN
        RETURN 'Número inválido, ingrese valor entre 0 y 5000';
END letras_a_numeros;

enter image description here

Upvotes: 3

Views: 3905

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You are referring to specifying the date language, such as:

SELECT TO_CHAR(some_date, 'DD Mon YYYY', 'NLS_DATE_LANGUAGE=''SPANISH''') FROM ...

But that applies to things like day and month names, and won't do what you want here. From the documentation:

Notes on date format element suffixes:

  • When you add one of these suffixes to a datetime format element, the return value is always in English.
    ...

so you can't use this mechanism to spell out the value in words in a language other than English, unfortunately.

You can see that the language parameter makes no difference to SP with a version of your query:

SELECT TO_CHAR(DATE '-4712-01-01' + (124 - 1), 'JSP', 'NLS_DATE_LANGUAGE=''SPANISH''')  OUTPUT
FROM DUAL

OUTPUT
------------------------------------------------------
ONE HUNDRED TWENTY-FOUR

You'll need to convert your number manually; you can adapt something like this to use Spanish words.

Upvotes: 5

Related Questions