Dermot's Tech Tips

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

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 ,

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 forw

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;

Oracle allows you to query separate databases using Database Links. You can even setup a Database Link in an Oracle database with a non Oracle database such as SQL Server or Sybase. Creating these types of Database Links can take some time, and sometimes you just want to reference a small bit of data on another Database and use it in your query. SQL Developer allows you define connections to multiple databases including non Oracle databases. You can query each connection separately but until now you couldn't easily query different data sources (connections) at the same time in the one statement. BRIDGE statement This is a SQL Developer only command. It is not supported in SQL*Plus or directly in the Oracle database. SQL Developer interprets this statement and performs multiple actions to return the result. BRIDGE statement can be used in two ways 1) To copy a table and its data from one connection to another 2) To facilitate cross connection/database queries --*******INTRO******* -