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