UTF-8 Migration: Errors running FINALIZE SQLs in DBeaver

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Different problems during the Database conversion with UTF-8 DB Migration Action Pack

The following 2 errors were encountered while running the 2nd SQL script in the Offline Part of the migration:

1/ The 2nd finalize SQL ended with an error immediately:

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 ||
...

After correcting the issue a second issue was encountered:

2/ Error running 2nd SQL script:

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

Environment

• Package.UTF8.DB.Migration version 1.0.2
• Oracle
• DBeaver as a Database Management Tool

Cause

These issues are caused by using DBeaver 

Resolution

The SQL scripts are verified using official database tools. If you use third-party tools, please adjust the SQL queries as needed.

The following tools, which are used for testing are recommended:

• Oracle: SQL Developer
• DB2: IBM Data Studio
• MS SQL Server: SQL Server Management Studio

A note has also been added to the documentation concerning this, here:

https://docs.automic.com/documentation/webhelp/english/AA/24.2/DOCU/24.2/Automic%20Automation%20Guides/Content/Installation_Upgrade/install_upgrade_MigratingAEDB.htm

Additional Information

The reference for this bug is DE124860