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.)