Oracle Virtualization and Cloud Consulting
view counter

Application Migration - Part 1

Thanks to Barry McGillin for this story

For the last couple of releases SQLDeveloper has added features which help our users analyze their applications.  Now, when I say analyze, I really just mean search the application code for items of interest which either need to be reported on, or need to be changed to work with Oracle.


This feature within the migration capabilities  allows you to look at application code and search it for particular items that are of interest. Lets take a very simple example of what we want to look at.  I'm using a sample shell script to use isql to get some data from sybase.

Oracle Virtualization and Cloud Consulting
view counter
#!/bin/sh
isql -UMYUSER -PMYPASS -SMYSERVER <use pubs2
go
select count(*) from authors
go
select top 5 au_lname,au_fname,postalcode from authors
go
EOF



Running this in sybase using pubs2 gives us these results.


bash-3.2$ sh test.sh

-----------
23

(1 row affected)
au_lname au_fname postalcode
---------------------------------------- -------------------- ----------
White Johnson 94025
Green Marjorie 94618
Carson Cheryl 94705
O'Leary Michael 95128
Straight Dick 94609

(5 rows affected)



Now, migrating this to Oracle by itself is a simple operation.  The SQL is simple and we can migrate it by hand, however, if we had many scripts like this we can use the application migration features to automate a lot of this.  


For this migration to work, we first need to migrate the database which we can do with the database migration features in SQLDeveloper.   Firstly, we use SQLDeveloper to connect to Sybase.  We can see the structure of the table for authors here.



Next we create a migration repository in an oracle schema and migrate the pubs2 database to oracle.  When its complete, we can see the status here.




Now we have the database moved to oracle, we can see the tables and data when we create a connection to the new schema for dbo, which by default is called dbo_pubs2.




Now, when we migrate the application and run it against oracle, we have a database and data to use.
So, in a perfect world, we can get the application to run out of the box after migration.  Lets manually rewrite this and see what it looks like.

#!/bin/sh

sqlplus dbo_pubs2/dbo_pubs2 << EOF

select count(*) from authors;

select au_lname, au_fname, postalcode from authors where rownum <=5;

EOF

and running this against our migrated database gives us this output.

ORACLE>sh test.sh

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 6 12:24:29 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL>
COUNT(*)
----------
23

SQL> SQL>
AU_LNAME AU_FNAME POSTALCODE
---------------------------------------- -------------------- ----------
White Johnson 94025
Green Marjorie 94618
Carson Cheryl 94705
O'Leary Michael 95128
Straight Dick 94609

SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE>



So at this stage, the we have a migrated database, and we also have a mockup of what we want our application changes to be for changing the application.  In the next post, Application Migration - Part 2, we'll look at migrating the script using the application migration features of SQL Developer to change the sql and the call to isql.



Read the entire article at its source

view counter