Dermot's Tech Tips

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

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

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

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

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

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

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

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

**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�

**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; ...

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