Oracle Database 12c allows you to define a column to be an IDENTITY column. It can be either GENERATED ALWAYS or BY DEFAULT.
A GENERATED ALWAYS AS IDENTITY column does not allow you to INSERT a value for it. When it comes to moving data from one table to another and this type of column is present, there are a few steps to "disable" the GENERATED ALWAYS AS IDENTITY column and after the data move "enable" it.
Sybase , SQL Server and other databases allow for long identifier names. Oracle allows for a maximum of 30 Bytes when naming objects like users, tables, ... When it comes to migrating objects to Oracle, SQL Developer will truncate the object names and resolve clashes with unique names.
How to Migrate T-SQL to Oracle PL/SQL Oracle SQL Developer available for free on OTN provides a Migrate to Oracle feature. Migrate to Oracle helps automate the task of migrating a SQL Server or Sybase database, their tables, data and their T-SQL objects (Views, Triggers, Functions, Procedures) to a corresponding Oracle database and PL/SQL Objects.
Debugging a procedure using SQL Developer is really neat. But I ran into an issue whereby I wanted to inspect the rows of a temporary table while I was debugging a stored procedure. I was unable to browse and inspect the rows in the temporary table as the debug session is on a different session. I had to throw in some debug code into the procedure to see something useful in the debug data tab. I ended up with the little procedure below which you may find useful. Basically I query the table in question and place the result in a CLOB to view in the debug window.
Sometimes its not possible to directly connect over JDBC to a database your want to migrate to Oracle. Sometimes you want someone else to perform the migration but would rather if you could just email them the metadata to carry out the migration without having them onsite. In these cases SQL Developer offers Offline Capture.
Moving data from a non Oracle database to Oracle can be a bit tricky when different character sets are at play. For this example Ill move data from a Russian (Cyrillic_General_CI_AS) SQL Server database on windows to UTF8 (AL32UTF8) Oracle database on linux.
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)
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
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.
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".
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.
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.
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.
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.
**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.
**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.
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.
**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.
**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.
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.