Dermot's Tech Tips

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.

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

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

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

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

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