Multibyte Offline Data Move

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.

SQL Server 2008 Cyrillic_General_CI_AS database
CREATE TABLE multibyte1(col1 VARCHAR(10),col2 CHAR(10));
CREATE TABLE multibyte2(col2 NVARCHAR(10),col2 NCHAR(100));
INSERT INTO multibyte1 VALUES('фис','фис');
INSERT INTO multibyte2 VALUES('фис','фис');
SELECT * FROM multibyte1;
SELECT * FROM multibyte2;

SQL Developer can migrate the two tables to Oracle

CREATE TABLE multibyte1(col1 VARCHAR2(10),col2 CHAR(10));
CREATE TABLE multibyte2(col2 NVARCHAR2(10),col2 NCHAR(100));

SQL Developer will also generate offline data move scripts which can perform the data move using SQL Servers BCP and Oracle SQL*Loader.

There are two issues with the scripts generated by SQL Developer (Ill fix them from the next release).

• The shell files are not in linux format
• The NLS_LANG setting is specified incorrectly as NLS_LANGUAGE

The solution is to 

• run dos2unix on the .sh files
• change NLS_LANGUAGE to NLS_LANG in the "bottom" oracle_ctl.sh file

Once that's done, the data move is performed in two steps. 

Dump the Data From SQL Server using BCP

The data from SQL Server can be dumped out to dat files using the unload_script.sh  or unload_script.bat.

The data inside the dat files can be verified as correct. 

The dat files will be encoded in  Cyrillic_General_CI_AS (Cyrillic Window 1251).

You can now move all the files to your linux machine to load into Oracle.

Load the Data To Oracle using SQL*Loader

SQL*Loader needs to be able to recognize the characters in the dat files.

In the "bottom" oracle_ctl.sh,  export NLS_LANG should be set to RUSSIAN_CIS.CL8MSWIN1251 .

SQL*Loader will recognize the characters and insert them correctly.

The oracle database is setup to save them in UTF8.

Verify the data in Oracle

In linux, if you open SQLPlus and query the tables, you may get ? , question marks instead of the Russian characters. This is because Oracle has saved the characters in UTF-8.

In linux, set the NLS_LANG to UTF8 and query the tables again.

export NLS_LANG=_.UTF8

Cross Connection Query Issue

Topic Discussion ( Re: Bug: Cross Connection Query not working in 3.1, 3.2.1 )
https://forums.oracle.com/forums/post!reply.jspa?messageID=1065008

DBMS_OUTPUT manipulation and filtering in SQL*Plus

Small script to filter and manipulate the DBMS_OUTPUT in PL/SQL from SQL*Plus

CLEAR SCREEN;
SET LONG 1000000000 ;
SET SERVEROUTPUT ON;

VARIABLE outputclob CLOB ;
VARIABLE maxlines   NUMBER;
--set the max lines
EXECUTE  :maxlines  :=10;

DECLARE
  --array to save the output lines into
  outtab dbms_output.chararr;
  outstr VARCHAR2(255);
BEGIN
  -- initialize the output clob
  dbms_lob.createtemporary (:outputclob, TRUE);
  -- add some lines to the output buffer, added some "special" lines to filter on
  dbms_output.put_line('special 1');
  dbms_output.put_line('nothing special 2');
  dbms_output.put_line('special 3');
  dbms_output.put_line('nothing special 4');
  dbms_output.put_line('special 5');
  dbms_output.put_line('nothing special 6');
  
  --get the output lines into the local array
  dbms_output.get_lines(outtab, :maxlines);--maxlines is changed here if there are fewer rows
  -- note that this string will not be part of the output clob, set serveroutput on will pick it up
  dbms_output.put_line('maxlines after:'||:maxlines);
  
  --loop through the array and build up the output clob
  FOR i IN 1 .. :maxlines LOOP
      outstr := outtab(i);
      -- filter the output
      IF INSTR(outstr,'special') = 1 THEN
        --modify the output
        dbms_lob.writeappend(:outputclob,
                            length(outstr||chr(10)||'my message'||chr(10)),
                                   outstr||chr(10)||'my message'||chr(10)); 
      END IF;                    
  END LOOP;
END;
/
print outputclob;

Migrate to Existing Oracle Users

Problem

By default, SQL Developer will migrate a Sybase, SQL Server ,... database to a brand new target user in Oracle. This new user is defined (CREATE USER ... ) at the start of the the generation script.

For Sybase and SQL Server, we append the owner name to the database name (ex: dbo_Northwind) , to come up with a new Oracle target user name. This is done to create the same separation of objects. At the moment (SQL Developer 3.1) we define the password to be the same as the user name. Its always best to perform the generation phase "offline" so you can inspect the generation script first before it is run.

Generation Script : Notice the CREATE USER ....

In the above example I migrated the SQL Server Northwind database with default preferences.

Default Preferences

Solution

If you wish to migrate to an existing Oracle User then there are two methods

1. Least Privilege Schema Migration : Run the generation script directly into the chosen connection, with no users created
2. Object Naming: Specify what target user name you want

Least Privilege Schema Migration

Tools>Preferences>Migration>Generation Options

Choose Least Privilege Schema Migration.

Once checked, you can reconvert a Captured Database Objects model. As long as there is only one target Oracle user , then the generation script will be created, without any CREATE USER or CONNECT ...

The script will then create the migrated objects into the target connection specified.

Note that if the migration project has more than one target user (ex: dbo_Northwind & user2_Northwind) SQL Developer will not create a least privilege script. It will create a standard script with CREATE USER, CONNECT TO .. for each user.

Object Naming: Specify what target user name you want

If you have more than one existing target Oracle user and you want the objects to be migrated into them. Then you have to explicitly state what Oracle user you want to use. SQL Developer needs to know what user to connect to when creating the objects. SQL Developer also needs to know the target user names when converting Views, Triggers, Procedures and Functions. So that cross database references are resolved correctly.

Any object name change, including user/schema name change, should be performed in the convert page of the Migration Wizard.

To see the Object Naming tab, you must capture the source database first. Then perform the convert. Otherwise SQL Developer does not know the name of the source objects.

Just choose "Proceed to Summary Page" on the Capture page of the Migration Wizard

You can right click on the Captured Database Objects node in the Migration Projects navigator and choose Convert.

Object Naming Tab : Only visible after the source database has been captured.

You can filter by object type ,to show just the Users/Schemas.

SRC_TYPE='MD_SCHEMAS'

Change the New Identifier to your target Oracle user. And Commit the change

The generation script will now use the new target name. And all cross database references will be correct.

Note that you may want to comment out the CREATE USER as it will fail if the user already exists, or you can just ignore the error.

Note you should review the CONNECT statements to make sure the existing password is used, possibly prompt for it using substitution.

Note that using the above two methods allows SQL Developer to perform the data move correctly as the target users are identified.

Sybase and SQL Server Image Data Move

SQL Developer can move data online and offline.

Online is really only for small amounts of data (<100mb).

For larger sets of data with greater performance and much better logging, the Offline Data Move feature should be used.

SQL Developer will create two sets of scripts.

One to dump out the data using Sybase/SQL Servers BCP tool to DAT files on the disk.

The second set of scripts to read the DAT files and load the data into the Oracle tables using Oracle SQL*Loader.

As the scripts are just plain text files they should be inspected and can be modified to suit your needs.

Because SQL Developer knows the names of all the tables and each columns name/datatype it can do a good job of defining these scripts, so very little has to be modified.

Currently there is an issue moving large Images from Sybase/SQL Server to Oracle using the offline data move scripts SQL Developer generate. But with a little change you can get them to move correctly.

For this little test , the first thing is to

Insert an Image into Sybase using BCP.

• Create a table with one column in Sybase of type IMAGE

CREATE TABLE loadimage (col1 IMAGE)

go

• Make sure the Sybase database can INSERT from BCP (note this database is called loadimage)

use master

go

sp_dboption "loadimage", "select into", true

go

use loadimage

go

checkpoint

go

• The bcp.fmt needs to have the correct size of the imagefile specified, in this case 1551333.

10.0

1

1 SYBIMAGE 0 1551333"" 1 col1

• From the command line load an image into the Sybase table using Sybase BCP

bcp loadimage..loadimage in image3.jpg -Usa -P -S -f bcp.fmt -T2000000

This should load up the image3.jpg into the Sybase table.

It can be viewed from SQL Developer.

Just browse to the Sybase table, click on the data tab. Double click the (BLOB) item and choose "View As Image".

Now we need to move it to Oracle.

Migrate the Sybase database to Oracle, but choose Offline Data Move in step 8 of the migration wizard.

The following changes need to be performed against the scripts SQL Developer generates for you.

The unload_script.bat needs to be changed as BCP by default will truncate to 32K.

mkdir data

mkdir log

bcp "[loadimage].[dbo].[loadimage]" out "data\dbo_loadimage.loadimage.dat" -T 1552503 -c -t "" -r "" -U%2 -P%3 -S%1

The -T 1552503 denotes the largest Image in that column.

This can be found by querying the Sybase table.

select max(datalength()) from

Now the DAT file will include all the IMAGE data in HEX format, and it will not be truncated.

Next we have to load it into Oracle and those scripts need a little change as well.

SQL Developer creates scripts which

1) Create a new CLOB column to hold the HEX string

2) Process the CLOB HEX string into a BLOB using a procedure

3) Remove the CLOB column

This is necessary as you cannot load a large HEX string directly into a BLOB column in Oracle.

This action is performed for you, but this is a default limit to the size of the HEX string SQL*Loader will read, and this needs to be changed.

Open the control/dbo_loadimage.loadimage.ctl file

load data

infile 'data/dbo_loadimage.loadimage.dat'

"str ''"

into table dbo_loadimage.loadimage

fields terminated by ''

trailing nullcols

(

SQLDEVELOPER_CLOB_1 CHAR(20000000)

)

Change the size of the CHAR column. This is the temporary column the scripts will create for you to load in the HEX String.

Once this is done you can run the script to load the image data into the Oracle table.

oracle_ctl.bat system oracle@11GR1

Notice that the rows are loaded fairly fast into the CLOB columns as HEX strings. But it does take some time to process these HEX strings into BLOBs.

Once the load is finished the Images can be inspected in SQL Developer

JDeveloper Group By Insight Preference

During Oracle OpenWorld a customer asked how to turn off the autogenerate of GROUP BY clause when using JDeveloper.

SQL Developer has a preference

But JDeveloper is missing this preference, and it is turned on by default.

This will be rectified in a future realese of JDeveloper but in the mean time there is a workaround.

1) Close JDeveloper.

2) Open JDevelopers product-preferences.xml file.

This can be found under

c:\Users\\AppData\Roaming\JDeveloper\\o.jdeveloper\

on windows 7. Or in the usual place applications persist preferences in your OS.

3)Add the following after the ide tag.

<hash n="DBConfig">
     <value n="AUTOGENERATEGROUPBY" v="false"/>
     <hash n="EXTENSION"/>
     <value n="INDENT_CACHE_NUMBER" v="2"/>
     <value n="INDENT_CACHE_USE_TAB" v="false"/>
  </hash>

.

4) Restart JDeveloper, and Autogenerate GROUP BY should not turned off.

Very Large Migrations

Most of the time SQL Developer does a good job of migrating a database from SQL Server, Sybase ,... To Oracle. But there are some tricks to help make very large migrations perform smoother.

This talks about Large migrations in terms of object numbers , not amount of data in tables.

If your migrating 50 databases at once, and each has 1,000 tables, 1,000 views , 1,000 procedures . That is a lot of meta data to churn through and sometimes we find

memory issues , open cursors ,... can be a problem.

We are always working to reduce the amount of resources required to perform the migrations , but for the moment (SQL Dev 3.0) here are a few tricks which just might make the difference between the migration failing and the migration completing.

Memory

By Default SQL Developer startups with

• AddVMOption -XX:MaxPermSize=128M
• (found in sqldeveloper\sqldeveloper\bin\sqldeveloper.conf)
• AddVMOption -Xmx640M (found in sqldeveloper\ide\bin\ide.conf)
• AddVMOption -Xms128M (found in sqldeveloper\ide\bin\ide.conf)

This is usually fine, but ... if you have more memory, SQL Dev maybe able to use it.

When using SQL Developer 32 bit on a 32 bit machine with a 32 bit JDK. The following is ok

• AddVMOption -XX:MaxPermSize=128M
• (found in sqldeveloper\sqldeveloper\bin\sqldeveloper.conf)
• AddVMOption -Xmx1024M (found in sqldeveloper\ide\bin\ide.conf)
• AddVMOption -Xms256M (found in sqldeveloper\ide\bin\ide.conf)

When using SQL Developer 64 bit on a 64 bit machine with a 64 bit JDK. The following is ok . As long as you have the memory.

• AddVMOption -XX:MaxPermSize=512M
• (found in sqldeveloper\sqldeveloper\bin\sqldeveloper.conf)
• AddVMOption -Xmx4096M (found in sqldeveloper\ide\bin\ide.conf)
• AddVMOption -Xms2048M (found in sqldeveloper\ide\bin\ide.conf)

Oracle Database

By Default Oracle uses a small maximum of open cursors allowed. When migrating large databases SQL Developer does tend to open a lot of cursors against the Oracle database which has the migration repository. We have worked on minimizing this and will continue to do so, but the workaround at the moment is to set this limit higher in the Oracle database you create your migration repository in.

ALTER SYSTEM SET open_cursors=10000 COMMENT='' SCOPE=BOTH

Note if you are migrating multibyte databases (objects names are in chinese, japanese ,.. ) you should create the migration repository in a UTF-8 Oracle database. Otherwise the object names may not be saved/migrated correctly.

SQL Developer

SQL Developer obviously interacts with the migration repository. For example to convert a stored procedure. It queries the migration repository to retrieve the captured SQL and then inserts into the repository the converted SQL. Not only that, SQL Developer also queries the migration repository to identify name changes, data type of columns and so on. These round trips back and forth to the migration repository can slow down a migration.

If you are migrating large amounts of Stored Procedures, Triggers, Views, Functions it is best to install SQL Developer on the same machine as the Oracle database that contains the migration repository. This will remove the network overhead and speed up the migration.

Migrate Incrementally

SQL Developers Migration Wizard allows you to define the entire migration you want up front, and then kick it off. This is ok for smaller migrations, but I think it best that most migrations perform the migration incrementally.

The migration wizard allow you perform each step of the migration by itself. This enables you to review each step of the migration before proceeding to the next.

Just click the "Proceed to Summary" checkbox at the bottom left of the wizard and then click Next. The wizard will then only perform the steps you have choosen so far.

When this step is complete you will get a chance to review the result and then kick off the wizard again , straight back into the wizard at the correct step.

Not only that but breaking it into steps also helps with memory/cursor issues.

Redundant Objects

Some times a database which is being migrated contains a lot of objects which are redundant or not required to migrate. If there is a substantial amount of these objects, its best to remove them from the captured model so as they do not consume resources going forward.

Right click the objects in the captured model you do not want and choose delete.

Note that this is a model and not connected to your Source SQL Server, Sybase ,... database.

So those source objects are not deleted , just items in your model saved in the migration repository.

Generation Script

Very large databases will have very large generation script. By default SQL Developer generates one big script which is then run to create all the target objects in Oracle.

But there is a preference (Migration > Generation Options > A File Per Object) to split each object (table,trigger,view,...) definition into a separate file.

Then a master script calls each file individually to create the objects. This means a much smaller master file.

It is also handy if you want to source control the object definitions.

Empty Space and Single Space Data Move

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.

FLOAT data type migration

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
insert into testfloat values(1.005)
go
insert into testfloat values(1.0049999999999999)
go
select * from testfloat --both values are displayed as 1.005
go
select * from testfloat where floatcol = 1.005 --returns both rows
go
select * from testfloat where floatcol = 1.0049999999999999 --returns both rows
go
select * from testfloat where floatcol = 1.0049999999999998 --returns both rows
go
select * from testfloat where floatcol = 1.0049999999999997 --returns NO rows
go
select * from testfloat where floatcol = 1.004999994--returns NO rows
go
select * from testfloat where floatcol = 1.004999493 --returns NO rows
go

So it looks like Sybase stores 1.005 as 1.0049999999999999, but displays it to the clients (like isql, jdbc) as 1.005
A comparison can be made with the literal 1.005 (though not recommended as float should never be compared in this way)

ORACLE
DROP table testfloat;
create table testfloat(floatcol float, binaryfloatcol binary_float ,varchar2floatcol varchar2(100) );
insert into testfloat values(1.005,1.005,'1.005') ;
insert into testfloat values(1.0049999999999999,1.0049999999999999,'1.0049999999999999');
select * from testfloat; --both values are displayed as 1.005 for BINARY_FLOAT and a mix as FLOAT
select * from testfloat where floatcol = 1.005; --returns one row
select * from testfloat where floatcol = 1.0049999999999999 ; --returns one row
select * from testfloat where floatcol = 1.0049999999999998 ; --returns NO rows
select * from testfloat where floatcol = 1.0049999999999997 ; --returns NO rows

select * from testfloat where binaryfloatcol = 1.005; --returns two rows
select * from testfloat where binaryfloatcol = 1.0049999999999999 ; --returns two rows
select * from testfloat where binaryfloatcol = 1.0049999999999998 ; --returns two rows
select * from testfloat where binaryfloatcol = 1.0049999999999997 ; --returns two rows

select * from testfloat where binaryfloatcol = 1.004999994 ; --returns two rows
select * from testfloat where binaryfloatcol = 1.004999493 ; --returns no row

Oracle stores FLOAT values as explicit numbers whereas BINARY_FLOAT values are not as precise. Oracle also displays 1.005 for both values in BINARY_FLOAT.

Conclusion
From the above test cases it looks like Oracle BINARY_FLOAT is a much better match than Oracle FLOAT for Sybase FLOAT, but it should be noted that since Sybase FLOAT and Oracle BINARY_FLOAT are not precise numbers , comparing equivalence is not identical between databases.

floats , be they Sybase FLOATs or Oracle BINARY_FLOATs should never be directly compared, rather a range of values should be matched.

Notes
Sybase BCP dumps out both floats as 1.0049999999999999
JDBC query against Sybase returns both floats as 1.005.

So during a SQL Developer data move, online will insert 1.005 , but offline will insert 1.0049999999999999. This doesn't really matter if the Oracle data type is BINARY_FLOAT, but it does if you use Oracle FLOAT data type.

Migration Reports 3.0

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 Size Details

Line size for every SQL Object
Columns
Details of all captured colums
Name Changes
All the name changes between the captured objects and the converted objects
Dependencies
Dependency tree between store procedures and other objects

Temporary Table Summary
Summary of the number of temporary tables per project / database
Temporary Table Details
List of all temporary tables identified and their parent procedure
Parameters
List of all procedure and function parameters including new params


Capture Issues

There should be no capture issues, but if an issues occurs it will be listed here


Conversion Status

Lists the number of Procedures, Triggers and Views which converted or failed to convert

Conversion Issues

Reports on all the issues which where encountered during the conversion.
Errors denote objects which failed to convert.
Limitations denote objects which converted but a particular statement or clause was not supported


Target Status

After selecting an appropriate connection to the target database and clicking refresh, this report will list all the objects in the converted model and their status in the target database. Valid, Invalid or Missing


Target Issues

After choosing the appropriate Target connection and clicking refresh, this looks at the issues of each object in the target database.


Data Quality

After selecting the appropriate
Model, Source Connection and Target Connection click Analysis.

You will be prompted to continue as you are reminded that a full table scan of all the tables on the source connection and target connection will take place.


SELECT count(*) from tablename is performed against each table and the values inserted into the migration repository.

Once complete, click refresh. Each table migrated is listed with the source row number and the target row number, making it easy to identify tables where all the data was not migrated

Note that SELECT count(*) from tablename may make a significant impact on the performance of the source and target database so best run only on non production databases.


Model Comparison

After choosing the first captured model and the second captured model click refresh. The differences between the models will be listed.

This is useful when trying to identify changes to your source database over a period of time , sometimes called the delta.

This knowledge can help identify any new or modified objects which require migration.

SQL Developer DB2 Connection

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

Heres what my preference page looks like. (note, JTDS is for SQL Server and Sybase)












Once the correct jar file has been added you now have an extra DB2 tab in the connections dialog
















Once you add your connection details you can browse your DB2 database objects.















You can learn more about migrating to Oracle from IBM DB2 LUW here

• http://www.oracle.com/technetwork/database/migration/index-084442.html
• http://download.oracle.com/docs/cd/E15846_01/doc.21/e15222/migration.htm#CHDECEIA
  
• http://www.oracle.com/technetwork/database/migration/db2-084087.html
• http://download.oracle.com/docs/cd/E15846_01/doc.21/e15286/toc.htm

SQL Server and Sybase Browsing

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







Another nice feature is the Schema Browser, which makes working on one database/owners objects much neater.

SQL Developer 3.0 Migration Features

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

Copy To Oracle

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 , the migrated table and data should be verified as complete.

Workheet Hints

SQL Developer currently doesn't support proprietary commands of non Oracle databases like SQL Server, Sybase ... . But a new feature call Worksheet Hints allows these commands to be executed.

SQL Developers Worksheet has to parse commands to identify their start and finish and their type (query, or update ,... ).
We recognize Oracle SQL , PL/SQL and SQL*Plus , but we have yet to work on other databases syntax.

So at the moment if you running a command against MySQL, SQL Server or Sybase, it has to be fairly similar to an Oracle statement for it to be recognized and run correctly.
EX: SELECT, INSERT,UPDATE,DELETE.

Things like MySQL
use [databasename]
or
show databases
are not recognized yet.

Full MySQL recognition will not make it into SQL Developer 3.0 ,
but SQL Developer 3 EA2 does allow you to give the Worksheet parser a hint as to what type of statement it is and how to execute it.

The hint is placed in front of the command, and
the command has to be terminated with a forward slash on its own line, or a semi colon.

The two hints are
/*sqldev:query*/ if your expecting a result set to be returned
/*sqldev:stmt*/ if you are expecting nothing returned

MySQL Example:
If you connect to your MySQL connection you can perform the following
/*sqldev:query*/show databases;
/*sqldev:stmt*/use dermot;
select database();
/*sqldev:stmt*/use information_schema;
select database();

The same goes for SQL Server , Sybase ,.... commands which are not recognized by our Worksheet.

Sybase Example:
/sqldev:query*/sp_help;

Appreciate this is not an ideal solution, but until we recognize the syntax correctly this may help out.
Just to be clear, we are not supporting all MySQL/SQL Server/Sybase commands yet. But hopefully using a "hint" might be of some use.

SQL Developer Automatic Connection Definition

When creating users in Oracle , you then have to create a connection in SQL Developer, repeating the information.

So you would run something like this in the SQL Worksheet
CREATE USER dermo IDENTIFIED BY dermo;
GRANT CONNECT,RESOURCE TO dermo;

Then you would have to create a SQL Developer connection.








In SQL Developer 3.0 you can use the SQL Worksheet to create the connection for you.









CREATE CONNECTION connectionname(CREATE USER username IDENTIFIED BY password);
GRANT CONNECT,RESOURCE to username;