Job - ORACLE - problem - ORA-01722 after upgrade version

Question

I upgrade ORACLE to version 19. And the automated task stopped working - however, the manual procedure call works fine, while the automatic task execution does not work properly.

I am getting an error: 'ORA-01722: invalid numberORA-06512, line 4'

I've a table : TEST_TABLE (field names are examples):

1. Test1 varchar (16 byte)
2. Test2 number
3. Test3 number
4. Test4 number
5. Test5 number
6. Test6 timestamp (6)

My procedure:

create or replace TEST_PROCEDURE AS
BEGIN
 execute immediate 'TRUNCATE TABLE TEST_TABLE';
INSERT INTO TEST_TABLE ( Test1, Test2, Test3, Test4, Test5, Test6 ) 
SELECT Test1, Test2, Test3, Test4, Test5, sysdate FROM TABLE_TEST_DATA;
END;

As above - if I run it manually - it works, if in a task - it does not work.

The problem is with ORACLE 19. Version 12 is fine.

What could be the reason? This error code is indicative of character conversion - but in my case there is no error on this background.

Thank's.

Answer 1

Сheck the parameters NLS_DATABASE_PARAMETERS, NLS_INSTANCE_PARAMETERS, NLS_SESSION_PARAMETERS.

SQL> SELECT 'Database nls_numeric_characters = "' || VALUE || '"'
  2    FROM NLS_DATABASE_PARAMETERS
  3  WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS'
  4  UNION ALL
  5  SELECT 'Instance nls_numeric_characters = "' || VALUE || '"'
  6    FROM NLS_INSTANCE_PARAMETERS
  7  WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS'
  8  UNION ALL
  9  SELECT 'Session nls_numeric_characters = "' || VALUE || '"'
 10    FROM NLS_SESSION_PARAMETERS
 11  WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';

'DATABASENLS_NUMERIC_CHARACTERS="'||VALUE||'"'
-------------------------------------------------------------------
Database nls_numeric_characters = ".,"
Instance nls_numeric_characters = ""
Session nls_numeric_characters = ",."

You need to add correct NLS_NUMERIC_CHARACTERS EXECUTE IMMEDIATE 'alter session set NLS_NUMERIC_CHARACTERS = '',.'''; or set correct NLS_DATABASE_PARAMETERS.

CREATE TABLE DEMIN.TABLE_TEST_DATA
(
    TEST1    VARCHAR2 (28),
    TEST2    VARCHAR2 (28),
    TEST3    VARCHAR2 (28),
    TEST4    VARCHAR2 (28),
    TEST5    VARCHAR2 (28)
);

CREATE TABLE DEMIN.TEST_TABLE
(
    TEST1    NUMBER (28, 4),
    TEST2    NUMBER (28, 4),
    TEST3    NUMBER (28, 4),
    TEST4    NUMBER (28, 4),
    TEST5    NUMBER (28, 4),
    TEST6    DATE
);

INSERT INTO DEMIN.TABLE_TEST_DATA VALUES('1,5','1,5','1,5','1,5','1,5');
COMMIT;

CREATE OR REPLACE PROCEDURE DEMIN.TEST_PROCEDURE
AS
BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST_TABLE';
    EXECUTE IMMEDIATE 'alter session set  NLS_NUMERIC_CHARACTERS = '',.''';
    INSERT INTO DEMIN.TEST_TABLE (TEST1, TEST2, TEST3, TEST4, TEST5, TEST6)
         SELECT TEST1, TEST2, TEST3, TEST4, TEST5, SYSDATE
           FROM DEMIN.TABLE_TEST_DATA;
    COMMIT;
END;


SQL> TRUNCATE TABLE TEST_TABLE;

Table truncated.

SQL> SELECT Test1, Test2, Test3, Test4, Test5, sysdate FROM TEST_TABLE;

no rows selected

SQL> SELECT Test1, Test2, Test3, Test4, Test5, sysdate FROM TABLE_TEST_DATA;

TEST1                        TEST2                        TEST3                        TEST4                        TEST5                        SYSDATE
---------------------------- ---------------------------- ---------------------------- ---------------------------- ---------------------------- ---------
1,5                          1,5                          1,5                          1,5                          1,5                          11-JAN-22

SQL> select 5/10 from dual;

      5/10
----------
        ,5

SQL> alter session set  NLS_NUMERIC_CHARACTERS = '.,';

Session altered.

SQL> select 5/10 from dual;

      5/10
----------
        .5

SQL> exec TEST_PROCEDURE;

PL/SQL procedure successfully completed.

SQL> SELECT Test1, Test2, Test3, Test4, Test5, sysdate FROM TEST_TABLE;

     TEST1      TEST2      TEST3      TEST4      TEST5 SYSDATE
---------- ---------- ---------- ---------- ---------- ---------
       1,5        1,5        1,5        1,5        1,5 11-JAN-22

SQL> alter session set  NLS_NUMERIC_CHARACTERS = '.,';

Session altered.

SQL> SELECT Test1, Test2, Test3, Test4, Test5, sysdate FROM TEST_TABLE;

     TEST1      TEST2      TEST3      TEST4      TEST5 SYSDATE
---------- ---------- ---------- ---------- ---------- ---------
       1.5        1.5        1.5        1.5        1.5 11-JAN-22

SQL>