A SQL Developer ... @dermotoneill

Sybase and SQL Server Image Data Move

2012-01-27T09:17:00.000-08:00

SQL Developer can move data online and offline.

Online is really only for small amounts of data (<100mb).

For larger sets of data with greater performance and much better logging, the Offline Data Move feature should be used.

SQL Developer will create two sets of scripts.

One to dump out the data using Sybase/SQL Servers BCP tool to DAT files on the disk.

The second set of scripts to read the DAT files and load the data into the Oracle tables using Oracle SQL*Loader.

As the scripts are just plain text files they should be inspected and can be modified to suit your needs.

Because SQL Developer knows the names of all the tables and each columns name/datatype it can do a good job of defining these scripts, so very little has to be modified.

Currently there is an issue moving large Images from Sybase/SQL Server to Oracle using the offline data move scripts SQL Developer generate. But with a little change you can get them to move correctly.

For this little test , the first thing is to

Insert an Image into Sybase using BCP.

Create a table with one column in Sybase of type IMAGE

CREATE TABLE loadimage (col1 IMAGE)
go

Make sure the Sybase database can INSERT from BCP (note this database is called loadimage)

use master
go
sp_dboption "loadimage", "select into", true
go
use loadimage
go
checkpoint
go

The bcp.fmt needs to have the correct size of the imagefile specified, in this case 1551333.

10.0
1
1 SYBIMAGE 0 1551333"" 1 col1

From the command line load an image into the Sybase table using Sybase BCP

bcp loadimage..loadimage in image3.jpg -Usa -P -S -f bcp.fmt -T2000000

This should load up the image3.jpg into the Sybase table.

It can be viewed from SQL Developer.

Just browse to the Sybase table, click on the data tab. Double click the (BLOB) item and choose "View As Image".

Now we need to move it to Oracle.

Migrate the Sybase database to Oracle, but choose Offline Data Move in step 8 of the migration wizard.

The following changes need to be performed against the scripts SQL Developer generates for you.

The unload_script.bat needs to be changed as BCP by default will truncate to 32K.

mkdir data

mkdir log

bcp "[loadimage].[dbo].[loadimage]" out "data\dbo_loadimage.loadimage.dat" -T 1552503 -c -t "" -r "" -U%2 -P%3 -S%1

The -T 1552503 denotes the largest Image in that column.

This can be found by querying the Sybase table.

select max(datalength()) from

Now the DAT file will include all the IMAGE data in HEX format, and it will not be truncated.

Next we have to load it into Oracle and those scripts need a little change as well.

SQL Developer creates scripts which

1) Create a new CLOB column to hold the HEX string

2) Process the CLOB HEX string into a BLOB using a procedure

3) Remove the CLOB column

This is necessary as you cannot load a large HEX string directly into a BLOB column in Oracle.

This action is performed for you, but this is a default limit to the size of the HEX string SQL*Loader will read, and this needs to be changed.

Open the control/dbo_loadimage.loadimage.ctl file

load data

infile 'data/dbo_loadimage.loadimage.dat'

"str ''"

into table dbo_loadimage.loadimage

fields terminated by ''

trailing nullcols

(

SQLDEVELOPER_CLOB_1 CHAR(20000000)

)

Change the size of the CHAR column. This is the temporary column the scripts will create for you to load in the HEX String.

Once this is done you can run the script to load the image data into the Oracle table.

oracle_ctl.bat system oracle@11GR1

Notice that the rows are loaded fairly fast into the CLOB columns as HEX strings. But it does take some time to process these HEX strings into BLOBs.

Once the load is finished the Images can be inspected in SQL Developer

JDeveloper Group By Insight Preference

2011-10-12T06:39:00.001-07:00

During Oracle OpenWorld a customer asked how to turn off the autogenerate of GROUP BY clause when using JDeveloper.

SQL Developer has a preference

But JDeveloper is missing this preference, and it is turned on by default.

This will be rectified in a future realese of JDeveloper but in the mean time there is a workaround.

1) Close JDeveloper.

2) Open JDevelopers product-preferences.xml file.

This can be found under

c:\Users\\AppData\Roaming\JDeveloper\\o.jdeveloper\

on windows 7. Or in the usual place applications persist preferences in your OS.

3)Add the following after the ide tag.

<hash n="DBConfig">
     <value n="AUTOGENERATEGROUPBY" v="false"/>
     <hash n="EXTENSION"/>
     <value n="INDENT_CACHE_NUMBER" v="2"/>
     <value n="INDENT_CACHE_USE_TAB" v="false"/>
  </hash>

4) Restart JDeveloper, and Autogenerate GROUP BY should not turned off.

Very Large Migrations

2011-08-26T04:07:00.000-07:00

Most of the time SQL Developer does a good job of migrating a database from SQL Server, Sybase ,... To Oracle. But there are some tricks to help make very large migrations perform smoother.

This talks about Large migrations in terms of object numbers , not amount of data in tables.

If your migrating 50 databases at once, and each has 1,000 tables, 1,000 views , 1,000 procedures . That is a lot of meta data to churn through and sometimes we find

memory issues , open cursors ,... can be a problem.

We are always working to reduce the amount of resources required to perform the migrations , but for the moment (SQL Dev 3.0) here are a few tricks which just might make the difference between the migration failing and the migration completing.

Memory

By Default SQL Developer startups with

AddVMOption -XX:MaxPermSize=128M
(found in sqldeveloper\sqldeveloper\bin\sqldeveloper.conf)
AddVMOption -Xmx640M (found in sqldeveloper\ide\bin\ide.conf)
AddVMOption -Xms128M (found in sqldeveloper\ide\bin\ide.conf)

This is usually fine, but ... if you have more memory, SQL Dev maybe able to use it.

When using SQL Developer 32 bit on a 32 bit machine with a 32 bit JDK. The following is ok

AddVMOption -XX:MaxPermSize=128M
(found in sqldeveloper\sqldeveloper\bin\sqldeveloper.conf)
AddVMOption -Xmx1024M (found in sqldeveloper\ide\bin\ide.conf)
AddVMOption -Xms256M (found in sqldeveloper\ide\bin\ide.conf)

When using SQL Developer 64 bit on a 64 bit machine with a 64 bit JDK. The following is ok . As long as you have the memory.

AddVMOption -XX:MaxPermSize=512M
(found in sqldeveloper\sqldeveloper\bin\sqldeveloper.conf)
AddVMOption -Xmx4096M (found in sqldeveloper\ide\bin\ide.conf)
AddVMOption -Xms2048M (found in sqldeveloper\ide\bin\ide.conf)

Oracle Database

By Default Oracle uses a small maximum of open cursors allowed. When migrating large databases SQL Developer does tend to open a lot of cursors against the Oracle database which has the migration repository. We have worked on minimizing this and will continue to do so, but the workaround at the moment is to set this limit higher in the Oracle database you create your migration repository in.

ALTER SYSTEM SET open_cursors=10000 COMMENT='' SCOPE=BOTH

Note if you are migrating multibyte databases (objects names are in chinese, japanese ,.. ) you should create the migration repository in a UTF-8 Oracle database. Otherwise the object names may not be saved/migrated correctly.

SQL Developer

SQL Developer obviously interacts with the migration repository. For example to convert a stored procedure. It queries the migration repository to retrieve the captured SQL and then inserts into the repository the converted SQL. Not only that, SQL Developer also queries the migration repository to identify name changes, data type of columns and so on. These round trips back and forth to the migration repository can slow down a migration.

If you are migrating large amounts of Stored Procedures, Triggers, Views, Functions it is best to install SQL Developer on the same machine as the Oracle database that contains the migration repository. This will remove the network overhead and speed up the migration.

Migrate Incrementally

SQL Developers Migration Wizard allows you to define the entire migration you want up front, and then kick it off. This is ok for smaller migrations, but I think it best that most migrations perform the migration incrementally.

The migration wizard allow you perform each step of the migration by itself. This enables you to review each step of the migration before proceeding to the next.

Just click the "Proceed to Summary" checkbox at the bottom left of the wizard and then click Next. The wizard will then only perform the steps you have choosen so far.

When this step is complete you will get a chance to review the result and then kick off the wizard again , straight back into the wizard at the correct step.

Not only that but breaking it into steps also helps with memory/cursor issues.

Redundant Objects

Some times a database which is being migrated contains a lot of objects which are redundant or not required to migrate. If there is a substantial amount of these objects, its best to remove them from the captured model so as they do not consume resources going forward.

Right click the objects in the captured model you do not want and choose delete.

Note that this is a model and not connected to your Source SQL Server, Sybase ,... database.

So those source objects are not deleted , just items in your model saved in the migration repository.

Generation Script

Very large databases will have very large generation script. By default SQL Developer generates one big script which is then run to create all the target objects in Oracle.

But there is a preference (Migration > Generation Options > A File Per Object) to split each object (table,trigger,view,...) definition into a separate file.

Then a master script calls each file individually to create the objects. This means a much smaller master file.

It is also handy if you want to source control the object definitions.

Empty Space and Single Space Data Move

2011-03-02T09:14:00.001-08:00

Empty Space strings, that is Strings with no text whats so ever '', are treated differently by different databases.
Oracle treats an empty string '' as NULL
Sybase treats an empty string '' as a single spaced string ' '
Some other database support empty strings

In the migration preferences you can decide how to handle empty strings.
You can either migrate them to
1) ' ' A single space.
or
2) NULL (which is how Oracle would interpret an empty string anyway)

There is one other thing to note.
JTDS 1.2 (the recommended JDBC driver for Sybase and SQL Server) returns an empty string even when the data in the Sybase or SQL Server database is a single space.
This is a bug in JTDS, but it should be noted that if you keep the default option of migrating empty strings to a single space, you should see no issue.

FLOAT data type migration

2011-02-11T03:34:00.000-08:00

Sybase FLOAT is generally used to save non integer numbers like fractions where no number of precision can hold the exact value. Sybase FLOATs do not store an exact value
"It stores slightly imprecise representations of real numbers as binary fractions at the hardware level"
http://www.sybase.com/detail?id=20313

Oracle has two data types, FLOAT and BINARY_FLOAT.
http://stackoverflow.com/questions/332492/oracle-floats-vs-number

FLOAT is really a decimal data type with exact values (basically it is a NUMERIC)
BINARY_FLOAT is a binary data type which better maps to Sybase FLOAT data type

If you migrate from Sybase FLOAT to Oracle FLOAT any value inserted will be treated as a specific explicit numeric, which will not behave like Sybase FLOAT.
If you migrate from Sybase FLOAT to Oracle BINARY_FLOAT then any value inserted will be treated as a binary imprecise number, just like Sybase.

SYBASE
drop table testfloat
go
create table testfloat (floatcol float)
go
insert into testfloat values(1.005)
go
insert into testfloat values(1.0049999999999999)
go
select * from testfloat --both values are displayed as 1.005
go
select * from testfloat where floatcol = 1.005 --returns both rows
go
select * from testfloat where floatcol = 1.0049999999999999 --returns both rows
go
select * from testfloat where floatcol = 1.0049999999999998 --returns both rows
go
select * from testfloat where floatcol = 1.0049999999999997 --returns NO rows
go
select * from testfloat where floatcol = 1.004999994--returns NO rows
go
select * from testfloat where floatcol = 1.004999493 --returns NO rows
go

So it looks like Sybase stores 1.005 as 1.0049999999999999, but displays it to the clients (like isql, jdbc) as 1.005
A comparison can be made with the literal 1.005 (though not recommended as float should never be compared in this way)

ORACLE
DROP table testfloat;
create table testfloat(floatcol float, binaryfloatcol binary_float ,varchar2floatcol varchar2(100) );
insert into testfloat values(1.005,1.005,'1.005') ;
insert into testfloat values(1.0049999999999999,1.0049999999999999,'1.0049999999999999');
select * from testfloat; --both values are displayed as 1.005 for BINARY_FLOAT and a mix as FLOAT
select * from testfloat where floatcol = 1.005; --returns one row
select * from testfloat where floatcol = 1.0049999999999999 ; --returns one row
select * from testfloat where floatcol = 1.0049999999999998 ; --returns NO rows
select * from testfloat where floatcol = 1.0049999999999997 ; --returns NO rows

select * from testfloat where binaryfloatcol = 1.005; --returns two rows
select * from testfloat where binaryfloatcol = 1.0049999999999999 ; --returns two rows
select * from testfloat where binaryfloatcol = 1.0049999999999998 ; --returns two rows
select * from testfloat where binaryfloatcol = 1.0049999999999997 ; --returns two rows

select * from testfloat where binaryfloatcol = 1.004999994 ; --returns two rows
select * from testfloat where binaryfloatcol = 1.004999493 ; --returns no row

Oracle stores FLOAT values as explicit numbers whereas BINARY_FLOAT values are not as precise. Oracle also displays 1.005 for both values in BINARY_FLOAT.

Conclusion
From the above test cases it looks like Oracle BINARY_FLOAT is a much better match than Oracle FLOAT for Sybase FLOAT, but it should be noted that since Sybase FLOAT and Oracle BINARY_FLOAT are not precise numbers , comparing equivalence is not identical between databases.

floats , be they Sybase FLOATs or Oracle BINARY_FLOATs should never be directly compared, rather a range of values should be matched.

Notes
Sybase BCP dumps out both floats as 1.0049999999999999
JDBC query against Sybase returns both floats as 1.005.

So during a SQL Developer data move, online will insert 1.005 , but offline will insert 1.0049999999999999. This doesn't really matter if the Oracle data type is BINARY_FLOAT, but it does if you use Oracle FLOAT data type.

Migration Reports 3.0

2011-02-09T03:57:00.000-08:00

We have revamped the migration reports in SQL Developer 3.0 to provide significantly more information about your migration. Instead of "hiding" this information under the Migration Report navigator , now the reports can be viewed by double clicking on any of the folder nodes in the Migration navigator. The reports are smart enough to tailor the information depending on where in the Migration Navigator you click.

Status

Gives you a heads up of the status of each migration project/model.
Also provides links which when double clicked, launch the migration wizard to perform the appropriate action

Summary

Provides the number of objects (tables, procedures, views , ... ) involved in each of the migration projects.It then breaks down into the number of objects in each database or owner.

Analysis

Provides fine grain details about the databases and objects being migrated.
SQL Size Chart
Visualize size of all SQL Objects
SQL Size Summary
Number of SQL Objects with similar sizes

SQL Size Details

Line size for every SQL Object
Columns
Details of all captured colums
Name Changes
All the name changes between the captured objects and the converted objects
Dependencies
Dependency tree between store procedures and other objects

Temporary Table Summary
Summary of the number of temporary tables per project / database
Temporary Table Details
List of all temporary tables identified and their parent procedure
Parameters
List of all procedure and function parameters including new params

Capture Issues

There should be no capture issues, but if an issues occurs it will be listed here

Conversion Status

Lists the number of Procedures, Triggers and Views which converted or failed to convert

Conversion Issues

Reports on all the issues which where encountered during the conversion.
Errors denote objects which failed to convert.
Limitations denote objects which converted but a particular statement or clause was not supported

Target Status

After selecting an appropriate connection to the target database and clicking refresh, this report will list all the objects in the converted model and their status in the target database. Valid, Invalid or Missing

Target Issues

After choosing the appropriate Target connection and clicking refresh, this looks at the issues of each object in the target database.

Data Quality

After selecting the appropriate
Model, Source Connection and Target Connection click Analysis.

You will be prompted to continue as you are reminded that a full table scan of all the tables on the source connection and target connection will take place.

SELECT count(*) from tablename is performed against each table and the values inserted into the migration repository.

Once complete, click refresh. Each table migrated is listed with the source row number and the target row number, making it easy to identify tables where all the data was not migrated

Note that SELECT count(*) from tablename may make a significant impact on the performance of the source and target database so best run only on non production databases.

Model Comparison

After choosing the first captured model and the second captured model click refresh. The differences between the models will be listed.

This is useful when trying to identify changes to your source database over a period of time , sometimes called the delta.

This knowledge can help identify any new or modified objects which require migration.

SQL Developer DB2 Connection

2010-12-22T00:47:00.000-08:00

SQL Developer 2.1.1 (and 3.0) supports browsing of IBM DB2 LUW 7, 8, 9 databases.
IBM DB2 iSeries and ZSeries Z/OS are not supported at this time.

To connect to DB2 LUW, SQL Developer first needs the correct JDBC driver.
Only one specific JDBC driver is supported.
db2jcc.jar with db2jcc_license_cu.jar

All other IBM drivers are not supported and may cause problems even if the above two drivers are specified. So best to only add the above two.

These drivers are usually found in your own DB2 database install or DB2 client install.
They are also available when you agree to the license (please check this first) and download IBMs DB2 Express-C database or Data Studio Standalone administration tools
http://www-01.ibm.com/software/data/db2/express/download.html

Once you have a DB2 client or database installed you can search for the specific JDBC jar files.

db2jcc.jar
db2jcc_license_cu.jar

Note you cannot use db2jcc4.jar and this would cause issues if added to SQL Developers Third Party JDBC Drivers list.

Heres what my preference page looks like. (note, JTDS is for SQL Server and Sybase)

Once the correct jar file has been added you now have an extra DB2 tab in the connections dialog

Once you add your connection details you can browse your DB2 database objects.

You can learn more about migrating to Oracle from IBM DB2 LUW here

SQL Server and Sybase Browsing

2010-12-02T05:23:00.000-08:00

SQL Developer 3.0 (EA3) makes it easier to browse your SQL Server and Sybase servers.

All databases on your server are now available under the one connection, not just the default databases selected in the new connection dialog.

As long as your login has the privileges you can browse the objects in databases other than your default database.

When it comes to issuing commands/queries in the worksheet you can reference objects outside of the default database explicitly
SELECT * FROM databasename.owner.table1

or you can change the default database using a worksheet "hint"
/*sqldev:stmt*/USE databasename;
SELECT * FROM table1

or you can choose the default databases in the navigator

Note that the default is set for the life of the session only. If you want to change the default database permanently you can do this in the New Connection Dialog. Just right click the connection and choose Properties. Then click "Retrieve Databases", choose the default databases and click save.

Another nice feature is the Schema Browser, which makes working on one database/owners objects much neater.

SQL Developer 3.0 Migration Features

2010-11-19T14:55:00.000-08:00

Barry McGillin has a breakdown of the new migration features of SQL Developer 3.0
http://barrymcgillin.blogspot.com/2010/10/migration-features-in-sql-developer-30.html

And an article on the new Estimation Report
http://barrymcgillin.blogspot.com/2010/10/migration-estimation-from-sql-developer.html

I'm going to flesh out some of these on this blog

Copy To Oracle

2010-11-11T03:59:00.000-08:00

SQL Developer 3.0 EA1 introduces the Copy to Oracle feature.

Copy to Oracle copies a table from a non Oracle database, like SQL Server, Sybase and MS Access to Oracle. The action can be found by right clicking one or more selected non Oracle tables and choosing "Copy To Oracle".

Its very handy if you just want to get a particular table or a handful of tables into Oracle including their data. There is no setup required and its very easy to do.

The "Migrate to Oracle" differs in that it provides user,procedures,trigger,view, constraints, keys, indexes and table migration. Whereas "Copy to Oracle" only supports the basic table migration.

MS Access customers may find "Copy to Oracle" particularly useful as it doesn't require any system privileges to access the databases metadata.
MS Access connections also have "Copy to Oracle" available on right click of the connection to easily allow all tables to be copied.

With all migrations , the migrated table and data should be verified as complete.

Workheet Hints

2010-11-11T03:56:00.000-08:00

SQL Developer currently doesn't support proprietary commands of non Oracle databases like SQL Server, Sybase ... . But a new feature call Worksheet Hints allows these commands to be executed.

SQL Developers Worksheet has to parse commands to identify their start and finish and their type (query, or update ,... ).
We recognize Oracle SQL , PL/SQL and SQL*Plus , but we have yet to work on other databases syntax.

So at the moment if you running a command against MySQL, SQL Server or Sybase, it has to be fairly similar to an Oracle statement for it to be recognized and run correctly.
EX: SELECT, INSERT,UPDATE,DELETE.

Things like MySQL
use [databasename]
or
show databases
are not recognized yet.

Full MySQL recognition will not make it into SQL Developer 3.0 ,
but SQL Developer 3 EA2 does allow you to give the Worksheet parser a hint as to what type of statement it is and how to execute it.

The hint is placed in front of the command, and
the command has to be terminated with a forward slash on its own line, or a semi colon.

The two hints are
/*sqldev:query*/ if your expecting a result set to be returned
/*sqldev:stmt*/ if you are expecting nothing returned

MySQL Example:
If you connect to your MySQL connection you can perform the following
/*sqldev:query*/show databases;
/*sqldev:stmt*/use dermot;
select database();
/*sqldev:stmt*/use information_schema;
select database();

The same goes for SQL Server , Sybase ,.... commands which are not recognized by our Worksheet.

Sybase Example:
/sqldev:query*/sp_help;

Appreciate this is not an ideal solution, but until we recognize the syntax correctly this may help out.
Just to be clear, we are not supporting all MySQL/SQL Server/Sybase commands yet. But hopefully using a "hint" might be of some use.

SQL Developer Automatic Connection Definition

2010-11-11T03:55:00.000-08:00

When creating users in Oracle , you then have to create a connection in SQL Developer, repeating the information.

So you would run something like this in the SQL Worksheet
CREATE USER dermo IDENTIFIED BY dermo;
GRANT CONNECT,RESOURCE TO dermo;

Then you would have to create a SQL Developer connection.

In SQL Developer 3.0 you can use the SQL Worksheet to create the connection for you.

CREATE CONNECTION connectionname(CREATE USER username IDENTIFIED BY password);
GRANT CONNECT,RESOURCE to username;

Cross Database Bridge Statement

2010-11-11T03:45:00.000-08:00

Oracle allows you to query separate databases using Database Links.
You can even setup a Database Link in an Oracle database with a non Oracle database such as SQL Server or Sybase.

Creating these types of Database Links can take some time, and sometimes you just want to reference a small bit of data on another Database and use it in your query.

SQL Developer allows you define connections to multiple databases including non Oracle databases. You can query each connection separately but until now you couldn't easily query different data sources (connections) at the same time in the one statement.

BRIDGE statement
This is a SQL Developer only command. It is not supported in SQL*Plus or directly in the Oracle database. SQL Developer interprets this statement and performs multiple actions to return the result.

BRIDGE statement can be used in two ways
1) To copy a table and its data from one connection to another
2) To facilitate cross connection/database queries

--*******INTRO*******
--The BRIDGE command is an Oracle SQL Developer client command
--It is not suported by any other product (SQL*Plus,....)
--It is used within Oracle SQL Developer to enable the "Copy to Oracle" feature and peform complex migration reports
--It is not suported as a standalone command, but can be experimented with
--The BRIDGE command is still immature and the parsing of its syntax including spaces is still basic.

-- *******SETUP*******
-- CREATE MS Access Northwind connection called, accessNorthwind
-- CREATE SQL Server 2005 Northwind connection called, sqlserverNorthwind
-- CREATE MySQL 5 world connection called, mysqlWorld
-- Run each part of this script against an Oracle connection


-- This new Oracle SQL Developer client command to create a SQL Developer Connection at the same time as creating a schema
CREATE CONNECTION oracleTest(GRANT DBA TO oracleTest identified by oracleTest);
CREATE CONNECTION oracleTest2(CREATE USER oracleTest2 identified by oracleTest2);
GRANT connect,resource,create view to oracleTest2;


--*******FEATURE SUMMARY*******
-- BRIDGE command can be used in two distinct ways
-- 1) TO COPY A TABLE from one connection (A third party connection or oracle connection) to another connection.
-- 2) BRIDGE QUERIES (TO QUERY TABLES OVER MULTIPLE CONNECTIONS)
-- COPY A TABLE is fairly straight forward, you specify the target table name and the query and connection used to define and populate the target table
-- BRIDGE QUERIES  uses the same method as above to move data from different connections to a target connection, but it then allows you to specify a query to run against the newly migrated tables/data.
-- It then DROPs the new Tables after the Query has run. From a user point of view it looks like a Query have taken place between different connections as they never see the target tables created,populated,queries and droped behind the scenes.

--*******SYNTAX*******
/*
<BRIDGE>:
BRIDGE <TABLEDEFINITION> ( ',' <TABLEDEFINTION>)*
[<targetQuery>] ;

<TABLEDEFINITION>:
[<targetConnectionName>](<newTargetTableName>)  AS [<sourceConnectionName>](<SOURCEQUERY>)[APPEND|REPLACE]

<SOURCEQUERY>:
SQL valid on the sourceConnection. Supports Dynamic Substitution.
({<defaultQuery}) gets replaced with a comma delimited list of values
{<defaultQuery} iterates the source query for each value returned

*/
--*******COPY A TABLE*******
-- CROSS CONNECTION TABLE/DATA MIGRATION
--This creates a new NEWcustomers1 table in the current connection(oracle), using a table from access (customers) referenced through the accessNorthwind connection.
BRIDGE NEWcustomers1 AS accessNorthwind(SELECT * FROM customers);

--Note that the SQL that defines the target table is run against the source Connection (accessNorthwind), so you have to use the source databases SQL syntax. In this case, MS Access.
BRIDGE NEWcustomers2 AS accessNorthwind(SELECT TOP 10 * FROM customers);

--A target table is defined using any valid query, so it doesnt have to be a straight copy of a particular table.Rows can be filtered, columns can be added, infact the target table could be based on a VIEW.
--In this case, no rows are returned , but an empty table NEWcustomers3 is created
BRIDGE NEWcustomers3 AS accessNorthwind(SELECT * FROM customers WHERE 1=2);

--If a target table name already exists, then an error is thrown.
--if you want to insert data to an existing table use the APPEND flag
BRIDGE NEWcustomers3 AS accessNorthwind(SELECT TOP 10 * FROM customers)APPEND;
BRIDGE NEWcustomers3 AS accessNorthwind(SELECT TOP 10 * FROM customers)APPEND;

--If the table already exists but you want to replace it , use the REPLACE flag
BRIDGE NEWcustomers3 AS accessNorthwind(SELECT TOP 10 * FROM customers)REPLACE;

--Heres an example of how the target table can be defined using an "inline view". Note, NOW() is a MS Access function returning the current time
BRIDGE NEWcustomers4 AS accessNorthwind(SELECT contactName, address AS NewAddress, NOW() AS currentTime FROM customers);

--DYNAMIC SUBSTITUTION
--A lot of times you want to filter the amount of rows being copied to the target table.
--It is handy to use a resource on the target connection to define how to filter the source table.
--Dynamic Substitution can be used to
--A) sub in a list of values Ex: 'London','Dublin','San Francisco'
--   Just place the substitution within ({})
--B) iterate over the query n times using a differnt value
--  Just place the substitution within {} with no round brakets
--  Note that column names can be used as bind variables elsewhere in the source query

--Example using dynamic substitution to create a list.
--{SELECT 'London' FROM DUAL} is run first on the default connection, the list is substituted into the source query
--SELECT * FROM customers WHERE city IN ('London')
BRIDGE NEWcustomers5 AS accessNorthwind(SELECT * FROM customers WHERE city IN ({SELECT 'London' FROM DUAL}));

--Example using dynamic substitution to iterate the query n times
--  {select 'Categories' SQLDEVTABLENAME FROM DUAL UNION select 'Customers' SQLDEVTABLENAME FROM DUAL UNION select 'Employees' SQLDEVTABLENAME FROM DUAL} is run first on the default connection
-- The following queries are created
--  select count(*) AS numrows, 'Categories' AS name FROM Categories
--  select count(*) AS numrows, 'Customers' AS name FROM Customers
--  select count(*) AS numrows, 'Employeees' AS name FROM Employeees
-- The rows from each query is inserted into the target table NEWLineSizes

BRIDGE NEWLineSizes AS accessNorthwind(select count(*) AS numrows, ':SQLDEVTABLENAME' AS name FROM
                                 {select 'Categories' SQLDEVTABLENAME FROM DUAL UNION select 'Customers' SQLDEVTABLENAME FROM DUAL UNION select 'Employees' SQLDEVTABLENAME FROM DUAL})APPEND;

--Example of defining two target tables using a comma to seperate the two distinct tables.
BRIDGE NEWcustomers6 AS accessNorthwind(SELECT * FROM customers),
  NEWcustomers7 AS accessNorthwind(SELECT TOP 5 * FROM customers);

--Example of defining two target tables using two seperate connection.
--NEWcustomers8 uses an access connection, NEWemployees8 uses a SQL Server connection
BRIDGE NEWcustomers8 AS accessNorthwind(SELECT * FROM customers),
  NEWemployees8 AS sqlserverNorthwind(SELECT * FROM employees);

--Example of creating the target table on a different connection from the default WorkSheet connection  
BRIDGE oracleTest(NEWcustomer9) AS accessNorthwind(SELECT * FROM customers);

--Example of creating on another target connection using the default connection to define the source
BRIDGE oracleTest(Newcustomer10) AS (SELECT * FROM NEWcustomers1);

--Example of using an Oracle connection as the source connection
BRIDGE NewCustomer11 AS oracleTest(SELECT * FROM NewCustomer10);

--********BRIDGE QUERIES**********
--BRIDGE can be used to peform cross connection queries. The user does not need to know that tables are created and populated on the target as they are deleted automatically after the query has run
--This show how to run an Oracle query against a MS Access table
BRIDGE TEMPcustomers AS accessNorthwind(SELECT * FROM customers)
SELECT * FROM TEMPcustomers WHERE rownum <=10;

--This shows how you can join a MS Access table and an Oracle table together in one query
BRIDGE TEMPcustomers AS accessNorthwind(SELECT * FROM customers)
SELECT * FROM TEMPcustomers,all_users;

--This shows how to query a MS Access database, a SQL Server database and an Oracle database all within the one query.
--To do this in the Oracle database you would have to setup mutiple DATABASE LINKS which can be difficult to do.
BRIDGE TEMPcustomers AS accessNorthwind(SELECT * FROM customers),
  TEMPemployees AS sqlserverNorthwind(SELECT * FROM employees),
  TEMPCustomersO  AS oracleTest(SELECT * FROM NewCustomer10)
SELECT  * FROM TEMPcustomers, TEMPemployees, TEMPCustomersO;  

-- MIGRATION EXAMPLES
--RUN STATEMENT ON MIGRATION REPOSITORY CONNECTION
-- This example is used in the migration reports to list the status of migrated objects.
-- It queries the target Oracle database(connection) for the status of objects found in the migration repository.
-- Both Oracle target database and Oracle migration repostitory can be on seperate Oracle databases
BRIDGE all_objects$targetdb AS &&SQLDEVPREF_TARGETCONN(SELECT status,object_name,owner FROM all_objects WHERE UPPER(owner) IN ({SELECT DISTINCT UPPER(schemaname) FROM  mgv_all_schema_details}))
SELECT r.desc_object_name,
  d.schemaname,
  'SQLDEV:LINK:&&SQLDEVPREF_TARGETCONN:null:'||UPPER(d.schemaname)||':'||r.DESC_OBJECT_NAME||':'||UPPER(d.objname)||':oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink' ObjectName,
  NVL2(a.status,a.status,  'Missing') status
FROM mgv_all_details d LEFT OUTER JOIN all_objects$targetdb a
                  ON (UPPER(d.schemaname) = UPPER(a.owner)
                  AND UPPER(d.objname) = UPPER(a.object_name)) , md_registry r
WHERE d.capturedorconverted='CONVERTED'
AND d.objtype in('MD_TABLES','MD_VIEWS','MD_INDEXES','MD_STORED_PROGRAMS','MD_TRIGGERS')
AND r.object_name = d.objtype
ORDER BY schemaName ASC, objtype DESC,objname ASC;

Quick Export Query Results

2009-10-23T03:12:00.000-07:00

When you run a query in the SQL Worksheet using "Run Statement" a Data Grid is used to display the results.

If you then want to export these results youll find that the query is run again.
This can be a pain if it is a long running query.
The following may help.

1) The Data Grid is only populated with 50 rows (by default) at a time. This is for performance reasons, but if you want to use CTRL C, to copy the results it will only copy the results which are currently present.

So click on the data grid and do
CTRL END
This will populate the data grid with the entire result set. When you perform a select all (CTRL A) and copy (CTRL C) you will get the entire result set, but a TAB is used as the delimiter which may not be good enough.

2) Create a VIEW from the query and export from the VIEW
CREATE OR REPLACE quickExportView AS

Then in the navigator right click on the view and perform Export Data.
In this way you dont have to run the query twice.

Things to Note
1) When using the export, it will always wrap your original query as a subquery.
SELECT * FROM ()

It does this so it can perform column and row filtering.

2) For a future release export will be based on the data grid so that the query does not need to be rerun

3) Use "Run Statement" (F9) only for queries. It does support DDL and DML, but these are best executed using "Run Script"(F5)

SQL Developers Directors Direction! OOW 2009

2009-10-19T08:52:00.001-07:00

Resizing dockable windows in SQL Developer 2.1

2009-10-02T13:50:00.001-07:00

Antlr3 Code Too Big

2009-07-01T05:27:00.000-07:00

Antlr3 can generate very large java files for complex grammars.
Javac can have a problem compiling them due to the size limit of static initializers in a class file.

An error like the following


: code too large
> > [12:56:12] public static final String[] tokenNames = new String[] {
> > [12:56:12] ^
> > [12:56:12] 1 error

My solution was to post process the generated Java files.
Moving a large section of static final initializers to a seperate interface(s).
Then reference (implement) the interface in the original Java file.

package oracle.dtools.ant.antlr3;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;

import org.apache.tools.ant.Task;

public class Antlr3PostProcess extends Task {
    private File oldTarget = null;
    private String pkg = null;
    
    private File newTarget= null;
    private File currentInterface = null;
    private int interfaceIndex = 0;
    private int lineIndex = 0;
    private final int MAXLINEINDEX = 1000;
    private String name = null;
    
    public Antlr3PostProcess(){
    }

    public String getPkg() {
        return pkg;
    }

    public void setPkg(String pkg) {
        this.pkg = pkg;
    }
    
    public File getTarget() {
        return oldTarget;
    }
    
    public void setOldTarget(File oldTarget) {
        name = oldTarget.getName().substring(0,oldTarget.getName().indexOf('.'));
        this.oldTarget = oldTarget;
        newTarget = new File(oldTarget.getParentFile(),name+"_TMPANTLR3POST.java");
    }

    public void execute() {
        try{
         boolean createFile =  newTarget.createNewFile();
        moveBitSetConstantsToInterface();
        //delete the old target and rename the new target
        oldTarget.delete();
        newTarget.renameTo(oldTarget);
        } catch (Exception e){
           
        }
    }

    private void moveBitSetConstantsToInterface() throws IOException {
      //open the target file
        BufferedReader reader = new BufferedReader(new FileReader(oldTarget));
        BufferedWriter writerInterface = getNewInterfaceWriter();
        BufferedWriter writerNewTarget = new BufferedWriter(new FileWriter(newTarget));

        //... Loop as long as there are input lines.
        String line = null;
        while ((line=reader.readLine()) != null) {        
            if(lineIndex > MAXLINEINDEX) {
                lineIndex =0;
                closeInterface(writerInterface)  ;
                writerInterface  = getNewInterfaceWriter();
            }
            if(line.startsWith("    public static final BitSet FOLLOW_")){
                lineIndex++;
                writerInterface.write(line); 
                writerInterface.newLine();   // Write system dependent end of line.
            } else if (line.startsWith("public class "+name+" extends Parser")) {
                writerNewTarget.write("public class "+name+" extends Parser implements "+getInterfaceName() +" {"); 
                writerNewTarget.newLine();   // Write system dependent end of line.
            }
            else {
                writerNewTarget.write(line); 
                writerNewTarget.newLine();   // Write system dependent end of line.
            }
            
        }
        closeInterface(writerInterface);
        //... Close reader and writer.
        reader.close();  // Close to unlock.
        //writerInterface.close();  // Close to unlock and flush to disk.
        writerNewTarget.close();  // Close to unlock and flush to disk.
        createInterfaceGroup();
    }


    private void createInterfaceGroup() throws IOException {
        currentInterface = new File(oldTarget.getParentFile(),getInterfaceName()+".java");
        boolean createFile =currentInterface.createNewFile(); 
        BufferedWriter writerInterface =  new BufferedWriter(new FileWriter(currentInterface));
        writerInterface.write("package "+pkg+";");
        writerInterface.newLine();
        writerInterface.write("import org.antlr.runtime.BitSet;");
        writerInterface.newLine();
        writerInterface.write("public interface "+getInterfaceName()+" extends ");
        writerInterface.write(getInterfaceName()+"1");
        for(int i=2;i<=interfaceIndex;i++){
            writerInterface.write(","+getInterfaceName()+i);
        }
        writerInterface.write(" {");
        writerInterface.newLine(); 
        writerInterface.write("}");
        writerInterface.close();
        
    }

    private void closeInterface(BufferedWriter writerInterface) throws IOException {
        writerInterface.newLine();
        writerInterface.write("}");  
        writerInterface.close();
    }

    private BufferedWriter getNewInterfaceWriter() throws IOException {
        BufferedWriter writerInterface =  new BufferedWriter(new FileWriter(getNextInterfaceFile()));
        createInterfaceHeading(writerInterface,currentInterface.getName().substring(0,currentInterface.getName().indexOf('.')),pkg);
        return writerInterface;
     
    }

    private void createInterfaceHeading(BufferedWriter writerInterface,String interfaceName, String interfacePkg) throws IOException {
        writerInterface.write("package "+interfacePkg+";");
        writerInterface.newLine();
        writerInterface.write("import org.antlr.runtime.BitSet;");
        writerInterface.newLine();
        writerInterface.write("public interface "+interfaceName+" {");
        writerInterface.newLine();
    }

    private File getNextInterfaceFile() throws IOException {
        interfaceIndex++;
        currentInterface = new File(oldTarget.getParentFile(),getInterfaceName()+interfaceIndex+".java");
        boolean createFile =currentInterface.createNewFile(); 
        return currentInterface;
    }

    private String getInterfaceName() {
        return name+"BitSet";
    }
}

Define the Ant Task

<taskdef name="Antlr3PostProcess" classname="oracle.dtools.ant.antlr3.Antlr3PostProcess" classpath="Your Classpath Here">
            <classpath><path refid="antlr.3" /><path refid="string.template" /></classpath>
</taskdef>

Reference the task after the Antlr has generated the Java files, but before javac.

<replace file="${antlr3.output}/Db2Parser.java" token="public static final String[] tokenNames" value="public final String[] tokenNames"/>
<replace file="${antlr3.output}/Db2Parser.java" token="Db2Parser.tokenNames" value="tokenNames"/>
<Antlr3PostProcess oldTarget="${antlr3.output}/Db2Parser.java" pkg="oracle.dbtools.migration.parser.grammar.antlr3">
</Antlr3PostProcess>

Antlr 3 Tips

2009-04-15T14:30:00.000-07:00

AntlrWorks
When dealing with large grammars start AntlrWorks with more memory otherwise it can hang.
java -Xmx1g -jar antlrworks-1.2.3.jar

Antlr 3 Parser
Some Antlr rule names are not valid in Java and will cause a compilation issue.
For example: byte: ... ;

Line Count CLOB or VARCHAR2

2009-03-16T07:23:00.000-07:00

I must be missing something, there has to be an easier way to count the number of lines in a VARCHAR2 or CLOB.
For the moment this does the trick ,but Id appreciate a heads up for a better solution.


length(regexp_replace(regexp_replace(TEXTVALUE,'^.*$','1',1,0,'m'),'\s',''))

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions130.htm#SQLRF06302

The inner regexp_replace matches each line = '^.*$'
Replace each line with the '1' character.
Beginning character = 1
First occurance = 0
Tell oracle its a multiline string = 'm'

The outer regexp_replace removes the new lines left over.
Counting the remaining characters gives you the line count.

Ill have to look into a better solution.
>EDIT
Heres a much much more performant solution


LENGTH(TEXTVALUE) -  LENGTH(replace(TEXTVALUE,chr(10))))

replacing all CHR(10) (linefeed char) with nothing. Then compare with original length.

A query which took me 40 minutes with the first query, now takes under a second.
much faster than Oracle 11g, regexp_count as well.

SQL Server 2005 CROSS APPLY conversion

2009-02-02T04:45:00.000-08:00

SQL Server 2005 provides a CROSS APPLY clause which in Oracle terms allows a correlated inline view or table function.

Heres some SQL Server CROSS APPLY background.
http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx

There are a couple of ways to convert this based on what exactly is trying to be done.
But here are a few methods.

There are 2 main uses of CROSS APPLY
1) CROSS APPLY with a table function
2) CROSS APPLY with a subquery (inline view in Oracleze)

1) CROSS APPLY with a table function
Jeff Smith put together this neat example of using a table function with CROSS APPLY
http://www.sqlteam.com/article/returning-complex-data-from-user-defined-functions-with-cross-apply

I expanded this example to try out a few different scenarios


drop table emails
go

create table emails (ID int primary key, EmailAddress varchar(100))
go
--added 2 rows which cant be processed by EmailParse
insert into emails
select 1,'test1@gmail.com' union all
select 2,'test2@gmail.com' union all
select 3,'test3@gmail.com' union all
select 4,'test' union all
select 5, null 
go

drop function EmailParse1
go

--return a table with (null,null) if you cant process
create function EmailParse1 (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
 declare @i int

 select @i = charindex('@', @email,1);

 if (@i > 1)
  insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
 else
  insert into @t values (null,null)

 return 
end
go

drop function EmailParse2
go

--return nothing if you cant process
create function EmailParse2 (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
 declare @i int

 select @i = charindex('@', @email,1);

 if (@i > 1)
  insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
   
    --return nothing if not valid

 return 
end
go

drop function EmailParse3
go

--return multiple rows if you can process and nothing if you cant
create function EmailParse3 (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
 declare @i int

 select @i = charindex('@', @email,1);

 if (@i > 1)
    begin
  insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
    insert into @t values (left(@email,@i-1), substring(@email,@i+1,20)) --replicatge multiple rows
    insert into @t values (left(@email,@i-1), substring(@email,@i+1,20)) --replicatge multiple rows
    end
        
 return 
end
go

drop function EmailParse4
go

--return multiple rows if you can process and a table(null,null) if you cant
create function EmailParse4 (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
 declare @i int

 select @i = charindex('@', @email,1);

 if (@i > 1)
    begin
  insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
        insert into @t values (left(@email,@i-1), substring(@email,@i+1,20)) --replicatge multiple rows
        insert into @t values (left(@email,@i-1), substring(@email,@i+1,20)) --replicatge multiple rows
    end
    else 
        insert into @t values (null,null)

 return 
end
go
PRINT 'SELECT 1'
go
select * from emails cross apply  EmailParse1(emails.EmailAddress) s
go

PRINT 'SELECT 2'
go
select * from emails outer apply EmailParse1(emails.EmailAddress) s
go

PRINT 'SELECT 3'
go
select * from emails cross apply  EmailParse2(emails.EmailAddress) s
go

PRINT 'SELECT 4'
go
select * from emails outer apply EmailParse2(emails.EmailAddress) s
go

PRINT 'SELECT 5'
go
select * from emails cross apply EmailParse3(emails.EmailAddress) s
go

PRINT 'SELECT 6'
go
select * from emails outer apply  EmailParse3(emails.EmailAddress) s
go
PRINT 'SELECT 7'
go
select * from emails cross apply EmailParse4(emails.EmailAddress) s
go

PRINT 'SELECT 8'
go
select * from emails outer apply EmailParse4(emails.EmailAddress) s
go

Generic Oracle PL/SQL Solution


DROP TABLE emails;
CREATE TABLE emails(id int, emailaddress varchar2(100));
INSERT INTO emails VALUES(1,'test1@gmail.com');
INSERT INTO emails VALUES(2,'test2@gmail.com');
INSERT INTO emails VALUES(3,'test3@gmail.com');
INSERT INTO emails VALUES(4,'test');
INSERT INTO emails VALUES(5,null);

DROP PACKAGE Email_pkg;
/
DROP TYPE emailAddressObject;
/
CREATE OR REPLACE TYPE emailAddressObject AS OBJECT
(UserName VARCHAR2(20) , Domain VARCHAR2(20),status CHAR(1));
/
CREATE OR REPLACE PACKAGE Email_pkg
AS
TYPE emailAddressTable IS TABLE OF emailAddressObject;
END;
/
CREATE OR REPLACE FUNCTION EmailParse1(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
  v_i := INSTR(v_email, '@', 1);
  IF ( v_i > 1 ) THEN
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P' ));
  ELSE
    PIPE ROW(emailAddressObject(null,null,'P'));  -- This needs to pass as this null table was present in the original
  END IF;
  RETURN;
END;
/



CREATE OR REPLACE FUNCTION EmailParse2(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
  v_i := INSTR(v_email, '@', 1);
  IF ( v_i > 1 ) THEN
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P' ));
 ELSE -- ADDED IN TO EMULATE
    PIPE ROW(emailAddressObject(null,null,null)); 
  END IF;

  RETURN;
END;
/


CREATE OR REPLACE FUNCTION EmailParse3(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
  v_i := INSTR(v_email, '@', 1);
  IF ( v_i > 1 ) THEN
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P'));
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P')); --multirow
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P')); --multirow
 ELSE -- ADDED IN TO EMULATE
    PIPE ROW(emailAddressObject(null,null,null)); 
  END IF;
  RETURN;
END;
/


CREATE OR REPLACE FUNCTION EmailParse4(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
  v_i := INSTR(v_email, '@', 1);
  IF ( v_i > 1 ) THEN
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P' ));
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P' )); --multirow
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20),'P' )); --multirow
  ELSE
    PIPE ROW(emailAddressObject(null,null,'P'));   -- This needs to pass as this null table was present in the original
  END IF;
  RETURN;
END;
/


PROMPT SELECT 1
--SELECT * FROM emails CROSS JOIN TABLE(EmailParse1(emails.emailaddress));
SELECT * FROM emails INNER JOIN TABLE(EmailParse1(emails.emailaddress))s ON s.status='P';

PROMPT SELECT 2
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse1(emails.emailaddress))s ON 1=1; 

PROMPT SELECT 3
SELECT * FROM emails INNER JOIN TABLE(EmailParse2(emails.emailaddress))s ON s.status ='P';
PROMPT SELECT 4
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse2(emails.emailaddress)) ON 1=1; 


PROMPT SELECT 5
SELECT * FROM emails INNER JOIN TABLE(EmailParse3(emails.emailaddress))s ON s.status = 'P';
PROMPT SELECT 6
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse3(emails.emailaddress)) ON 1=1; 

PROMPT SELECT 7
SELECT * FROM emails INNER JOIN TABLE(EmailParse4(emails.emailaddress))s ON s.status = 'P';
PROMPT SELECT 8
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse4(emails.emailaddress)) ON 1=1;

Explanation:
The EmailParse functions get converted to Oracle pipelined table function.

If the SQL Server table function always returns a table under all circumstances
insert into @t values (null,null)
--see example EmailParse1, EmailParse4
the conversion to Oracle is relatively straight forward.

You can convert the
SQL Server CROSS APPLY to Oracle CROSS JOIN
And
SQL Server OUTER APPLY to Oracle LEFT OUTER JOIN ... ON 1=1

Heres the example just converting EmailParse1, EmailParse4 as they always return a table.


DROP TABLE emails;
CREATE TABLE emails(id int, emailaddress varchar2(100));
INSERT INTO emails VALUES(1,'test1@gmail.com');
INSERT INTO emails VALUES(2,'test2@gmail.com');
INSERT INTO emails VALUES(3,'test3@gmail.com');
INSERT INTO emails VALUES(4,'test');
INSERT INTO emails VALUES(5,null);

DROP PACKAGE Email_pkg;
/
DROP TYPE emailAddressObject;
/
CREATE OR REPLACE TYPE emailAddressObject AS OBJECT
(UserName VARCHAR2(20) , Domain VARCHAR2(20));
/
CREATE OR REPLACE PACKAGE Email_pkg
AS
TYPE emailAddressTable IS TABLE OF emailAddressObject;
END;
/
CREATE OR REPLACE FUNCTION EmailParse1(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
  v_i := INSTR(v_email, '@', 1);
  IF ( v_i > 1 ) THEN
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20) ));
  ELSE
    PIPE ROW(emailAddressObject(null,null));  -- This needs to pass as this null table was present in the original
  END IF;
  RETURN;
END;
/

CREATE OR REPLACE FUNCTION EmailParse4(v_email IN VARCHAR2)
RETURN Email_pkg.emailAddressTable PIPELINED
AS
v_i NUMBER(10,0);
BEGIN
  v_i := INSTR(v_email, '@', 1);
  IF ( v_i > 1 ) THEN
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20) ));
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20) )); --multirow
    PIPE ROW( emailAddressObject(SUBSTR(v_email, 0, v_i - 1), SUBSTR(v_email, v_i + 1, 20) )); --multirow
  ELSE
    PIPE ROW(emailAddressObject(null,null));   -- This needs to pass as this null table was present in the original
  END IF;
  RETURN;
END;
/


PROMPT SELECT 1
--SELECT * FROM emails CROSS JOIN TABLE(EmailParse1(emails.emailaddress));
SELECT * FROM emails CROSS JOIN TABLE(EmailParse1(emails.emailaddress))s ;

PROMPT SELECT 2
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse1(emails.emailaddress))s ON 1=1; 


PROMPT SELECT 7
SELECT * FROM emails CROSS JOIN TABLE(EmailParse4(emails.emailaddress))s;
PROMPT SELECT 8
SELECT * FROM emails LEFT OUTER JOIN TABLE(EmailParse4(emails.emailaddress)) ON 1=1;

If the SQL Server table function doesn't always return a function
For example in EmailParse2, EmailsParse3

It more difficult to convert to Oracle. The reason being there is a difference as to how SQL Server and Oracle interpret Table Functions when nothing is returned.
The conversion require is more complex, but it will work in both scenarios so is a generic solution. If performing the conversion by hand then each query maybe modified to suit its own circumstances.

In the above (generic) example the SQL Server EmailParse2 and EmailParse3 return nothing if it cant parse the email address. The last two values in emails cant be parsed ('dermot' and null).

In SQL Server if nothing is returned by the table function then
A) The Cross Apply happens and those null rows are restricted
B) The Outer Apply happens and all the left hand side table rows are used.

In Oracle if nothing is returned by the table function then the CAST to TABLE doesnt happen. This is the difference between Oracle and SQL Server.
A) The Oracle CROSS JOIN happens and those null rows are restricted. Same as SQL Server
B) The Oracle LEFT OUTER JOIN happens but as there is no right hand side table, the join fails. Restricting those rows in the left hand side table. This is different from SQL Server, which returns all the rows in the left hand side table.

So you may think that the solution is to make sure the Oracle always returns a table, even emailAddressObject(null,null).
Now the Oracle LEFT OUTER JOIN happens identically to SQL Server as there is a table to join against. But the Oracle CROSS JOIN returns all the rows, it doesn't restrict null values as it thinks it has a result (an empty table) to join with.

So we need to limit the Oracle CROSS JOIN to only those rows which are successfully parsed. This could entail adding another field to the emailAddressObject with a status that can be checked.
And changing the CROSS JOIN to
INNER JOIN .. ON emailAddressObject.status ='P' -- 'P' for passed

The conversion steps are
1) Convert The SQL Server Table Function to an Oracle Pipelined Table function
2) Create an Oracle Oracle TYPE


CREATE OR REPLACE TYPE emailAddressObject AS OBJECT(UserName VARCHAR2(20) , Domain VARCHAR2(20),status CHAR(1));

Notice the status field is new. This will appear in a select list if using SELECT *
3)Make sure all Oracle Table Function return a table in all cases.
If nothing was returned before pass back
PIPE ROW(emailAddressObject(null,null,null));
If a null table was being passed back in SQL Server
(insert into @t values (null,null))
return the following in Oracle.
PIPE ROW(emailAddressObject(null,null,'P'));
4)SQL Server CROSS APPLY converts to Oracle INNER JOIN .... ON s.status='P'
5)SQL Server OUTER APPLY converts to Oracle LEFT OUTER JOIN .... ON 1 = 1

CROSS APPLY with a subquery (inline view in Oracleze)
Oracle inline views cannot reference outer queries, they are effectively standalone and are instantiated inmemory before the outer query is processed.

Heres a T-SQL Example that uses an inline view, based on Jeff Smith's example.


drop table customer
go
create table customer (emailid int, city varchar(100), country varchar(100))
go
insert into customer
select 1,'new york','usa' union all
select 2,'dublin','ireland' union all
select 3,'hong kong', 'china'
go
select emails.ID, s.city, s.country
from emails
CROSS APPLY
(select city,country from customer where emailid =emails.ID ) s;
go

select emails.ID, s.city, s.country
from emails
OUTER APPLY
(select city,country from customer where emailid =emails.ID ) s;
go

Oracle wont allow the inline view ("s") to reference the emails table in the outer query. So there are a few ways to convert this.

Oracle PL/SQL


drop table customer;
/
create table customer (emailid int, city varchar2(100), country varchar2(100));
/
insert into customer
select 1,'new york','usa' from dual union all
select 2,'dublin','ireland' from dual union all
select 3,'hong kong', 'china' from dual ;

/
select emails.ID, s.city, s.country
from emails
INNER JOIN (select city,country,emailid from customer ) s
ON s.emailid =emails.ID;
/

select emails.ID, s.city, s.country
from emails
LEFT OUTER JOIN
(select city,country,emailid from customer) s
ON  s.emailid =emails.ID;
/

The T-SQL CROSS APPLY is converted to PL/SQL using an INNER JOIN with the correlation is pulled out as the ON condition.

The T-SQL OUTER APPLY is converted to PL/SQL using a LEFT OUTER JOIN, again the correlation is pulled out as the ON condition.

Note that the inline view select list was modified to include any references in the ON clause.

Oracle Open World Migration Sessions

2008-08-29T03:07:00.000-07:00

Oracle Open World 2008 is kicking off on the 21st September.
www.oracle.com/openworld

There are some migrations sessions scheduled which can be signed up for now.

S298684 : Hands-on Lab: Migrate Your Third-Party Database to Oracle Database 11g Today

S298684 : Consolidate Your Desktop Databases to Oracle Database 11g

Hope to see you there
Dermot.

Offline Data Move DATEs

2008-06-12T04:40:00.000-07:00

During the offline data move, scripts are created to use BCP to dump out your data in SQL Server or Sybase to .dat files. Other scripts are created to use Oracle SQL*Loader to load the .dat files into the Oracle tables.

Your SQL Server/ Sybase database will dump out the DATETIME and SMALLDATETIME values in formats which may not be recognized by Oracle. So you have to specify what the format mask is.

SQL Server / Sybase DataTypes
DATETIME has millisecond precision.
SMALLDATETIME has only second precision.

Oracle DataTypes
DATE has only second precision
TIMESTAMP has millisecond precision

You can specify their format mask in the migration preferences.

The preferences work like this

The Timestamp Mask is applied to the DATETIME Sybase or SQL Server values
The Date Mask is applied to the SMALLDATIME Sybase or SQL Server values.

So now the full date value is recognized by SQL*Loader.

The best bit is that even if you map Sybases/ SQL Servers DATETIME to Oracles DATE (which is the default mapping). The TO_TIMESTAMP that happends in SQL*Loader will be implicitly converted to a DATE when inserted, and the milliseconds automatically truncated.

Here are some Defaults which you should specify in the Migration preferences
SQL Server
Timestamp Mask Preference = DATETIME in SQL Server = 'yyyy-mm-dd HH24:mi:ss.ff3'
Date Mask Preference= SMALLDATETIME in SQL Server = 'yyyy-mm-dd HH24:mi:ss'

Sybase
Timestamp Mask Preference = DATETIME in Sybase = 'Mon dd yyyy hh:mi:ss:ff3AM'
Date Mask Preference = SMALLDATETIME in Sybase = 'Mon dd yyyy hh:mi:ss'

You should check your .dat files to make sure the format is correct. You can regenerate the scipts after you change the preference. The formats are applied in the generated Oracle/*.ctl files.

SQL Developer Migration Workbench 1.5.1 for Sybase / SQL Server Quick Guide

2008-06-11T09:41:00.000-07:00

This Quick Guide will run through the main steps you should take to perform a Sybase or SQL Server migration to Oracle.

Installation

Download the latest version of SQL Developer ( using 1.5.1 today), to your Development PC which can access your Oracle database and your SQL Server/ Sybase database. SQL Developer ships with the migration extensions built in.
Download and Install Oracle XE if you dont have access to an exiting Oracle database.
UnZip SQL Developer into its own directory (for example "SQLDev")
Don't use an existing ORACLE_HOME or ORACLE directory
Run sqldeveloper
Setup the jtds driver (open source JDBC driver for Sybase and SQL Server) with SQL Developer
JTDS can be got using the Check For Updates feature. Tools > Prefereneces > Extensions > Check For Updates. Walk through the wizard and choose any new migration updates and the JTDS driver.

Setup Connections & Migration Repository

Create a connection to Sybase or SQL Server
Create a connection to your Oracle database
Create a new schema for the migration repository (MIGREP)
GRANT DBA TO MIGREP IDENTIFIED BY MIGREP
Create a connection to your migration repository schema (MIGREP)
Associate the migration repository with this schema
Right Click the migration repository connection (MIGREP), Migration Repository > Associate Migration Repository

Setup the Migration Preferences

Setup the "Is Quoted Identifier On" preference
Setup the DATETIME and SMALLDATETIME format masks if required.

Capture your Database
Creates a "snapshot" of the Sybase / SQL Server database in our migration repository.

Capture the Sybase / SQL Server database (dont use Quick Migrate!) .
Note there is an offline capture available as well if you cannot directly connect to your Sybase database
Save the Captured Dialog Text (Handy to have a list of captured number or objects)
Identify any Capture Issues and Resolve (there shouldn't be any!)
Browse the Captured Model

Convert your Captured Database to Oracle

Convert the Captured Model to the Oracle Model
Choose the datatype mapping (best to use the default mapping)
Save the Converted Dialog Text (Handy to have a list of converted number of objects)
Save the Migration Log
Identify any Convert Issues and Resolve

Generate The Oracle Database (Schema and Objects)

"Generate" the Migration scripts
Run the Scripts using the SQL Worksheet to create your Oracle database.
Save the Result of the Generation Script

Project Plan
Errm. It looks like Im doing the plan in the middle of the project!
But in reality the above steps can be completed for one database within hours, or a day or two. You havent converted any data, we have just been working on the database definition (tables, views, procedures,...). You may have lots of issues remaining, but you now know what has been automated for you and what manual work remains. This is the time to step back and think of the entire project and plan.

How are you going to validate the Oracle database?
How are you going to rectify conversion issues or objects which didnt convert?
How are you going to switch from Sybase / SQL Server to Oracle. Will there be downtime for users?
What apps are affected by the switch? What work is required to move them to Oracle?

Enhance your Oracle Database

Identify and Generation Issues and Resolve them in your Oracle database. Not in the converted model as there are better tools to work with real Oracle objects.
Verify that all the objects in your Sybase / SQL Server database are present and valid in your Oracle database.

Move the Data from Sybase / SQL Server to Oracle

Data Move for small dataset (<100mb),>
Data Move for large dataset (>100mb) , use the offline datamove

Test & Tune
The buck stops with you to validate that the migration is complete and succefull. Note that this part can take longer than the migration itself. How do you test your Sybase / SQL Server database currently? How will you verifiy that the Oracle database works as expected?

Deploy

Will the database definition have changed since you started the migration?
Will the data have changed? How to move it to Oracle. Using a delta or perform an entire data move again.

Your migration approach will differ, depending on the complexity of the database, the applications that run on top, your company standards, .... . Hopefully the above steps will help.

SQL Developer Migration Workbench 1.5.1

2008-06-11T04:44:00.000-07:00

SQL Developer 1.5 released 8 weeks ago with the new Sybase capability, aiding migrations from Sybase 12 and Sybase 15 to Oracle.

It worked pretty good, and we got some great feedback from customer using it in the real world on their migration projects. All the enhancements and now provided in SQL Developer 1.5.1 released on Monday. Thanks to all.

Download it here
http://www.oracle.com/technology/products/database/sql_developer/index.html

Here are the main improvements

Improved Offline Data Move Scripts including support for both DATETIME And SMALLDATETIME formats
Variable Name Collision Management
Temporary Table Name Collision Management
Sybase Identity Columns Converted Correctly
Emulation Packages Improved

Many of the Issues noted by some customers regarding SQL Developer such as ORACLE_HOME issues, Connections dissapearing, NLS settings, .. are resolved.

List of Migration Bug fixes for 1.5.1

 Num      Subject
7137280  SYBASE 12 LINUX OFFLINE CAPTURE SCRIPTS NOT AVAILABLE
7129820  CANNOT DROP CONVERTED INDEXES
7113461  PROCEDURE VARIABLES NAMES ARE NOT COLLISIONED CORRECTLY
7113269  EXECUTE PROCEDURE PARAMETER NAMING INCORRECT
7046907  OFFLINE BLOB DATA MOVE "CREATE PROCEDURE" PREFERENCE SHOULD BE ON BY DEFAULT
7046890  TEMPORARY TABLE NAMES NOT COLLISIONED
7046870  CONVERTING IDENTIFIERS > 100 CHARS IN LENGHT CAUSED THE CONVERT PHASE TO FAIL
7046785  NOEXPAND TABLE HINT IS NOT RECOGNIZED
7022092  SYBASE: IDENTITY COLUMNS NOT CONVERTED TO SEQUENCE AND TRIGGER
7005215  OTNFORUMS : OBJECT/DATABASE/MODEL WORDS USED INCONSISTENTLY
6989645  IMPROVE TEMPORARY TABLE DEFINITION CONVERSION
6989465  CURSOR STATUS SHOULD BE EMULATED @@SQLSTATUS : CURRENT LIMITATION
6989025  SYBASE DATALENGTH FUNCTION NOT TRANSLATED< STILL LIMITATION
6979464  TRIGGER INSERTED REFERENCE NOT TRANSLATED CORRECTLY IN MERGE STMT
6976180  INVALID SYNTAX CAUSES THE TRANSLATOR TO LOOP/ HANG SQL DEV
6970168  EMPTY STRING AND NULL COMPARISON NOT TRANSLATED CORRECTLY
6953135  CONVERT FUNCTION NOT CORRECTLY EMULATED
6944669  DOUBLE QUOTE LITERALS NOT TRANSLATED CORRECTLY
6927102  COLUMN NAME DATA CAUSED SYNTAX ERROR
6853149  NPE DISPLAYED WHEN CAPTURE INTO EXISTING MODEL CLICKED FOR THIRD PARTY DATABASES
6853074  IN CAPTURED MODEL SELECT ALL TABLES RIGHT CLICK,RENAME & REMOVE FOLDER NOT WORKI
5903238  UPGRADE EMULATION PACKAGE
4529352  OMWB USING WRONG TEMPORARY TABLES INSIDE THE STORED PROCEDURE
3545475  OMWB FAILS TO TRASLATE " TO ' IN SOME GROUP BY EXPRESSIONS
2694450  T-SQL PARSER FAILS TO PARSE 'AFTER UPDATE' TRIGGER
7038824  NTEXT TO NCLOB ON NON UNICODE DATABASE CORRUPT WITH CHINESE CHARACTER
7013620  QUICK MIGRATE: PROVIDE INCORRECT PASSWORD, NEVER PROMPTS, JUST FAILS
6994152  ORACLE_HOME ENVIRONMENT VARIABLE SOMETIMES HAS AN ADVERSE AFFECT ON MIGRATIONS
6356767  EXCEPTION OCCURING ON CONNECTING TO A SQL SERVER CONNECTION
2694526  OMWB DOES NOT STORE DEPENDENCY INFORMATION FOR FUNCTIONS

Oflline Data Move with Oracle XE

2008-06-05T06:36:00.000-07:00

Noticed that sometimes I got an ORA-12519 error when using the oracle_ctl.bat file which calls SQL*Loader to upload each dat file into its Oracle table.

When I ran the script line by line I got no problems. Seemed like SQL*Loader was a little to fast for Oracle XE. Found this on the forums

Intermittent ORA-12519 error on 10g XE
http://forums.oracle.com/forums/thread.jspa?messageID=1252551

After setting
ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE
and restarting the database, my oracle_ctl.bat file ran without issues.

Using Regular Expression To Resolve Multiple Issues

2008-05-21T06:54:00.001-07:00

One way to resolve issues which occur too many times to fix by hand in the generation script is to use a regular expression to search and replace some problematic code.

Shiva Ramakrishnan provided these regular expressions

\(['][A-Za-z_ ]*\)& ----------------à \1'||chr(38)||'


\(['][A-Za-z_ ]*\)& \([A-Za-z_ ]*[']\)--------------------------à \1'||chr(38)||'\2


\(raw(\)\([0-9]*\)\() default 0\) ----------------------------------à \1\2) default '0'


\('12/31/9999'\) -------------------------------------------à to_date(\1,'mm/dd/yyyy')


CAST('9999-12-31' AS DATE) ----------àto_date(‘12/31/9999’,’mm/dd/yyyy’)

First one is replacing ‘&’ with CHR(38). SQLPLUS provides mechanism to overlook this. So this is not a major concern.
The second one is where RAW datatypes are assigned DEFAULT 0. I am reassigning with DEFAULT ‘0’
The third is a case of DATE Fields assigned as is. I think NLS_DATE parameters can be SET to overlook this. I am doing a global replacement.

Hope to have these automatically fixed soon.

Captured And Converted Indexes Report

2008-05-21T06:19:00.001-07:00

Heres a little SQL Developer user defined report to list the captured and converted indexes. You can change the query to list 1 or the other.

Copy the following text and create a file called MigratedIndexes.xml with it.
The In the SQL Developer User Defined Reports , Import the file. When you open the report you should choose the migration repository.

<?xml version="1.0" encoding="UTF-8" ?><displays><display id="0b9c6f78-011a-1000-8001-0aa970396f91" type="" style="Table" enable="true">
 <name><![CDATA[List Captured And Converted Indexes]]></name>
 <description><![CDATA[List Captured And Converted Indexes]]></description>
 <tooltip><![CDATA[List Captured And Converted Indexes]]></tooltip>
 <drillclass><![CDATA[null]]></drillclass>
 <CustomValues>
     <TYPE>horizontal</TYPE>
 </CustomValues>
 <query>
     <sql><![CDATA[SELECT DISTINCT NVL(c.type, 'captured') type, t.project_name, t.catalog_name, t.schema_name, t.table_name, i.index_name, i.index_type
FROM md_indexes i, mgv_all_tables t, md_connections c
WHERE i.table_id_fk = t.table_id AND c.id = t.connection_id
ORDER BY type, t.project_name, t.catalog_name, t.schema_name, t.table_name, i.index_name]]></sql>
 </query>
</display>
</displays>

Parameters Of A Procedure Call

2008-05-20T07:27:00.000-07:00

**Update: This is now fixed in SQL Developer 1.5.1**
A SQL Server and Sybase translation issue.
If you executed a procedure from another procedure, and you specified the executed procedure parameter names, you may have got a doubling up of param names.

T-SQL Example

create procedure procparam1 @param1 int, @param2 int
as
select @param1, @param2
go


create procedure procparam2 @param1 int
as
declare @var1 int
begin
select @var1 = 10
execute procparam1 @param1=@param1,@param2=@var1

execute procparam1 @param1,@var1

execute procparam1 @param1=1,@param2=2

execute procparam1 1,2
end
go

Generated PL/SQL

CREATE OR REPLACE PROCEDURE procparam2
(
v_param1 IN NUMBER DEFAULT NULL ,
cv_1 IN OUT SYS_REFCURSOR,
cv_2 IN OUT SYS_REFCURSOR,
cv_3 IN OUT SYS_REFCURSOR,
cv_4 IN OUT SYS_REFCURSOR
)
AS
v_var1 NUMBER(10,0);
BEGIN

BEGIN
v_var1 := 10;

procparam1(v_param1v_param1 => v_param1,
       v_param2v_param2 => v_var1,
       cv_1 => cv_1);

procparam1(v_param1,
       v_var1,
       cv_1 => cv_2);

procparam1(v_param1v_param1 => 1,
       v_param2v_param2 => 2,
       cv_1 => cv_3);

procparam1(1,
       2,
       cv_1 => cv_4);

END;
END;

Note the v_param1v_param1 .

A real fix will be included in SQL Dev 1.5.1, but if you cant wait heres a hack.

Close SQL Developer
Backup your \sqldeveloper\extensions\ oracle.sqldeveloper.migration.translation.sqlserver.jar
Extract \sqldeveloper\extensions\oracle.sqldeveloper.migration.translation.sqlserver.jar:templates\tsql.stg
Including its directory structure to C:\
Edit the "templates\tsql.stg" file
Use the "first" method around the paramName, so only 1 paramName is used

TSQL_executeProcedureParam(paramName, paramValue) ::=<<
<if(paramName)><first(paramName)> =\> <paramValue>
<else><paramValue>
<endif>
>>

Delete the exising templates\tsql.stg from the jar file, and add the modified one back in, including directory structure, so the jar file has a templates\tsql.stg
Restart SQL Developer and convert again.

Sybase Offline Capture Scripts on Windows

2008-05-20T05:07:00.000-07:00

**Update: This is now fixed in SQL Developer 1.5.1**
Sybase Offline Capture Scripts on Windows are incorrect. Some files needs to replaced for them to work, the text of which can be found below.

You should then be able to perform an Offline Capture successfully.

SYBASE 15
SYB15_OFFLINE_CAPTURE.BAT username password databasename servername
For Example

SYB15_OFFLINE_CAPTURE.BAT sa "" pubs2 the4400.ie.oracle.com

SYBASE 12
SYB12_OFFLINE_CAPTURE.BAT username password databasename servername
For Example

SYB12_OFFLINE_CAPTURE.BAT sa "" pubs2 the4401.ie.oracle.com

Some Tips

"" represents an empty string for no password.
Sybase BCP, ISQL should be first on your PATH if you have SQL Server installed as well.
Example: SET PATH=D:\SybaseClient\OCS-15_0\bin;%PATH%
The Servername must be defined in your sql.ini file for BCP to work.

SYBASE 15 (ASE15)
Replace the following files

SYB15_OFFLINE_CAPTURE.BAT
SYB15_BCP_SCRIPT.BAT

SYB15_OFFLINE_CAPTURE.BAT

@echo off

rem ** SET THE VALUE FOR THE OFFLINE_CAPTURE_COLUMN_DELIMITER
rem ** echoed through bcp to COLUMN.TXT
set OFFLINE_CAPTURE_COLUMN_DELIMITER="<EOC>"

rem ** SET THE VALUE FOR THE OFFLINE_CAPTURE_ROW_DELIMITER
rem ** echoed through bcp to ROW.TXT
set OFFLINE_CAPTURE_ROW_DELIMITER="<EOR>"

rem ** SET THE SCRIPT VERSION ENVIRONMENT VARIABLE
set OMWB_SCRIPT_VERSION=15

rem ** SET THE VALUE FOR THE OMWB FILE ENVIRONMENT VARIABLE
set OMWB_SCRIPT_FILE=%3\%3_INFO.TXT

rem ** DISPLAY THE HELP PAGE IF THE USER REQUESTS IT

if "%1"=="-h"   goto help
if "%1"=="help" goto help
if "%1"=="?"    goto help
if "%1"=="-?"   goto help


rem *** DISPLAY THE SCRIPT VERSION IF THE USER REQUESTS IT

if "%1"=="-ver" goto version
if "%1"=="version" goto version

rem *** CHECK THAT THREE PARAMETERS HAVE BEEN ENTERED
rem *** THE PASSWORD CAN BE "", SO WE DON't DO THE SAME CHECK FOR THAT

if "%1"=="" goto input_error
if "%3"=="" goto input_error
if "%4"=="" goto input_error

rem *** START THE EXECUTION OF THE SCRIPT INSTRUCTIONS

goto start

:start

rem ** CREATE THE OUTPUT DIRECTORIES

mkdir master
mkdir %3

rem *** CALL THE BCP SCRIPT TO CREATE THE METADATA FILES

call SYB15_BCP_SCRIPT.BAT %1 %2 %3 %4 %OFFLINE_CAPTURE_COLUMN_DELIMITER% %OFFLINE_CAPTURE_ROW_DELIMITER%

rem *** CHECK THAT ALL OF THE OUTPUT FILES HAVE BEEN CREATED

goto checkoutput


:help
echo -----------------------------------------------------------------------
echo ---------------------  Oracle Migration Workbench ---------------------
echo ---------------------         Release %OMWB_SCRIPT_VERSION%       ---------------------
echo -----------------------------------------------------------------------
echo ---- This script will generate delimited flat files containing     ----
echo ---- schema metadata from the database you wish to migrate. This   ----
echo ---- script will envoke the Bulk Copy Program (BCP) that should be ----
echo ---- part of your Sybase install base.                         ----
echo ---- Please ensure that your path points to the version of BCP     ----
echo ---- that is installed with the Sybase from which you wish     ----
echo ---- to migrate. Your current path setting is listed below:        ----
echo ----
PATH
echo ----
echo ----
echo ---- To run this script, enter the following command at the prompt ----
echo ----
echo ---- OMWB_OFFLINE_CAPTURE login_id password database_name server_name
echo ---- where,
echo ---- dba_login_id is a login id which has been granted db_datareader
echo ---- and view definition on database_name
echo ---- password is the password for the login id
echo ---- database_name is the name of the database you wish to capture
echo ---- server_name is the name of the server on which the database resides
echo ---- For example,
echo ----      OMWB_OFFLINE_CAPTURE sa sapwd employeeDB DEPT1_SERVER
echo ----
echo -----------------------------------------------------------------------
goto exit

:input_error
echo ---- ** Error executing the script
echo ----
echo ---- To run this script, enter the following command at the prompt ----
echo ----
echo ---- OMWB_OFFLINE_CAPTURE login_id password database_name server_name
echo ---- where,
echo ---- login_id is a login id which has been granted db_datareader
echo ---- and view definition on database_name
echo ---- password is the password for the login id
echo ---- database_name is the name of the database you wish to capture
echo ---- server_name is the name of the server on which the database resides
echo ---- For example,
echo ----      OMWB_OFFLINE_CAPTURE sa sapwd employeeDB DEPT1_SERVER
echo ----
echo -----------------------------------------------------------------------
goto exit


:version
echo -----------------------------------------------------------------------
echo ---- This is the Oracle Migration Workbench offline capture script
echo ---- version %OMWB_SCRIPT_VERSION% for Sybase 15
echo -----------------------------------------------------------------------
goto exit

:omwbfile
echo OMWB REPORT FOR %3                            >  %OMWB_SCRIPT_FILE%
echo ____________________________________________  >> %OMWB_SCRIPT_FILE%
echo * SCRIPT EXECUTION DATE AND TIME:             >> %OMWB_SCRIPT_FILE%
date /t                                            >> %OMWB_SCRIPT_FILE%
time /t                                            >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo SYSTEM PROPERTIES                             >> %OMWB_SCRIPT_FILE%
echo _______________________                       >> %OMWB_SCRIPT_FILE%
echo * PLATFORM VERSION:                           >> %OMWB_SCRIPT_FILE%
ver                                                >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo * PLATFORM CODEPAGE:                          >> %OMWB_SCRIPT_FILE%
mode con codepage /sta                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo SOURCE DATABASE SERVER PROPERTIES             >> %OMWB_SCRIPT_FILE%
echo __________________________________            >> %OMWB_SCRIPT_FILE%
echo * BCP VERSION:                                >> %OMWB_SCRIPT_FILE%
bcp -v                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo * DATABSE SERVER COLLATION AND VERSION:       >> %OMWB_SCRIPT_FILE%
isql -U%1 -P%2 -S%4 -i%3  -i properties.sql        >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo DIRECTORY LISTING for %3                      >> %OMWB_SCRIPT_FILE%
echo __________________________________            >> %OMWB_SCRIPT_FILE%
dir %3                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo DIRECTORY LISTING FOR MASTER                  >> %OMWB_SCRIPT_FILE%
echo _______________________________               >> %OMWB_SCRIPT_FILE%
dir master                                         >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo ATTRIBUTES OF SCRIPT:                         >> %OMWB_SCRIPT_FILE%
echo _______________________                       >> %OMWB_SCRIPT_FILE%
attrib SYB15_OFFLINE_CAPTURE.bat                    >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo CONTENTS OF EXECUTED SCRIPT                   >> %OMWB_SCRIPT_FILE%
echo ____________________________________          >> %OMWB_SCRIPT_FILE%
type SYB15_OFFLINE_CAPTURE.BAT                      >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo CONTENTS OF BCP SCRIPT                        >> %OMWB_SCRIPT_FILE%
echo ____________________________________          >> %OMWB_SCRIPT_FILE%
type SYB15_BCP_SCRIPT.BAT                           >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%
echo -                                             >> %OMWB_SCRIPT_FILE%

echo ** END REPORT FOR %3                          >> %OMWB_SCRIPT_FILE%

rem *** DISPLAY THE FINAL INSTRUCTIONS TO THE USER
goto finalinstructions


:checkoutput

rem *** CHECK THAT ALL DATABASE META FILES HAVE BEEN CREATED
if not exist %3\SYB12_SYSUSERS.dat          echo ** ERROR %3\SYB12_SYSUSERS.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.
if not exist %3\SYB12_SYSOBJECTS.dat        echo ** ERROR %3\SYB12_SYSOBJECTS.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.
if not exist %3\SYB12_SYSTYPES.dat            echo ** ERROR %3\SYB12_SYSTYPES.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.
if not exist %3\SYB12_SYSCOLUMNS.dat        echo ** ERROR %3\SYB12_SYSCOLUMNS.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.
if not exist %3\SYB12_SYSCOMMENTS.dat        echo ** ERROR %3\SYB12_SYSCOMMENTS.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.
if not exist master\SYB12_SYSDATABASES.dat  echo ** ERROR master\SYB12_SYSDATABASES.dat has not been created. Please check the screen output and .err files to detect the problem. Execute the script again when the problem is fixed or contact your Oracle representative if the problem persists.

rem *** CHECK THAT ALL MASTER META FILES HAVE BEEN CREATED



rem ** UPDATE THE OMWB FILE - THIS HOLDS SCRIPT AND PERTINENT SOURCE DATABASE SERVER INFO

goto omwbfile


:finalinstructions
echo **************************************************************************
echo ** The offline capture script has completed execution.                  
echo ** Please review the screen output and .err files (if any) in the output
echo ** directories for any irregularities. You may need to execute the
echo ** script again to resolve any irregularities.               
echo **                                                            
echo ** Finally, please archive the directory containing the sybase15.ocp file.
echo ** This contains master and %3 directories (preserve the
echo ** directory structure in the archive) the ROW.TXT, the COLUMN.TXT, and the
echo ** sybase15.ocp file. Return the archive file to
echo ** your Oracle representative.                                 
echo **************************************************************************
goto exit



:exit
rem ** REMOVE THE SCRIPT VERSION ENVIRONMENT VARIABLE
set OMWB_SCRIPT_VERSION=
rem ** REMOVE THE VALUE FOR THE OMWB FILE ENVIRONMENT VARIABLE
set OMWB_SCRIPT_FILE=
rem ** REMOVE THE VALUE FOR THE END OF ROW DELIMITER ENVIRONMENT VARIABLE
set OFFLINE_CAPTURE_ROW_DELIMITER=
rem ** REMOVE THE VALUE FOR THE END OF COLUMN ENVIRONMENT VARIABLE
set OFFLINE_CAPTURE_COLUMN_DELIMITER=

SYB15_BCP_SCRIPT.BAT

rem  %1 DBA login id
rem  %2 password
rem  %3 database name
rem  %4 database server name
rem  %5 end or column delimiter
rem  %6 end of row delimiter


rem export the required system tables in the master database
bcp sysdatabases out master\SYB12_SYSDATABASES.dat -c  -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4

rem export the required system tables in the database to be migrated

bcp %3.dbo.sysusers out %3\SYB12_SYSUSERS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.sysobjects out %3\SYB12_SYSOBJECTS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.systypes out %3\SYB12_SYSTYPES.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.syscolumns out %3\SYB12_SYSCOLUMNS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n"  -U%1 -P%2 -S%4
bcp %3.dbo.syscomments out %3\SYB12_SYSCOMMENTS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n"  -U%1 -P%2 -S%4
isql -U%1 -P%2 -D%3 -S%4 -b -i get_indexes.sql -o %3\SYB12_SYSINDEXES.dat
isql -U%1 -P%2 -D%3 -S%4 -b -w200 -i get_constraints.sql -o %3\SYB12_SYSCONSTRAINTS.dat

For SYBASE 12
Only 1 SYB12_BCP_SCRIPT.BAT needs to be updated with the following

SYB12_BCP_SCRIPT.BAT

rem  %1 DBA login id
rem  %2 password
rem  %3 database name
rem  %4 database server name
rem  %5 end or column delimiter
rem  %6 end of row delimiter


rem export the required system tables in the master database
bcp sysdatabases out master\SYB12_SYSDATABASES.dat -c  -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4

rem export the required system tables in the database to be migrated

bcp %3.dbo.sysusers out %3\SYB12_SYSUSERS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.sysobjects out %3\SYB12_SYSOBJECTS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.systypes out %3\SYB12_SYSTYPES.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n" -U%1 -P%2 -S%4
bcp %3.dbo.syscolumns out %3\SYB12_SYSCOLUMNS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n"  -U%1 -P%2 -S%4
bcp %3.dbo.syscomments out %3\SYB12_SYSCOMMENTS.dat -c -t "\t<EOC>\t" -r "\t<EOC>\t<EOR>\n"  -U%1 -P%2 -S%4
isql -U%1 -P%2 -D%3 -S%4 -b -i get_indexes.sql -o %3\SYB12_SYSINDEXES.dat
isql -U%1 -P%2 -D%3 -S%4 -b -w200 -i get_constraints.sql -o %3\SYB12_SYSCONSTRAINTS.dat

Manage Tablespaces

2008-05-15T10:23:00.000-07:00

In the old OMWB we had a "Discover Tablespaces" feature, so that you could assign tables to existing tablespaces in your Oracle database, before generating your Database.

In SQL Developer you can do this in 3 ways.

Change the generation script so that you specify a different default tablespace for the new schema and all its tables.
Change a tables tablespace once in Oracle. Right click the table and choose Storage > Move Tablespace
Write a script to automatically assign tables to different tablespaces depending on some criteria.

The first two options are show in this screen shot

But the third way may be the most useful.
If you wanted to have seperate tablespaces depending on the size of the tables you could run the following scripts on your Sybase/SQL Server database. The output of which can be run against your migrated Oracle database to move the tables to their new tablespaces. The best thing about this approach is you can do this before migrating the data, so that moving tablespaces is trivial.

SQL Server

SELECT CASE
       WHEN num_rows >=100 THEN
         'ALTER TABLE ' + TABLE_NAME +' MOVE TABLESPACE '+' BIGTABLESPACE ;'
       WHEN num_rows <100 AND num_rows >=50 THEN
         'ALTER TABLE ' + TABLE_NAME +' MOVE TABLESPACE '+' MEDIUMTABLESPACE ;'
       WHEN num_rows <50  THEN
          'ALTER TABLE ' + TABLE_NAME +' MOVE TABLESPACE '+' SMALLTABLESPACE ;' END  SCRIPT
FROM (SELECT
Table_Name = so.name ,
NUM_ROWS = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name ) ALL_TABLES

Sybase

drop table TableRowCount
go

drop procedure populateTableRowCount
go

CREATE TABLE TableRowCount(table_name varchar(200),num_rows int)
go

create procedure populateTableRowCount as
begin
declare @tableName varchar(200)
declare @rowNum int
declare name_cursor cursor
for select name from sysobjects where type ='U' and name != 'TableRowCount'
for read only
CREATE TABLE #countSize(rowNum int)
open name_cursor
fetch name_cursor into @tableName
while @@sqlstatus = 0 -- ie no errors and we successfully fetched a row
begin
execute( 'insert into #countSize  select count(*) rowNum from ' + @tableName)
select  @rowNum = max(rowNum) from #countSize
delete from #countSize
insert into TableRowCount values(@tableName,@rowNum)
fetch next from name_cursor into @tableName
end
close name_cursor
deallocate cursor name_cursor
end
go

exec  populateTableRowCount
go

SELECT CASE
        WHEN num_rows >=100 THEN
          'ALTER TABLE ' + table_name +' MOVE TABLESPACE '+' BIGTABLESPACE ;'
        WHEN num_rows <100 AND num_rows >=50 THEN
          'ALTER TABLE ' + table_name +' MOVE TABLESPACE '+' MEDIUMTABLESPACE ;'
        WHEN num_rows <50  THEN
           'ALTER TABLE ' + table_name +' MOVE TABLESPACE '+' SMALLTABLESPACE ;' END  SCRIPT
FROM TableRowCount
go

Oracle : If you are already on Oracle and the data is present in your Oracle tables.

SELECT CASE
       WHEN num_rows >=100 THEN
         'ALTER TABLE ' || TABLE_NAME ||' MOVE TABLESPACE '||' BIGTABLESPACE ;'
       WHEN num_rows <100 AND num_rows >=50 THEN
         'ALTER TABLE ' || TABLE_NAME ||' MOVE TABLESPACE '||' MEDIUMTABLESPACE ;'
       WHEN num_rows <50  THEN
          'ALTER TABLE ' || TABLE_NAME ||' MOVE TABLESPACE '||' SMALLTABLESPACE ;' END  SCRIPT
FROM ALL_TABLES
WHERE lower(owner) ='ownername'

The above scripts should generate another script like

SCRIPT
ALTER TABLE au_pix MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE authors MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE blurbs MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE discounts MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE publishers MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE roysched MOVE TABLESPACE  MEDIUMTABLESPACE ;
ALTER TABLE sales MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE salesdetail MOVE TABLESPACE  BIGTABLESPACE ;
ALTER TABLE skill MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE skill_detail MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE stores MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE table_emp MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE titleauthor MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE titles MOVE TABLESPACE  SMALLTABLESPACE ;

Which when run in Oracle will move the tables to the correct tablespace. The above results are from Sybase pubs2 example database

Offline Blob Data Move

2008-05-15T05:33:00.000-07:00

**Update: This is now set "on" by default in the preferences of SQL Developer 1.5.1**
In SQL Developer 1.5 we have a nifty solution for moving BLOBs (Images in SQL Server and Sybase) to Oracle offline.

The problem in the past was SQL Server and Sybase BCP tool dumps out binary values in HEX.
HEX cant be loaded easily into a BLOB in Oracle using SQL*Loader, there are some limitations to the size of the BLOB.

In the past we had a manual solution to create an extra CLOB column to load the HEX as a string/clob to . Then we had a procedure which was run after the datamove to convert the HEX to RAW piece by piece and insert it into the BLOB column. This is automated now but you have to set a preference first.

Select "Generate Stored Procedure to Migration Blobs Offline". In 1.5 by efault its off. Ill change this for 1.5.1 .

Now when you move your data offline, BLOBS will be handled automatically.

Search & Replace SQL within the Repository

2008-05-15T03:16:00.000-07:00

Sometimes you'd like to go a do a quick string search and replace of SQL for your captured triggers, procedures, functions or views.
The DDL for these objects is held within different migration repository tables. You can create the following view within your migration repository and use it to update the underlying migration repository.

CREATE OR REPLACE VIEW MGV_ALL_CAPTURED_SQL AS
WITH
captured_schema AS
(SELECT SCHEMA_ID
   FROM mgv_all_schema
  WHERE connection_id IN
  (SELECT ID FROM md_connections WHERE NVL(type,'Captured') != 'CONVERTED')),
captured_connections AS
(SELECT ID FROM md_connections WHERE NVL(type,'Captured') != 'CONVERTED') ,
captured_view_trigger AS
(SELECT v.trigger_id vt
   FROM mgv_all_view_triggers v
  WHERE v.connection_id IN
  (SELECT * FROM captured_connections)) ,
captured_table_trigger AS
(SELECT t.trigger_id tt
   FROM mgv_all_table_triggers t
  WHERE t.connection_id IN
  (SELECT * FROM captured_connections))
SELECT ID,'md_stored_programs' ObjType, Name objectName, native_sql
 FROM md_stored_programs,
captured_schema
WHERE language = 'MSTSQL'
  AND SCHEMA_ID_FK = captured_schema.schema_id
UNION ALL
SELECT ID,'md_views' ObjType, view_Name objectName, native_sql
 FROM md_views,
captured_schema
WHERE language = 'MSTSQL'
  AND SCHEMA_ID_FK = captured_schema.schema_id
UNION ALL
SELECT ID,'md_triggers' ObjType, trigger_Name objectName, native_sql
 FROM md_triggers
WHERE language = 'MSTSQL'
  AND (md_triggers.id in (select vt from captured_view_trigger union select tt from captured_table_trigger  ));

This view lists the procedures,function,views and triggers in the captured model.
Using it we can update those objects SQL.

update md_views
set native_sql = replace(native_sql, 'noexpand', 'noexpand nolock')
where id in ( select id from mgv_all_captured_sql where native_sql like '%noexpand%') ;

update md_stored_programs
set native_sql = replace(native_sql, 'noexpand', 'noexpand nolock')
where id in ( select id from mgv_all_captured_sql where native_sql like '%noexpand%') ;

update md_triggers
set native_sql = replace(native_sql, 'noexpand', 'noexpand nolock')
where id in ( select id from mgv_all_captured_sql where native_sql like '%noexpand%');

commit;

In the above example I'm replacing all occurances of "noexpand" with "noexpand nolock". This is a bug in 1.5 (fixed for 1.5.1.) where the Microsoft documentation of noexpand is not complete. I needed to add "nolock" after noexpand for the translator to recognize the syntax.

Quick Start to Migrating your SQL Server or Sybase Database

2008-05-13T04:28:00.000-07:00

** A better guide for SQL Developer 1.5.1 can be found here **
Ill outline the steps I would use to migrate a SQL Server or Sybase database to Oracle.

Download the latest version of SQL Developer
Download JTDS 1.2
Download and Install Oracle XE if you dont have access to an exiting Oracle database.
UnZip SQL Developer into its own directory (for example "SQLDev")
Don't use an existing ORACLE_HOME or ORACLE directory
Extract the JTDS jar file (jtds-1.2.jar) to a handy directory (SQLDev is fine)
Create the sqldeveloper.cmd file just to make double sure
Double click the sqldeveloper.cmd file to launch SQL Developer
Setup the jtds driver with SQL Developer
Tools> Preferences > Database > Third Party JDBC Drivers. Add the JTDS jar file
Create a connection to SQL Server/Sybase
Create a connection to your Oracle database
Create a new schema for the migration repository (MIGREP)
GRANT DBA TO MIGREP IDENTIFIED BY MIGREP
Create a connection to your migration repository schema (MIGREP)
Associate the migration repository with this schema
Right Click the migration repository connection (MIGREP), Migration Repository > Associate Migration Repository
Browse the SQL Server/Sybase database
Setup the "Is Quoted Identifier On" preference
Setup the DATETIME to DATE format mask if required
Setup the Offline BLOB data move preference if required
Capture the SQL Server/Sybase database (dont use Quick Migrate!) .
Note there is an offline capture available as well if you cannot directly connect to your SQL Server/Sybase database
Save the Captured Dialog Text (Handy to have a list of captured number or objects)
Identify any Capture Issues and Resolve (there shouldn't be any!)
Browse the Captured Model
Convert the Captured Model to the Oracle Model
Save the Converted Dialog Text (Handy to have a list of converted number of objects)
Save the Migration Log
Identify any Convert Issues and Resolve
"Generate" the Migration scripts
Run the Scripts using the SQL Worksheet to create your Oracle database.
Save the Result of the Generation Script
Identify and Generation Issues and Resolve them in your Oracle database.
Verify that all the objects in your SQL Server/Sybase database are present and valid in your Oracle database
Data Move for small dataset (<100mb),>
Data Move for large dataset (>100mb) , use the offline datamove
Test, Verify and Tune your database

Heres a viewlet made using SQL Dev 1.2.1 migrating a SQL Server database.

Protocol Violation / Capturing Issues

2008-05-07T06:25:00.000-07:00

**Update:This is now fixed in SQL Developer 1.5.1**
Some SQL Developer Migration Workbench users are experiencing issues capturing.
The migration log may have a Protocol Violation exception reported.

There seems to be an issue with some ORACLE_HOME versions installed on the same PC as SQL Developer.
To ensure that the SQL Developer Migration Workbench uses the shipped JDBC drivers and nothing from the existing ORACLE_HOME use this little script.

The workaround is to tell SQL Developer to ignore the ORACLE_HOME so that it has to reference the shipped JDBC drivers.


1) Close SQL Developer
2) Create a sqldeveloper.cmd file in the SQL Developer root directory
3) With the following contents
SET ORACLE_HOME=%CD%
start sqldeveloper.exe

4) Run sqldeveloper.cmd

We are looking into what causes this issue, but for the moment the above is the workaround.
This solution was originally suggested on the otn forums
http://forums.oracle.com/forums/thread.jspa?messageID=2058411�

Sybase Identity Columns Bug / Workaround

2008-05-07T02:22:00.000-07:00

**Update:This is now fixed in SQL Developer 1.5.1.**
Bug Alert! Identity Columns are not converted correctly for Sybase in SQL Developer 1.5.
Heres how it should be done manually till we get it fixed.

Replace columname and tablename with the identity column details.

CREATE SEQUENCE  <tablename>_<columnname>_SEQ
 MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1  NOCYCLE ;

CREATE OR REPLACE TRIGGER <tablename>_<columnname>_TRG BEFORE INSERT OR UPDATE ON <tablename>
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
 IF INSERTING AND :new.<columnname> IS NULL THEN
   SELECT  <tablename>_<columnname>_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
   -- If this is the first time this table have been inserted into (sequence == 1)
   IF v_newVal = 1 THEN
     --get the max indentity value from the table
     SELECT NVL(max(<columnname>),0) INTO v_newVal FROM <tablename>;
     v_newVal := v_newVal + 1;
     --set the sequence to that value
     LOOP
          EXIT WHEN v_incval>=v_newVal;
          SELECT <tablename>_<columnname>_SEQ.nextval INTO v_incval FROM dual;
     END LOOP;
   END IF;
   -- save this to emulate @@identity
  sybase_utilities.identity := v_newVal;
  -- assign the value from the sequence to emulate the identity column
  :new.<columnname> := v_newVal;
 END IF;
END;

We should have this automated in the next release.

Note I used this great tool to format the code in this blog
http://formatmysourcecode.blogspot.com/

SQL Server DATETIME TO Oracle DATE

2008-05-06T06:18:00.000-07:00

**Update:There is a better, more consistent solution in SQL Developer 1.5.1**
Offline Datamove involves using SQL Server/Sybase BCP tool to dump out the data in the tables into dat files. Then Oracles SQL*Loader is used to load the data into Oracle.
This is the recommended approach when dealing with large datasets, as BCP and SQL*Loader are designed for datamove.
Also SQL Developer will create the scripts for you, so you don't have to manually create them.

One issue that has cropped up is moving DATETIME values to DATE.
SQL Server/ Sybase DATETIME has millisecond precision. Oracle has the TIMESTAMP datatype to hold millisecond values. But sometimes you just want to convert from DATETIME to DATE and ignore the milliseconds as they are not important.

To do this you can specify in the date format mask to ignore any values after the second value. Use dots '.' to do this job.
Date Mask :yyyy-mm-dd HH24:mi:ss......

The SQL*Loader scripts will include this format mask around each of the DATETIME column values being loaded.

If you dont specify in the format mask to ignore the millisecond values , SQL*Loader will not recognize the DATETIME values in the dat files.

Note: The old OMWB used to create a view in the source database todo this, but this solution is allot neater.

Dynamic SQL

2008-04-30T03:55:00.000-07:00

Dynamic SQL can be tricky to translate. Imagine the following T-SQL


EXECUTE("SELECT TOP 10 " + @selectList + "FROM table1,"+@newTableName + " WHERE "+@condition1);

Gets Translated to


BEGIN
   EXECUTE IMMEDIATE 'SELECT TOP 10 ' || v_selectList || 'FROM table1,' || v_newTableName || ' WHERE ' || v_condition1;
END;

Our translator is designed to recognize and translate valid T-SQL. But the SQL within the strings that make up the execute statement is not complete, therefore its not valid SQL that the translator can work with. How should it translate "SELECT TOP 10" ?

As a whole the Execute statement is valid, but in the eyes of the translator it cant do much with it. So the translator does not translate dynamic SQL. This has to be manually performed.
But there is help!

Take the Dynamic SQL String and create a valid, complete SQL Statement from it.
I added in "dummy" values to make up for the missing pieces.


SELECT TOP 10 dummycol/*@selectList*/ FROM table1 , dummytable/*@newTableName*/ WHERE dummycondition = true/*@condition1*/

Pop it in the Translation Scratch Editor and translate.


SELECT dummycol
  /*@selectList*/FROM table1 ,
       dummytable
   /*@newTableName*/WHERE dummycodition = TRUE/*@condition1*/ AND ROWNUM <= 10;

I would used q'# .... #' to define the string.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements003.htm#i11223
This just allows me to use single quotes within string literal, so I don't have to escape each one. In this example its not required.


BEGIN
   EXECUTE IMMEDIATE 'SELECT'||v_selectList ||'FROM table1 ,'|| v_newTableName || 'WHERE '||v_condition1 || ' AND ROWNUM <= 10';
END;

This is a small example so you may be quicker just translating the Dynamic SQL by hand. The process gets handy when you have a larger Dynamic SQL Statement.

What to do if a procedure/trigger/view fails to convert

2008-04-23T06:51:00.000-07:00

Sometimes a procedure, function, trigger or view will fail to convert. An error will popup in the migration log "Failed to convert blah blah". The case maybe that the object is hundreds of lines long but one line is causing the translator to fail.

Heres how I go about identifying that problematic clause so you can continue the migration.

Copy the ddl into the Translation Scratch Editor (Migrations> Translation Scratch Editor)(TSE)
Select the correct translator from the TSE toolbar.
Translate it
If it translates , then you can use the converted PL/SQL as the starting point. Paste it into your converted model, report the bug and continue with your migration.
If it fails, then I comment out half of the statements (still leaving valid T-SQL, dont cut words or statements in half :) )
Keep commenting out parts of the objects ddl until you it translates and you have comented out a particular statement or clause. You can then paste the generated PL/SQL into your converted model, log a bug and continue your migration.

Often there is 1 style of syntax that the translator doesnt recognize, but because the same developer wrote many of the procedures, the same syntax occurs again and again. Once you can recognize what the issue is, you can modify the captured model suitably and then report a bug so that it can be automated in the next release.

SQL Dev 1.5 Migration Tip

2008-04-23T05:41:00.000-07:00

Sybase and SQL Server have an option to called "is quoted identifier on".
When ON, double quotes can be used to reference identifiers (tables,columns,....).
When OFF, double quotes means string literals ("Hello World").

In the past this caused no end of problems when translating to PL/SQL , as our translator had to guess if a double quote was referencing a identifier or a string.
For Example what does this mean ?


SELECT "col1" from table1

Is "col1" a column in table1 or is it string? In PL/SQL strings are defined using single quotes and identifiers are convert so that they are case insensitive (without double quotes).

Pre 1.5 the translator would look at the context the double quotes where used and make a best guess.

In 1.5 we changed this so that the translator can be more accurate.
There is a new preference

By Default (like the SQL Server and Sybase) it is on. So double quotes are recognized in T-SQL as identifiers.

If the database your migrating uses double quotes as strings, then unclick this option before converting or attempting a quick migrate.

You will run into convert issues if this option is not set correctly. I have added a note to the doc but as this is a bit of a gotcha in 1.5 I thought Id blog it.

Note, that Alias can still be defined using double quotes and the translator recognizes them correctly.

Ref:
http://msdn2.microsoft.com/en-us/library/aa259228(SQL.80).aspxhttp://manuals.sybase.com/onlinebooks/group-as/asg1250e/refman/@Generic__BookTextView/25659;pt=26021