- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
Parameters should accept keyword DEFAULT to indicate we want same behaviour as if parameter was not
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.
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;
The two inserts behaved identically.
In PL/SQL the mechanism is a bit different.
create or replace procedure proc1 (
, p2 varchar2 DEFAULT 'P2 DEFAULT'
, p3 varchar2 DEFAULT 'P3 DEFAULT'
call proc1('P1 ACTUAL');
call proc1('P1 ACTUAL', p3=>'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:
- The expression to be converted to a number (mandatory)
- The format model (optional)
- NLS parameters (optional
There is no way to call TO_NUMBER where expression and NLS parameters are specified, but not format model.
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 = '.,']';
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='.,']')
-- NULL as format model does not mean "use default", this returns NULL
select to_number('123.456', NULL, q'[nls_numeric_characters='.,']')
-- DEFAULT is not valid, gets ORA-00936
select to_number('123.456', DEFAULT, q'[nls_numeric_characters='.,']')
-- 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='.,']')
-- TM9 as input format model isn't working, gets ORA-01722
select to_number('123.456', 'TM9', q'[nls_numeric_characters='.,']')
-- This is awkward and how many nines should I add to be safe?
select to_number('123.456', '99999999999999999999D99999999999999999999', q'[nls_numeric_characters='.,']')
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='.,']')
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.)