SQLcl Alias Example For Dropping Multiple Objects.

Uncategorized
| Oct 12, 2017
SQLcl allows you to define "named" scripts or statements using the ALIAS command. 
Jeff has a neat example on his blog.
I created the following alias to make it really easy to destroy a database! drop objects.

RM Alias

Dropping tables, views, procedures is a daily occurrence for database developers.

The following SQLcl ALIAS may come in handy when you want to drop lots of objects at the same time. Just specify the

A REST Enabled SQL View!

Uncategorized
| Sep 14, 2017
I put together a small Oracle PL/SQL package which demonstrates how to access REST Enabled SQL service and standard REST end points through PL/SQL and SQL.

It can also dynamically create an Oracle Database View to access information from a REST Enabled SQL request. This view can be referenced in SQL just like any other view but the data is provided from the REST Enabled SQL service which could be running against another database.

Maybe (more...)

Getting Started with REST Enabled SQL

Uncategorized
| Sep 7, 2017
POST a query or DML or DDL or even a short script over HTTPS to ORDS and have ORDS run that on your Oracle Database and have the results returned in JSON. That's the plan.

What You Need To Know

REST Enabled SQL uses Schema Authentication as well First Party Authentication.  



This means you can run SQL against an Oracle Database if the following is true.
  • REST Enabled SQL is enabled in ORDS
  • A (more...)

New! REST Enabled SQL for ORDS

Uncategorized
| Sep 7, 2017

The Oracle REST Data Service provides REST access to your Oracle Database. Tables, predefined queries and PL/SQL blocks can be exposed as RESTful services. This is great when you can foresee what table, query or action you wish to REST enable.

Starting in ORDS 17.3 you can now POST the query, pl/sql or sql*plus statement to ORDS at run time. This new feature is call REST Enabled SQL and it is built into ORDS (more...)

Temporarily Disable a Generated Always As Identity Column

Uncategorized
| Apr 5, 2017
Oracle Database 12c allows you to define a column to be an IDENTITY column.
It can be either  GENERATED ALWAYS or BY DEFAULT.

A GENERATED ALWAYS AS IDENTITY column does not allow you to INSERT a value for it.
When it comes to moving data from one table to another and this type of column is present, there are a few steps to "disable" the GENERATED ALWAYS AS IDENTITY column and after the data move (more...)

SQLcl Aliases & The Invisible Column Trick

Uncategorized
| Nov 11, 2015
SQLcl is the bee's knees.

Problem
Today I had a common problem. I wanted to reposition a column within a table.
"Oh you should use views!" 
"Never reference a table directly!" 
"Column position should be meaningless!"
I hear you shout. Yes but, look at this table definition.

PERSON table











FIRSTNAME,ADDRESS,PHONE,LASTNAME
Doesn't that drive you mad. 

I want the order FIRSTNAME, LASTNAME, ADDRESS, PHONE
Or worse. You decide to (more...)

Migrating To Oracle Using Custom Object Names

Sybase , SQL  Server and other databases allow for long identifier names.
Oracle allows for a maximum of 30 Bytes when naming objects like  users, tables, ...
When it comes to migrating objects to Oracle,  SQL Developer will truncate the object names and resolve clashes with unique names.

Say for example you have two tables called

  • Application_Name_SubArea_Name_SpecificAreaName_Table_Table1
  • Application_Name_SubArea_Name_SpecificAreaName_Table_Table2


Oracle SQL Developer will convert these to

  • Application_Name_SubArea_Name_S
  • Application_Name_SubArea_Name_1
This default new name may not be to your (more...)

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 (more...)

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
Both are procedural languages which provide standard programming language features like control flow, variables, conditions and support (more...)

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

I was debugging a procedure using SQL Developer last week that inserted some rows into a temporary table.  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 (more...)

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 (more...)

Multibyte Offline Data Move

Uncategorized
| Mar 14, 2013
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 (more...)

Cross Connection Query Issue

Uncategorized
| Oct 22, 2012
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

Uncategorized
| Jul 16, 2012
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 (more...)

Migrate to Existing Oracle Users

Uncategorized
| Mar 20, 2012
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 (more...)

Sybase and SQL Server Image Data Move

Uncategorized
| Jan 27, 2012
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 (more...)

JDeveloper Group By Insight Preference

Uncategorized
| Oct 12, 2011
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"/>
(more...)

Very Large Migrations

Uncategorized
| Aug 26, 2011
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 (more...)

Empty Space and Single Space Data Move

Uncategorized
| Mar 2, 2011
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 (more...)

FLOAT data type migration

Uncategorized
| Feb 11, 2011
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 (more...)