Different problems during the Database conversion to UTF8 with UTF-8 DB Migration Action Pack
A. Operation interrupted during insert into RT in 1st generated SQL
Failure during the insert into the RT table.
SQL Error \[1013\] \[72000\]: ORA-01013: user requested cancel of current operation Error : 1013, Position : 71, Sql = insert into RT NOLOGGING select * from RT@uc4source where RT_AH_Idnr > 216726010, OriginalSql = insert into RT NOLOGGING select * from RT@uc4source where RT_AH_Idnr > 216726010, Error Msg = ORA-01013: user requested cancel of current operation
B. Invalid 'ADD CONSTRAINT' statements in 1st generated SQL
However, after resuming the first step, an error popped up when adding constraints.
SQL Error \[904\] \[42000\]: ORA-00904: "AH_IDNR": invalid identifier Error : 904, Position : 79, Sql = alter table AV add constraint FK_AV_AH foreign key (AV_AH_IDNR) REFERENCES AV (AH_IDNR) NOVALIDATE, OriginalSql = alter table AV add constraint FK_AV_AH foreign key (AV_AH_IDNR) REFERENCES AV (AH_IDNR) NOVALIDATE, Error Msg = ORA-00904: "AH_IDNR": invalid identifier ORA-00904: "AH_IDNR": invalid identifier I gathered that the table that should be referenced was AH, not AV. 130 of the 'ADD CONSTRAINT' statements include the same mistake, referring back to the same table instead of the foreign one. I fixed these statements. The remaining eight 'ADD CONSTRAINT' statements required different modifications: alter table MSG add constraint FK_MSG_MSGA foreign key (MSG_OH_IDNR) REFERENCES MSG (MSGA_OH_IDNR) NOVALIDATE; \-\- Changed referenced table to MSGA alter table MSG add constraint FK_MSG_MSGT foreign key (MSG_MSGT_TYPE) REFERENCES UC_MSGT (MSGT_TYPE) NOVALIDATE; -\- Changed referenced table to UC_MSGT alter table MSGA add constraint FK_MSGA_OH foreign key (MSGA_OH_IDNR) REFERENCES OH (OH_IDNR) NOVALIDATE; -\- Changed referenced table to OH alter table MSGA add constraint FK_MSGA_MSGDT foreign key (MSGA_MSGDT_SHORT) REFERENCES UC_MSGDT (MSGDT_SHORT) NOVALIDATE; \-\- Changed referenced table to UC_MSGDT alter table MSGA add constraint FK_MSGA_MSGKD foreign key (MSGA_MSGKD_SHORT) REFERENCES UC_MSGKD (MSGKD_SHORT) NOVALIDATE; \-\- Changed referenced table to UC_MSGKD alter table MSGAN add constraint FK_MSGAN_MSGA foreign key (MSGAN_OH_IDNR) REFERENCES MSGA (MSGA_OH_IDNR) NOVALIDATE; -\- Changed referenced table to MSGA alter table MSGTX add constraint FK_MSGTX_MSG foreign key (MSGTX_MSG_IDNR) REFERENCES MSG (MSG_IDNR) NOVALIDATE; \-\- Changed referenced table to MSG alter table MSGTX add constraint FK_MSGTX_MSGL foreign key (MSGTX_MSGL_SHORT) REFERENCES UC_MSGL (MSGL_SHORT) NOVALIDATE; -\- Changed referenced table to UC_MSGL After making these modifications, I was able to complete adding the constraints.
C. Problem setting the statements terminator in the 2nd SQL script
The 2nd finalize SQL, it ended immediately with an error.
SQL Error \[900\] \[42000\]: ORA-00900: invalid SQL statement
Error : 900, Position : 0, Sql = /
declare
cursor c is SELECT to_char('ALTER SEQUENCE ' \|\| sequence_name \|\|
...
D. Error running 2nd SQL script
After fixing the statement terminator problem, the following problem can come up.
SQL Error \[6550\] \[65000\]: ORA-06550: line 1, column 25: PLS-00201: identifier 'V_USER' must be declared ORA-06550: line 1, column 32: PL/SQL: ORA-00904: : invalid identifier ORA-06550: line 1, column 7: PL/SQL: SQL Statement ignored ORA-06550: line 1, column 113: PLS-00372: In a procedure, RETURN statement cannot contain an expression ORA-06550: line 1, column 113: PL/SQL: Statement ignored Error : 6550, Position : 24, Sql = begin select owner into v_user from all_objects where object_name='GET_SCHEMA_NAME' and object_type='FUNCTION'; return v_user; end;, OriginalSql = begin select owner into v_user from all_objects where object_name='GET_SCHEMA_NAME' and object_type='FUNCTION'; return v_user; end;, Error Msg = ORA-06550: line 1, column 25: PLS-00201: identifier 'V_USER' must be declared
Package.UTF8.DB.Migration version 1.0.2
Database Oracle 19C
DBeaver as a Database Management Tool
This has been classified as a defect of the Action Package.UTF8.DB.Migration
A fix for this problem will come in a near version of Action Pack UTF8 Database Migration Action Pack
Bug ID: PCK_UTF8_DB-1
Bug Title: Problems with FINALIZE SQL in Package.UTF8.DB.Migration v1.0.2