0

I am trying to create a function that returns pipelined table with input parameters being varchar2, date or number. I am trying to input amount as varchar2 because I can't process it like a number with comma before decimals. Here is body of my package function:

 FUNCTION kreiraj_reprogram_rate (p_sifra_kupca             VARCHAR2,
                                     p_id_kartice_kupca        NUMBER,
                                     p_iznos_s_pdv             VARCHAR2,
                                     p_broj_rata               NUMBER,
                                     p_frekvencija_dospijeca   NUMBER,
                                     p_datum_dospijeca         VARCHAR2,
                                     p_datum_dokumenta         VARCHAR2)
        RETURN reprogram_rate_table
        PIPELINED
    IS
        v_data              reprogram_rate_rec;
        v_user              VARCHAR2 (200) := mc__secure_context.get_context_username ();
        v_iznos_rate        NUMBER;
        v_broj_rate         NUMBER;
        v_zadnja_rata       NUMBER;
        v_datum_dokumenta   DATE;
        v_datum_dospijeca   DATE;
    BEGIN
    write_log_table ('TEST','p_iznos_s_pdv: '|| to_number(p_iznos_s_pdv));
        v_datum_dospijeca := TO_DATE (p_datum_dospijeca, 'dd.mm.yyyy');
        v_datum_dokumenta := TO_DATE (p_datum_dokumenta, 'dd.mm.yyyy');

        v_iznos_rate := ROUND (to_number(p_iznos_s_pdv) / p_broj_rata, 2);
        v_zadnja_rata :=
            p_iznos_s_pdv - ROUND ((p_broj_rata - 1) * v_iznos_rate, 2);
        v_broj_rate := 1;

        FOR r IN 1 .. p_broj_rata
        LOOP
            IF v_broj_rate = p_broj_rata
            THEN
                v_data.osnovica := ROUND ((v_zadnja_rata / 1.25), 2);
                v_data.pdv := ROUND (v_zadnja_rata * 0.2, 2);
                v_data.ukupno := v_zadnja_rata;
            ELSE
                v_data.osnovica := ROUND ((v_iznos_rate / 1.25), 2);
                v_data.pdv := ROUND (v_iznos_rate * 0.2, 2);
                v_data.ukupno := ROUND (v_iznos_rate, 2);
            END IF;

            v_data.sifra_kupca := p_sifra_kupca;
            v_data.kartica_kupca := p_id_kartice_kupca;
            v_data.broj_rate := v_broj_rate;
            v_data.datum_dokumenta := v_datum_dokumenta;
            v_data.datum_dospijeca := v_datum_dospijeca;

            PIPE ROW (v_data);

            v_broj_rate := v_broj_rate + 1;

            v_datum_dospijeca :=
                CASE
                    WHEN p_frekvencija_dospijeca = 1
                    THEN
                        ADD_MONTHS (v_datum_dospijeca, 1)
                    WHEN p_frekvencija_dospijeca = 3
                    THEN
                        ADD_MONTHS (v_datum_dospijeca, 3)
                    WHEN p_frekvencija_dospijeca = 6
                    THEN
                        ADD_MONTHS (v_datum_dospijeca, 6)
                    WHEN p_frekvencija_dospijeca = 12
                    THEN
                        ADD_MONTHS (v_datum_dospijeca, 12)
                END;
        END LOOP;
    END kreiraj_reprogram_rate;

And the error says that error is on the first write_log_table line where I try to convert value 4159,57 to number.

I tried select to_number('4159,57') from dual in SQL developer and it works just fine so I can't figure out what is the catch.

Can someone help?

Thanks.

GodDamnLawyer
  • 67
  • 10
  • 4
    Is the procedure being run in the same session that your `select` statement is being executed? My first guess would be that you have different `nls_numeric_characters` settings in the two sessions https://stackoverflow.com/questions/24571355/nls-numeric-characters-setting-for-decimal That might also explain what " I can't process it like a number with comma before decimals" means if you're used to using commas as your decimal separator but your session expects to use periods. – Justin Cave Oct 08 '21 at 07:55
  • simply try to_number( replace('4159,57',',','.') ) inside function to see if nls differs from your computer with servers – Ali Fidanli Oct 08 '21 at 08:00
  • 3
    You shouldn't rely on NLS settings when doing conversions, so you could specify a format mask and settings in the `to_number()` calls; but it would be better to mage the formal argument a number not a string, and make it the client's responsibility to do any local conversion needed to be able to pass you an actual number. – Alex Poole Oct 08 '21 at 08:00

0 Answers0