E-Business Non-CDB to PDB Conversion errors with ORA-01722: invalid number (Doc ID 2705556.1)

Last updated on JULY 27, 2023

Applies to:

Symptoms

On : 12.1.3 or 12.2.x version, Database for Application Technology

When attempting to perform PDB conversion for an EBS database to 19c multitenant,
the following error occurs.

ERROR
-----------------------

TXK_CREATE_PDB
*******FATAL ERROR*******
PROGRAM : (/u01/app/oracle/product/12.1.0.2/dbhome_1/appsutil/bin/txkCreatePDB.pl)
TIME : Tue Aug 25 18:17:52 2020
FUNCTION: main::convertNonCDBToPDB [ Level 1 ]
ERRORMSG: PDB conversion did not complete successfully.

The script output convert_noncdb_to_pdb.out is showing error:
SQL> -- Check that we have no invalid table data
DOC>     The following statement will cause an "ORA-01722: invalid number"
DOC>     error, if the database contains invalid data as a result of type
DOC>     evolution which was performed without the data being converted.
DOC>
DOC>     To resolve this specific "ORA-01722: invalid number" error:
DOC>       Perform the data conversion (details below) in the pluggable database.
...
DOC>#
SQL>
SQL> declare
 2    do_abort boolean := false;
 3  begin
 4    if dbms_preup.condition_exists ('INVALID_SYS_TABLEDATA') then
 5      -- dump out the info
 6      dbms_preup.run_check('INVALID_SYS_TABLEDATA');
 7      do_abort := TRUE;
 8    end if;
 9    if dbms_preup.condition_exists ('INVALID_USR_TABLEDATA') THEN
10      -- dump out the info
11      dbms_preup.run_check('INVALID_USR_TABLEDATA');
12      do_abort := TRUE;
13    END IF;
14    If do_abort THEN
15      dbms_output.put_line ('Invalid table data.');
16      dbms_output.put_line ('Non-CDB conversion aborting.');
17      dbms_output.put_line ('For instructions, look for ORA-01722 in this script.');
18      dbms_output.put_line ('Please resolve these and rerun noncdb_to_pdb.sql.');
19      RAISE INVALID_NUMBER;
20    end if;
21  end;
22  /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 19

Changes

non-CDB is being converted to PDB

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Following either
Note 2580629.1 - Interoperability Notes: Oracle E-Business Suite Release 12.1 with Oracle Database 19c
or
Note 2552181.1 - Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c

2. In the step for converting the non-CDB to PDB, this error happens
2.5 Convert Database to Multitenant Architecture
Step 5. Create the PDB

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.