Posts

Showing posts from May, 2008

Using Regular Expression To Resolve Multiple Issues

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…

Captured And Converted Indexes Report

Image
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><…

Parameters Of A Procedure Call

**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 =>…

Sybase Offline Capture Scripts on Windows

Image
**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.BATSYB15_BCP_SCRIPT.BAT
SYB15_OFFLINE_CAPTURE.BAT
@echo off

rem ** SET THE VALUE FOR THE O…

Manage Tablespaces

Image
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 TablespaceWrite 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 tabl…

Offline Blob Data Move

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

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_prog…

Quick Start to Migrating your SQL Server or Sybase Database

** 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.2Download 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 directoryExtract the JTDS jar file (jtds-1.2.jar) to a handy directory (SQLDev is fine)Create the sqldeveloper.cmd file just to make double sureDouble click the sqldeveloper.cmd file to launch SQL DeveloperSetup the jtds driver with SQL DeveloperTools> Preferences > Database > Third Party JDBC Drivers. Add the JTDS jar file
Create a connection to SQL Server/SybaseCreate a connection to your Oracle database
Create a new schema for the migration repository (MIGREP)GRANT DBA TO MIGREP IDENTIFIED BY MIGREPCreate a connection to your migration repository schema (MIGREP)As…

Protocol Violation / Capturing Issues

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

**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 sequ…

SQL Server DATETIME TO Oracle DATE

Image
**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 …