Hi I'm using DB 18c XE for some development and I can't figure out how to make the VALIDATE_CONVERSION function run properly with its full syntax:
Testcase:
alter session set NLS_NUMERIC_CHARACTERS = '.,';
drop table my_test purge;
create table my_test (c1 CLOB constraint my_json_constraint CHECK (c1 Is JSON));
--truncate table my_test;
insert into my_test (c1) values ('{"value":"0,00,0"}');
insert into my_test (c1) values ('{"value":"1234.07"}');
commit;
select t.c1.value,
VALIDATE_CONVERSION(t.c1.value as NUMBER, '9999D99','NLS_NUMERIC_CHARACTERS = ''.,''') as is_valid
from my_test t;
Is there a way to make it work as the current result is an ORA-00939?
Thanks
Loïc