view counter

Feed items

T-SQL v PL/SQL

How to Migrate T-SQL to Oracle PL/SQL
Oracle SQL Developer available for free on OTN provides a Migrate to Oracle feature.
Migrate to Oracle helps automate the task of migrating a SQL Server or Sybase database, their  tables, data and their T-SQL objects (Views, Triggers, Functions, Procedures) to a corresponding Oracle database and PL/SQL Objects.

T-SQL v PL/SQL

Inspecting a Temporary Table or Uncommitted Table rows in a Debug Session

Debugging a procedure using SQL Developer is really neat. But I ran into an issue whereby I wanted to inspect the rows of a temporary table while I was debugging a stored procedure. I was unable to browse and inspect the rows in the temporary table as the debug session is on a different session. I had to throw in some debug code into the procedure to see something useful in the debug data tab. I ended up with the little procedure below which you may find useful. Basically I query the table in question and place the result in a CLOB to view in the debug window.

Offline Capture

Sometimes its not possible to directly connect over JDBC to a database your want to migrate to Oracle.
Sometimes you want someone else to perform the migration but would rather if you could just email them the metadata to carry out the migration without having them onsite.
In these cases SQL Developer offers Offline Capture.

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.

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);

DBMS_OUTPUT filtering using SQL*Plus

Little script to filter & modify the DBMS_OUTPUT.
May come in useful as an example using SQL*Plus CLOB VARIABLE.

CLEAR SCREEN;
SET LONG 1000000000 ;
SET SERVEROUTPUT ON;

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

DECLARE

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.

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.

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

view counter