I did the migration of the application from IBM Power to Exadata.
Data were transferred using DataPump.
There were no serious errors after import. All software components were compiled after import.
But the the customer complained error: "ORA-06502: PL/SQL: character string too small"
1.
One of reason of ORA-6502 was found quickly, the query
col PROPERTY_NAME for a30
col PROPERTY_VALUE for a36
select PROPERTY_NAME,PROPERTY_VALUE from database_properties order by 1;
showed difference in NLS_LENGTH_SEMANTICS between source and target databases.
Source database was NLS_LENGTH_SEMANTICS='CHAR', but on the Exadata database was ='BYTE'.
The command
alter system set NLS_LENGTH_SEMANTICS='CHAR' scope=both sid='*';
was completed successfully. And there were no errors in alert.log and in sqlplus session.
But ORA-6502 was appeared and again !
It disappeared after the database was recreated with NLS_LENGTH_SEMANTICS='CHAR' in parameter file.
But ORA-6502 don't disappeared again !
2.
We spend a week and found the 2nd underwater stone. It was the special PL/SQL parameter !
If you look into ALL_PLSQL_OBJECT_SETTINGS, then you'll see NLS_LENGTH_SEMANTICS column. This means, that oracle PL/SQL code depends of NLS_LENGTH_SEMANTICS settings !
The query showed:
select NLS_LENGTH_SEMANTICS from ALL_PLSQL_OBJECT_SETTINGS where owner=...
NLS_LENGTH_SEMANTICS
--------------
BYTE
Where the 'BYTE' become from ?
We did
alter session set NLS_LENGTH_SEMANTICS='CHAR';
alter package T_BSCMS compile body;
and ORA-6502 disappeared !
So, problem was solved, but where the BYTE become from ?
After we moved rows with DataPump the customer admin copied and compiled some the package bodies using SQL Developer.
As you guess, SQL Developer uses 'BYTE' as default setting !
Look:
Tools -> Preferences -> Database -> NLS -> Length
Data were transferred using DataPump.
There were no serious errors after import. All software components were compiled after import.
But the the customer complained error: "ORA-06502: PL/SQL: character string too small"
1.
One of reason of ORA-6502 was found quickly, the query
col PROPERTY_NAME for a30
col PROPERTY_VALUE for a36
select PROPERTY_NAME,PROPERTY_VALUE from database_properties order by 1;
showed difference in NLS_LENGTH_SEMANTICS between source and target databases.
Source database was NLS_LENGTH_SEMANTICS='CHAR', but on the Exadata database was ='BYTE'.
The command
alter system set NLS_LENGTH_SEMANTICS='CHAR' scope=both sid='*';
was completed successfully. And there were no errors in alert.log and in sqlplus session.
But ORA-6502 was appeared and again !
It disappeared after the database was recreated with NLS_LENGTH_SEMANTICS='CHAR' in parameter file.
But ORA-6502 don't disappeared again !
2.
We spend a week and found the 2nd underwater stone. It was the special PL/SQL parameter !
If you look into ALL_PLSQL_OBJECT_SETTINGS, then you'll see NLS_LENGTH_SEMANTICS column. This means, that oracle PL/SQL code depends of NLS_LENGTH_SEMANTICS settings !
The query showed:
select NLS_LENGTH_SEMANTICS from ALL_PLSQL_OBJECT_SETTINGS where owner=...
NLS_LENGTH_SEMANTICS
--------------
BYTE
Where the 'BYTE' become from ?
We did
alter session set NLS_LENGTH_SEMANTICS='CHAR';
alter package T_BSCMS compile body;
and ORA-6502 disappeared !
So, problem was solved, but where the BYTE become from ?
After we moved rows with DataPump the customer admin copied and compiled some the package bodies using SQL Developer.
As you guess, SQL Developer uses 'BYTE' as default setting !
Look:
Tools -> Preferences -> Database -> NLS -> Length
