Chapter 6 - Database Stored Object Migration

Abstract

Stored database objects are stored procedures, views, and triggers that reside in the database management system. Stored objects in the database are both a blessing and a curse. This chapter discovers how SQL Developer can be used to greatly reduce the effort required to migrate stored procedures, views, and triggers from other RDBMSes to Oracle. Stored procedure migration is typically the most time-consuming aspect of database object migration. This is because stored procedures contain both vendor-specific SQL and procedural language code. Stored procedures also have complex business logic and typically contain thousands, if not hundreds of thousands, of lines of code. Trigger migration is made more time-consuming when there are DDL statements or transactions. Oracle has workarounds for DDL statements and transactions in triggers. View migration is by far the easiest of the three migration types, as views contain no vendor-specific procedural language and the SQL in views is typically less complex. This chapter demonstrates that migrating database stored objects is easy using Oracle SQL Developer. SQL Developer captures, converts, and generates the source database stored objects.