Oracle VM Site Review - Oracle VM Health Check
view counter

Feed items

Oracle 12c Implicit Result Sets in SQL Developer 4.1

Ever want to run a Stored Procedure or a PL/SQL anonymous block and just want to "print out" the results of a query ?  Even as a little bit of debug information?

In Oracle 11g you have to create a SQL*Plus REFCURSOR variable and then bind it within the anonymous block  or  pass it as an argument to a procedure/function. Run the code and then print the refcursor.

This requires a bit of know how in SQL*Plus and how it will work with your PL/SQL block or procedure.

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.

view counter