Forum Stats

  • 3,824,936 Users
  • 2,260,441 Discussions
  • 7,896,354 Comments

Discussions

Parameters should accept keyword DEFAULT to indicate we want same behaviour as if parameter was not

Kim Berg Hansen
Kim Berg Hansen Senior System DeveloperMember Posts: 1,000 Bronze Trophy
edited Jun 24, 2020 6:34AM in Database Ideas - Ideas

Background

In an INSERT statement we are allowed to use keyword DEFAULT for a column to get the same behaviour as if we did not specify the column.

For example:

create table tab1 (

   col1 number not null

, col2 number default 10

);

insert into tab1 values (10, DEFAULT);

insert into tab1 (col1) values (10);

select * from tab1;

      COL1       COL2

---------- ----------

        10         10

        10         10

The two inserts behaved identically.

In PL/SQL the mechanism is a bit different.

create or replace procedure proc1 (

   p1 varchar2

, p2 varchar2 DEFAULT 'P2 DEFAULT'

, p3 varchar2 DEFAULT 'P3 DEFAULT'

) is

begin

   dbms_output.put_line(p1);

   dbms_output.put_line(p2);

   dbms_output.put_line(p3);

end;

/

call proc1('P1 ACTUAL');

P1 ACTUAL

P2 DEFAULT

P3 DEFAULT

call proc1('P1 ACTUAL', p3=>'P3 ACTUAL');

P1 ACTUAL

P2 DEFAULT

P3 ACTUAL

We need to used named parameter notation to make P2 use the default.

We cannot do:

call proc1('P1 ACTUAL', DEFAULT, 'P3 ACTUAL');

With a PL/SQL procedure/function it is not really a big issue, since you can do named notation.

But built-in (not PL/SQL) functions cannot do this.

For example TO_NUMBER has 3 parameters:

  1. The expression to be converted to a number (mandatory)
  2. The format model (optional)
  3. NLS parameters (optional

There is no way to call TO_NUMBER where expression and NLS parameters are specified, but not format model.

Usecase

Using TO_NUMBER(v_string) is easy way to convert any simple numeric value in the string (simple meaning it only has digits and a possible decimal separator).

When format model is omitted, the default is to handle it no matter how many digits there are.

If the application is to work internationally, the app should work no matter what NLS settings a session has.

So if the string expression comes from a fixed data source with a known fixed decimal separator, the TO_NUMBER call should work no matter what the session NLS_NUMERIC_CHARACTERS are.

Here are some examples:

-- This works, but is clumsy

select value into save_val from nls_session_parameters

where parameter = 'NLS_NUMERIC_CHARACTERS';

execute immediate q'[alter session set nls_numeric_characters = '.,']';

select to_number('123.456')

from dual;

execute immediate q'[alter session set nls_numeric_characters = ']' || save_val || q'[']';

-- This is invalid, gets ORA-00907

select to_number('123.456', nlsparam=>q'[nls_numeric_characters='.,']')

from dual;

-- NULL as format model does not mean "use default", this returns NULL

select to_number('123.456', NULL, q'[nls_numeric_characters='.,']')

from dual;

-- DEFAULT is not valid, gets ORA-00936

select to_number('123.456', DEFAULT, q'[nls_numeric_characters='.,']')

from dual;

-- This works, but too few digits if I don't know how many digits in input

select to_number('123.456', '999D999', q'[nls_numeric_characters='.,']')

from dual;

-- TM9 as input format model isn't working, gets ORA-01722

select to_number('123.456', 'TM9', q'[nls_numeric_characters='.,']')

from dual;

-- This is awkward and how many nines should I add to be safe?

select to_number('123.456', '99999999999999999999D99999999999999999999', q'[nls_numeric_characters='.,']')

from dual;

We cannot use named notation to specify only NLS parameters.

We cannot use DEFAULT, this is unsupported.

We cannot use NULL to indicate using the default behaviour, as NULL format model makes TO_NUMBER return NULL.

We cannot use format model TM9 which would have been almost identical to default behaviour (in TO_CHAR using TM9 is almost identical to not using a format model.)

The possible options right now are:

  • Save session NLS_NUMERIC_CHARACTERS, set session to desired values, call TO_NUMBER, and then set session back to the saved NLS_NUMERIC_CHARACTERS.
  • Use format model 999999999999999999999999999999D999999999999999999999999999 and hope the number of digits suffice.

It would be so much easier and safer if we could do:

to_number('123.456', DEFAULT, q'[nls_numeric_characters='.,']')

Proposal

Make it possible to specify DEFAULT for optional parameters.

Ideally make it possible both for built-in functions and also for PL/SQL procedures/functions.

(Alternatively just for built-ins, as there is an alternative way in PL/SQL at the moment.)

Kim Berg HansenPhilipp SalvisbergErik van RoonApexBineWilliam RobertsonRobert MarzSven W.L. FernigrinijbbarretoberxJeff Kemp-Oracleuser12980594
12 votes

Active · Last Updated

Comments

  • Stew Ashton
    Stew Ashton Member Posts: 2,898 Bronze Crown

    When discussing ideas here, I like to play the devil's advocate and find the least bad workaround. This is what I use:

    with data(x) as (  select '1' from dual union all  select '.1' from dual union all  select ',1' from dual union all  select '1.1' from dual union all  select '1,1' from dual)select x, to_number(translate(x, ',.', substr(1/10,1,1)||substr(1/10,1,1) )) nfrom data;X            N--- ----------1            1.1          ,1        ,1          ,1        1.1        1,11,1        1,1

    Regards,

    Stew

    user12980594
  • Robert Marz
    Robert Marz Member Posts: 3 Blue Ribbon

    When discussing ideas here, I like to play the devil's advocate and find the least bad workaround. This is what I use:

    with data(x) as (  select '1' from dual union all  select '.1' from dual union all  select ',1' from dual union all  select '1.1' from dual union all  select '1,1' from dual)select x, to_number(translate(x, ',.', substr(1/10,1,1)||substr(1/10,1,1) )) nfrom data;X            N--- ----------1            1.1          ,1        ,1          ,1        1.1        1,11,1        1,1

    Regards,

    Stew

    Stew,

    this stops working, if you have an additional thousands separator in your number strings, like '10.123,123'.

    Regards,

    Robert

  • Stew Ashton
    Stew Ashton Member Posts: 2,898 Bronze Crown

    Stew,

    this stops working, if you have an additional thousands separator in your number strings, like '10.123,123'.

    Regards,

    Robert

    Yes, it was never intended to deal with thousands separator, but you are right to point that out.

    Regards,

    Stew

  • Stew Ashton
    Stew Ashton Member Posts: 2,898 Bronze Crown
    edited Jun 28, 2020 5:03AM

    Stew,

    this stops working, if you have an additional thousands separator in your number strings, like '10.123,123'.

    Regards,

    Robert

    Robert Marz wrote:...this stops working, if you have an additional thousands separator in your number strings, like '10.123,123'.

    Come to think of it, TO_NUMBER stops working too. The input string to TO_NUMBER cannot have group separators in it, unless a specific number format is provided.

    Here is an alternative that will strip out group separators and convert the decimal character :

    select to_number(  translate(num_string, nls_chars, substr(to_char(0, 'fm0D'), 2))) from <input>;

    Or very slightly more concise :

    select to_number(  translate(num_string, '0'||nls_chars, to_char(0, 'fm0D'))) from <input>;
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited Jun 30, 2020 4:49AM

    Totally agree with the proposal.

    But I have a slight problem with the sample case:

    So if the string expression comes from a fixed data source with a known fixed decimal separator, the TO_NUMBER call should work no matter what the session NLS_NUMERIC_CHARACTERS are.

    If the data source is fixed and the decimal separator is fixed,we should use a fixed format mask.
    The problem here is that a way to specify the intended format mask is missing.
    In my eye the most logical solution would be to use this:

    to_number('123.456', '99999999999999999999.99999999999999999999')

    It seems to work as intented, as can be proven easily with the following example.'

    Note my nls session have been set to GERMAN, so the sessions decimal separator is a ","

    select to_char(to_number('1,234,567.8', '99999999999999999999.99999999999999999999'),'99999999999999999990D99999999999999999999') from dual

        

    1234567,8

    Although the suggestion with the default keyword provides a workaround, this still requires to set a nls setting via the third parameter, even if we do not want an nls dependency in this case.
    Maybe a more flexible format mask would be the better choice for this specific example.
    My personal preference would be that 
    - a 9 at the end after the decimal separator allows as many 9s as possible (unless the FX modifier is used).
    - a 9 before the decimal separator and at the start (excluding things like FX/FM modifiers or signs) extends to as many possible 9s as needed.

    This would then allow the following solution (which currently gets ORA-01722)

    to_number('1,234,567.8', '9.9')

    expected result (german nls settings):

    1.234.567,8

    Unfortunately this would also break existing code. So it might need some tweaking, like another format modifier to explicitly specify this behaviour.

    Kind of the opposite of FX. Lets call it FLex format.

    to_number('1,234,567.8', 'FL9.9')

    1.234.567,8


    This doesn't change my opinion that the DEFAULT keyword is a very good proposal. I just feel it is not the best solution for the problem case.

    -- Edit: Typo corrections and some word changes to clarify meaning

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    Totally agree with the proposal.

    But I have a slight problem with the sample case:

    So if the string expression comes from a fixed data source with a known fixed decimal separator, the TO_NUMBER call should work no matter what the session NLS_NUMERIC_CHARACTERS are.

    If the data source is fixed and the decimal separator is fixed,we should use a fixed format mask.
    The problem here is that a way to specify the intended format mask is missing.
    In my eye the most logical solution would be to use this:

    to_number('123.456', '99999999999999999999.99999999999999999999')

    It seems to work as intented, as can be proven easily with the following example.'

    Note my nls session have been set to GERMAN, so the sessions decimal separator is a ","

    select to_char(to_number('1,234,567.8', '99999999999999999999.99999999999999999999'),'99999999999999999990D99999999999999999999') from dual

        

    1234567,8

    Although the suggestion with the default keyword provides a workaround, this still requires to set a nls setting via the third parameter, even if we do not want an nls dependency in this case.
    Maybe a more flexible format mask would be the better choice for this specific example.
    My personal preference would be that 
    - a 9 at the end after the decimal separator allows as many 9s as possible (unless the FX modifier is used).
    - a 9 before the decimal separator and at the start (excluding things like FX/FM modifiers or signs) extends to as many possible 9s as needed.

    This would then allow the following solution (which currently gets ORA-01722)

    to_number('1,234,567.8', '9.9')

    expected result (german nls settings):

    1.234.567,8

    Unfortunately this would also break existing code. So it might need some tweaking, like another format modifier to explicitly specify this behaviour.

    Kind of the opposite of FX. Lets call it FLex format.

    to_number('1,234,567.8', 'FL9.9')

    1.234.567,8


    This doesn't change my opinion that the DEFAULT keyword is a very good proposal. I just feel it is not the best solution for the problem case.

    -- Edit: Typo corrections and some word changes to clarify meaning

    I made this into a new db idea:

    Kim Berg Hansen
  • Kim Berg Hansen
    Kim Berg Hansen Senior System Developer Member Posts: 1,000 Bronze Trophy

    Totally agree with the proposal.

    But I have a slight problem with the sample case:

    So if the string expression comes from a fixed data source with a known fixed decimal separator, the TO_NUMBER call should work no matter what the session NLS_NUMERIC_CHARACTERS are.

    If the data source is fixed and the decimal separator is fixed,we should use a fixed format mask.
    The problem here is that a way to specify the intended format mask is missing.
    In my eye the most logical solution would be to use this:

    to_number('123.456', '99999999999999999999.99999999999999999999')

    It seems to work as intented, as can be proven easily with the following example.'

    Note my nls session have been set to GERMAN, so the sessions decimal separator is a ","

    select to_char(to_number('1,234,567.8', '99999999999999999999.99999999999999999999'),'99999999999999999990D99999999999999999999') from dual

        

    1234567,8

    Although the suggestion with the default keyword provides a workaround, this still requires to set a nls setting via the third parameter, even if we do not want an nls dependency in this case.
    Maybe a more flexible format mask would be the better choice for this specific example.
    My personal preference would be that 
    - a 9 at the end after the decimal separator allows as many 9s as possible (unless the FX modifier is used).
    - a 9 before the decimal separator and at the start (excluding things like FX/FM modifiers or signs) extends to as many possible 9s as needed.

    This would then allow the following solution (which currently gets ORA-01722)

    to_number('1,234,567.8', '9.9')

    expected result (german nls settings):

    1.234.567,8

    Unfortunately this would also break existing code. So it might need some tweaking, like another format modifier to explicitly specify this behaviour.

    Kind of the opposite of FX. Lets call it FLex format.

    to_number('1,234,567.8', 'FL9.9')

    1.234.567,8


    This doesn't change my opinion that the DEFAULT keyword is a very good proposal. I just feel it is not the best solution for the problem case.

    -- Edit: Typo corrections and some word changes to clarify meaning

    Some good points, Swen.

    Yes, for the specific usecase it would be possible to use better format masks, if such were available. Good idea that you created.

    Using the hardcoded dot/period in the format mask only works for cases where the known string format uses a dot/period for decimal separator - then it works regardless of NLS setting.

    If you are in the reverse situation, where for example a US company needs to read fixed data from Germany, then that is not possible without using NLS in the conversion somehow.

    And then we are back to the case that there is no format element that specifies the same as omitting the format element.

    I could also for this particular case have asked for a format element that would have the same behaviour as not specifying a format.

    But then I thought - why not make it more generic for all functions, not just TO_NUMBER ;-)

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    Some good points, Swen.

    Yes, for the specific usecase it would be possible to use better format masks, if such were available. Good idea that you created.

    Using the hardcoded dot/period in the format mask only works for cases where the known string format uses a dot/period for decimal separator - then it works regardless of NLS setting.

    If you are in the reverse situation, where for example a US company needs to read fixed data from Germany, then that is not possible without using NLS in the conversion somehow.

    And then we are back to the case that there is no format element that specifies the same as omitting the format element.

    I could also for this particular case have asked for a format element that would have the same behaviour as not specifying a format.

    But then I thought - why not make it more generic for all functions, not just TO_NUMBER ;-)

    You are correct that for the opposite case where the input string has a different but known NLS than the session and the default, we need to add the third parameter.

    select to_char(to_number('1.234.567,8','9999999999999999999D9999999999999999999','nls_numeric_characters=,.'), 'FM9999999999999999990D9999999999999999999') from dual;
    1234567.8