Wednesday, 5 April 2017

Temporarily Disable a Generated Always As Identity Column

Oracle Database 12c allows you to define a column to be an IDENTITY column.

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 "enable" it.

set  echo on
drop table test_identity;
create table test_identity(id number generated always as identity);

--This insert will fail as the column is generated always 
--and it does not accept values
insert into test_identity(id) values(1);

--Change the identity column to generated by default. 
--This will allow us to insert our values
alter table test_identity modify id  generated by default as identity;

--This insert works. as will Copy to Oracle
insert into test_identity(id) values(1);
select * from test_identity;

--Change the identity column back to generated always. 
--And set its start value to the max id in the table + 1.
alter table test_identity modify id generated always as identity 
(START WITH limit value) ;

--test this fails as expected because the identity is back to generated always
insert into test_identity(id) values(1);

--test that the identity generates a value after the values inserted 
--and not a duplicate. so the table should have two rows 1 and 2.
insert into test_identity(id) values(DEFAULT);
select * from test_identity;

I may write a SQLcl Alias to perform a GENERATED ALWAYS AS IDENTITY "disable" and "enable"

Wednesday, 11 November 2015

SQLcl Aliases & The Invisible Column Trick

SQLcl is the bee's knees.

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

Doesn't that drive you mad. 

Or worse. You decide to add a new column  (middlename) to your table definition script.
create table person (firstname varchar2(100), 
                           middlename varchar2(100),
                                 lastname varchar2(100), 
                                   address varchar2(100), 
                                     phone varchar2(100));

But you need to update the table right now
alter table person add (middlename varchar2(100));
Your table definition script will generated a different table from your live table as the live tables "middlename" will be the last column... 

You could perform CTAS, drop the old table and rename the new table. But what about its triggers, indexes, ... 

Luckily in Oracle 12c  INVISIBLE columns  have a neat side effect.
Ask Tom goes into the details, but the gist of is 
  • If you make a column invisible it is logically ordered as the last column. 
  • By making some of the columns invisible  it allows you to position a column where you want.
  • Afterwards make the columns visible again. The new ordering is retained.
Shifts those columns to the end, making the middlename column the second column. Make them visible again and you have logically reordered the table columns.

SQLcl Alias & The Invisible Column Trick
How about we write a command in SQLcl to do this for us?
That's where ALIAS comes in. You can name a query,PL/SQL or script and provide it arguments to match bind variables.

Done! Save the pos.xml to your local drive.

Open SQLcl  and load the alias

alias load c:\pos.xml

Now you can change column positions (At your own risk! This is just an example code. What happens if you already have INVISIBLE columns? What happens if your column names are case sensitive ???  I have more work to do before production)

pos tablename columname position

Monday, 23 March 2015

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 liking, so SQL Developer has an easy way of changing this mapping.

First perform the Capture and Convert phases. You do not need to perform all the phases of a migration as the migration wizard allows you to stop and start a migration at many points. In this case click "Finish" on the Convert page of the migration wizard.

Once the capture and convert is complete, we will have a list of all the objects to be migrated including their original "source" name and their default "target" name. But we have yet to translate SQL objects like procedures and views. This is important...

Left click on the  "Converted Database Objects" node in the migration navigator and choose the "Object" report tab. This report allows you to change the "target" name of any of the objects. It also allows you to sort and filter the objects by types. Once you have made and committed the changes you can proceed with the migration.

Right click on the "Converted Database Objects" node and choose "Translate". 

This will pop you back into the migration wizard at the correct page so you can continue the migration.
The neat thing about this is, now the Translation will take into account you chosen target names. 

Choosing a specific Target user is a common requirement.

For example: By default bugtestcase2.dbo is mapped to bugtestcase. But you many have an existing user you want to use. You can use the same "Objects" report to change the target user.

You should also choose "Offline" option during the generation and data move. This is so you can provide the existing password of your existing users.

You'll end up with a  CREATE USER in your generation script. But you can comment this out or ignore the error. Just be sure to give the existing password when prompted for it.

Tuesday, 23 December 2014

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.

In Oracle 12c a new feature called IMPLICIT RESULT SETS allows you to pass back a result set without parameters or external binds. When calling from SQL*Plus or SQL Developer Worksheet , this means we do not have to create REFCURSOR variables.

It was initially developed to help migrating from Sybase and SQL Server to Oracle. But its a nice feature which may help you in your day to day.

Explicit Result Sets in 11g

 Implicit Result Sets in 12c

In Oracle 12c the cursor is defined as variable not a parameter. This cursor variable can be "reused" to pass a cursor value into DBMS_SQL.RETURN_RESULT.  Clients which support Implicit Results Sets (like SQL*Plus and SQL Developer) will handles these implicitly for you.

There are pros and cons to this.
1) Not all clients/drivers support it.
2) Using Explicit parameters is very good practice as it denotes what the procedure is going to return.
3) Its MAGIC!

1) You do not need to know how to define, bind/assign or print SQL*Plus variables.
2) You do not have to change the procedure parameters. Especially useful if you want to return multiple result sets.
3) Its MAGIC!

Its handy in an anonymous block as well. No fuss!

Wednesday, 22 January 2014


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.

Both are procedural languages which provide standard programming language features like control flow, variables, conditions and support embedded DML SQL (INSERT,UPDATE,DELETE,SELECT) statements.

Many of the structures found in T-SQL have a one to one mapping to similar structures in PL/SQL.
Sometimes there are differences in syntax and features.

Using SQL Developers Scratch Editor, T-SQL can be automatically converted to the corresponding Oracle PL/SQL. Typically procedures are migrated using the Migrate to Oracle feature, but the Scratch Editor provides a neat editor to type T-SQL on the left hand pane and have it automatically converted to Oracle PL/SQL on the right.

Some of the obvious differences  are the T-SQL variables/parameters are denoted with '@' . This makes them distinct from column names. Oracle variables/parameters do not use this syntax.

Another obvious difference is the T-SQL query result set is passed back implicitly without the need for an explicit returned value. Oracles normal practice is to return the cursor explicitly through the procedures parameters.

In Oracle 12c, implicitly returned cursors are now supported through DBMS_SQL.RETURN_RESULT.
This makes migrating procedures a lot simpler as the procedure header does not change and therefore neither do its calls.

Many of the mappings between T-SQL and PL/SQL are handled automatically by Oracle SQL Developer.

Sometimes its not obvious why one construct is converted to another. The following outlines the some of the more common conversions.

Object Naming
SQL Server allow identifiers (table names, column names,..) to be up to 128 characters. Oracle has a limit of 30 bytes. SQL Developer will truncate and resolve collisions of objects names so that valid object names are referenced in the PL/SQL.

Embedded DDL 
DDL , ex: CREATE TABLE, can be embedded and then referenced within T-SQL.
DDL cannot be directly embedded in PL/SQL but there are two choices.
1) Take the DDL out of the  procedure and create it up front as a separate object.
2) Use dynamic sql (EXECUTE IMMEDIATE) to create the DDL from within the procedure. But then all references to it have to be withing dynamic sql as well.

Temporary Tables tend to be the most common DDL defined within T-SQL.
SQL Developer takes these definitions  out of the procedures and convert them into Oracle Global Temporary Tables.

String Concatenation
T-SQL use the + operator for string concatenation and Oracle uses ||.
When converting from T-SQL to PL/SQL the expression data types need to known to choose the correct operator in oracle. SQL Developer can lookup the table definition to decide what data type the columns are.

T-SQL allows UPDATE and DELETE statements to have a FROM clause.
Oracle SQL does not allow a FROM clause in an UPDATE statement.
But the same functionality can be defined using a sub query or a MERGE clause.
SQL Developer will automatically convert these types of statements to a MERGE statement.

Empty String
Empty string literals '', means different things in different databases.
Sybase treats an empty string literal '' as a single space ' '.
SQL Server treats an empty string literal '' as an empty string literal ''
Oracle treats an empty string literal '' as a NULL.
During the conversion of T-SQL if an empty string literal is used , it is converted to a single space string literal.
Empty Strings are also converted to single spaces when moving data from SQL Server to Oracle.

Dynamic SQL
Dynamic SQL, or SQL defined within strings are not converted. Dynamic SQL typically isn't a complete statement, instead they tend to be strings built from constants, parameter values, column values ... , these substring of a SQL statement, are not translated automatically by SQL Developer. Instead they have to be reviewed and manually converted.

SQL Developer can be of some help though.
If you can manually put together an example of the complete T-SQL statement, the Scratch Editor can be used to convert it to PL/SQL.

Helping you manually convert the procedure.

  OPEN v_cursor FOR  'select ' || ' * from t1 FETCH FIRST 2 ROWS ONLY';

If the dynamic SQL is not directly available to translate, 12c provides a new feature SQL Translation Framework, which allows you to translate non Oracle SQL statements to Oracle on the fly.

It requires installing SQL Developers translator into the Oracle database  and creating a new SQL Translation Profile. The procedure can then use dbms_sql to parse and execute the query and have the dynamic SQL translated by the translator on the fly.

create or replace PROCEDURE proc1( v_cursor  OUT SYS_REFCURSOR)
  c integer;
  n number;
  v number;
 c := dbms_sql.open_cursor();
    dbms_sql.parse(c, 'select TOP 2 * from t1', dbms_sql.foreign_syntax);
    n := dbms_sql.execute(c);
  v_cursor := DBMS_SQL.TO_REFCURSOR(c);

Monday, 7 October 2013

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.

1) Create this procedure in your schema
  v_count INT;
  v_qryCtx DBMS_XMLGEN.ctxHandle;
  v_result CLOB;
  EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO :v_count FROM '||p_tablename INTO v_count;
  DBMS_OUTPUT.PUT_LINE('tablename='||p_tablename||' : rowcount='||v_count);
  --query table rows into XML
  v_qryCtx := DBMS_XMLGEN.newContext('SELECT * FROM ' ||p_tablename || ' WHERE ROWNUM <='||p_numrowstoinspect);
  -- Get the result
  v_result := DBMS_XMLGEN.getXML(v_qryCtx);
  DBMS_OUTPUT.PUT_LINE('--BEGIN DEBUGINFO RESULT FOR '||p_tablename||CHR(10)||CHR(10)||v_result||CHR(10)||'--END DEBUGINFO RESULT FOR '||p_tablename);
2) Call DEBUGINFO from within the procedure your testing.
Pass the tablename and number of rows to inspect

3) Step into DEBUGINFO during debug to view "live" values.
V_COUNT contains the number of rows in the table
V_RESULT contains the table data (for the number of rows to inspect)

4) Review the log window for DBMS_OUTPUT
Result available after the procedure finishes.

Tuesday, 20 August 2013

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.

Instead of making a connection to you database using a SQL Developer JDBC connection and then extracting the database metadata over the connection, you can run a script provided by SQL Developer which extracts the database metadata to files. These files can be used to perform the capture, making it easy to provide the database metadata to others without a connection.

Script Creation

The Offline Capture scripts can be acquired using SQL Developer.
Tools > Migration > Create Database Capture Scripts ...

Choose where you want to place the scripts, Windows or Linux format and which Platform
( SQL Server, MySQL, Sybase, DB2, Teradata)

Running Scripts


The scripts will use the tools of  non Oracle database(ex: BCP in Sybase and SQL Server, db2 for DB2,... ) to export the metadata (table definitions, procedure definitions, ..., not the table data) out to flat dat files on the disk. So it is essential that those "client" tools are available when running the scripts.  

The scripts also require a highly privileged database owner  (ex: sa for Sybase and SQL Server) so that the metadata tables can be read and their data extracted.

  • Disk space is usually not an issue as only the metadata of the database is extracted at this stage, not the table data.
  • SH files may need to made executable before being run

Running Scripts

The scripts have to be run by hand from the command line. The user supplies a privileged login into the database as an argument and the name of the database to extract its metadata.

SYBASE 15 Windows Example
Arguments: username password databasename servername

Note, in SQL Server and Sybase that two directories will be created.
  • Metadata from the databasename chosen
  • Metadata from the master database
This master database metadata is the same for all databases on this server.
If you want to offline capture databases from different servers, then you should create a directory for each server and copy/run the offline capture scripts from each directory.

Extract One Database
SYB15_OFFLINE_CAPTURE sa sa_password pubs2  servername

The result should be something like this

Extract Multiple Databases From the Same Server
SYB15_OFFLINE_CAPTURE sa sa_password database1  servername
SYB15_OFFLINE_CAPTURE sa sa_password database2  servername

The result should be something like this.

Extract Multiple Databases From Different Servers
From Directory server1
SYB15_OFFLINE_CAPTURE sa sa_password database1  servername1
SYB15_OFFLINE_CAPTURE sa sa_password database2  servername1

The result should be something like this.

From Directory server2
SYB15_OFFLINE_CAPTURE sa sa_password database3  servername2
SYB15_OFFLINE_CAPTURE sa sa_password database4  servername2

The result should be something like this.

The following are the expected files in a database directories and the master directory for Sybase 15.
master DIR

database DIR (ex: pubs2)

Now the metadata is on disk, the next step is to package it up to send it to the migrator.

Packaging Metadata

The file names, directories names and structure are important. Do not change the names of any files of directories or move their positions. All the files are required for SQL Developer to perform the  offline capture. Best to zip up the entire directory including scripts, metadata, directories without any changes.
When zipping up the files, make sure to keep the directory structure, so when unzipped the same directory structure is reproduced.

Offline Capture of Files

Unzip the files provided so that they are accessible to SQL Developer.
Create a brand new migration project. Tools > Migration > Migrate to Oracle

Follow the migration wizard.On Step 4 (Source Database)
  • Choose Mode Offline.
  • Choose the *.ocp file. 
Continue through migration

Note that SQL Developer requires a new project for each Server. So if you are migrating multiple servers, you will have to have separate migration projects, and have to perform the offline capture for each server.

Hopefully this makes offline capture a little easier to follow. Ill place up some examples of different databases soon.

Thursday, 14 March 2013

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" 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  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",  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

Monday, 16 July 2012

DBMS_OUTPUT manipulation and filtering in SQL*Plus

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

SET LONG 1000000000 ;

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

  --array to save the output lines into
  outtab dbms_output.chararr;
  outstr VARCHAR2(255);
  -- 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
                            length(outstr||chr(10)||'my message'||chr(10)),
                                   outstr||chr(10)||'my message'||chr(10)); 
      END IF;                    
print outputclob;

Tuesday, 20 March 2012

Migrate to Existing Oracle Users

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

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.

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.