Dermot's Tech Tips

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

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 add a new column  (middlename) to your table definition script. create table person (firstname varchar2(100),                             middlename varchar2(100),                                  lastname varchar2(100),                              ...

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 conve...

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 n...

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 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...

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 --COMPILE THIS WITH DEBUG AND STEP INTO IT IF YOU WISH TO SEE RUN TIME VALUES CREATE OR REPLACE PROCEDURE DEBUGINFO(p_tablename VARCHAR2,p_numrowstoinspect NUMBER DEFAULT 10) AS v_count INT; v_qryCtx DBMS_XMLGEN.ctxHandle; v_result CLOB; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO :v_count FROM '||p_tablename INTO v_count; DBMS_OUTPUT.PUT_LINE('tablename='||...

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) Runni...