IDENTITY column in Oracle

Prior to Oracle 12c , there was no direct equivalent to Sybase / SQL Server / PostgreSQL  identity column . This enhancement is available in 12c. 

Under the hood , Oracle creates sequence and associate it to the base table.  The  sequence name is tagged as "ISEQ$$_XXXXX"  , where XXXX is the object id of the base table.   The record is available in xxx_sequences ( all / user / pdb) views , till the recycle bin is purged . 

To create a table with the identity column , the user needs to "CREATE TABLE" and "CREATE SEQUENCE" privilege . 

Datapump has been enhanced to accommodate this enhancement as well. Here is an example. 




SQL> show user
USER is "ZAHIR"
SQL>

SQL> Create table t_identity
  2  (  empid int GENERATED BY DEFAULT ON NULL AS IDENTITY ,
  3     empname varchar2(20)
  4     ) ;
Create table t_identity
*
ERROR at line 1:
ORA-01031: insufficient privileges

C:\Users\mohideen>sqlplus system@pdb_test

SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 20 13:04:13 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Wed Apr 20 2016 12:42:47 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant create sequence to zahir ;

Grant succeeded.

SQL> conn zahir/password@pdb_test
Connected.
SQL>
SQL> Create table t_identity
  2  (  empid int GENERATED BY DEFAULT ON NULL AS IDENTITY ,
  3     empname varchar2(20)
  4     ) ;

Table created.

SQL> col object_name format a20
SQL> Select  object_name , object_id , object_type  from user_objects where object_name like 'T_IDENT%';

OBJECT_NAME           OBJECT_ID OBJECT_TYPE
-------------------- ---------- -----------------------
T_IDENTITY                92733 TABLE

SQL> col sequence_name format a20
SQL> Select  sequence_name , min_value  from user_sequences ;

SEQUENCE_NAME         MIN_VALUE
-------------------- ----------
ISEQ$$_92733                  1

SQL> drop table t_identity;

Table dropped.

SQL> Select  sequence_name , min_value  from user_sequences ;

SEQUENCE_NAME         MIN_VALUE
-------------------- ----------
ISEQ$$_92733                  1

SQL> purge recyclebin ;

Recyclebin purged.

SQL> Select  sequence_name , min_value  from user_sequences ;

no rows selected

Accessing Postgresql / MySQL / MS SQL Server from Oracle.

Gone are the days , where a business has only one DBMS to maintain its operations.

Most of the times , you are dealing with multiple DBMS in your organization. 

The vendors provide some means of connecting to distributed databases . For instance ,

Oracle provides "database link"  ( via transparent gateway), MS SQL Server provides "Linked Server" mechanism to connect to heterogeneous databases and other data sources.

Here , we will look at connecting to PostgreSQL from Oracle by using transparent ODBC gateway .  This process is applicable any ODBC complaint data sources . 

Oracle does provides transparent gateways for MS SQL Server , DB2 . But those needs to be licensed.

As of today , ODBC transparent gateway is not separately licensed . Please check with your rep for the licensing details.

Here are the steps.

Step 1 :

Configure ODBC connection and a create a system DSN for PostgreSQL

Let us call it as "pgsales”

Note : This needs to be configured as "System DSN"  , not as User DSN.

Step2 :

Create a file under %ORACLE_HOME%\hs\admin in the form initpgsalesodbc.ora . The naming convention is “init” followed by  desired name . In my case , I have it as  initpgsalesodbc.ora. 

Set the HS_FDS_CONNECT_INFO to the DSN name that was configured in ODBC setup . 

I would recommend to keep the trace level to 'ON' for the initial setup . Once the setup is complete ; connectivity is established ; we can turn off the trace .  

Oracle provides sample file . We can use clone this file to suit our needs.  

Here are the contents of initpgsalesodbc.ora.

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = pgsales

HS_FDS_TRACE_LEVEL =  ON

#

# Environment variables required for the non-Oracle system

#

#set =

Step 3 :

Add a entry in listener.ora to register this data source.   The SID name must match with the init file created in the above step.

      (SID_DESC =

              (SID_NAME = pgsalesodbc)

              (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)

              (PROGRAM = dg4odbc)     

    )

Step 4: 

Add entry in tnsnames.ora . The SID  in connect_data should match SID defined in the listerner.ora and the file in hs\admin. 

pgsqllink  =

  (DESCRIPTION=

 (enable=broken)

    (ADDRESS=(PROTOCOL=tcp)(HOST=ZAHIRSER)(PORT=1521))

    (CONNECT_DATA=(SID= pgsalesodbc))

    (HS=OK)

  )

Step 5:

Restart the listener.You should see to make sure that the listener is servicing the services we created.

Service "pgsalesodbc" has 1 instance(s).

  Instance "pgsalesodbc", status UNKNOWN, has 1 handler(s) for this service...

Step 6: 

Create a database link .  

In the following example , LINK2PGSQL is our db link , 'zahir' is the user id in the postgresql database and "passwd1234" is the password . 

 

SQL> CREATE DATABASE LINK LINK2PGSQL   CONNECT TO "zahir" IDENTIFIED BY "passwd1234" USING 'pgsqllink'  ;

 Database link created.

 

To test the connectivity , issue a select statement as below . Please note , for Postgresql databases  , you will need to enclose the table/view names in quotes.

 

SQL> select * from "t"@LINK2PGSQL;

c

--

A

B

C

With this feature , we can manipulate the data stored in heterogeneous databases . 

Comments Welcome .

 

Rolling Back a DDL

Not all the RDBMS are same .  Even though , there have  similar syntax / features  , they differ how they handle transactions  / locking / DDLs . 

In Oracle , DDLs  does implicit commit;  But in SQL Server , the DDLs can be rollback , if necessary. 

Moral of the story - Every RDBMS has own way to doing things.  We can't assume , if a particular feature works in one database  , it will work in other database in the same way. 

Sometimes , it helps us to unlearn some of the stuff you have learnt for one database and go from basics. 

Here is an example . 

In Oracle 

SQL> Create Table emp
  2  ( id int ,
  3    name varchar(50)
  4    ) ;

Table created.

SQL>     alter table emp add sal numeric(9,2) ;

Table altered.

SQL> rollback;

Rollback complete.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(50)
 SAL                                                NUMBER(9,2)


In SQL Server


C:\>sqlcmd -W
1> use test
2> go
Changed database context to 'test'.
1> Create Table emp
2> ( id int ,
3>   name varchar(50)
4>   ) ;
5> go
1> sp_help emp
2> go
Name Owner Type Created_datetime
---- ----- ---- ----------------
emp dbo user table 2016-04-04 12:27:01.283


Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
----------- ---- -------- ------ ---- ----- -------- ------------------ -------------------- ---------
id int no 4 10    0     yes (n/a) (n/a) NULL
name varchar no 50             yes no yes SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment Not For Replication
-------- ---- --------- -------------------
No identity column defined. NULL NULL NULL

RowGuidCol
----------
No rowguidcol column defined.

Data_located_on_filegroup
-------------------------
PRIMARY

The object 'emp' does not have any indexes, or you do not have permissions.

No constraints are defined on object 'emp', or you do not have permissions.

No foreign keys reference table 'emp', or you do not have permissions on referencing tables.
No views with schema binding reference table 'emp'.
1>
2>  begin transaction
3>     alter table emp add sal numeric(9,2) ;
4> go
1> sp_help emp
2> go
Name Owner Type Created_datetime
---- ----- ---- ----------------
emp dbo user table 2016-04-04 12:27:01.283


Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
----------- ---- -------- ------ ---- ----- -------- ------------------ -------------------- ---------
id int no 4 10    0     yes (n/a) (n/a) NULL
name varchar no 50             yes no yes SQL_Latin1_General_CP1_CI_AS
sal numeric no 5 9     2     yes (n/a) (n/a) NULL

Identity Seed Increment Not For Replication
-------- ---- --------- -------------------
No identity column defined. NULL NULL NULL

RowGuidCol
----------
No rowguidcol column defined.

Data_located_on_filegroup
-------------------------
PRIMARY

The object 'emp' does not have any indexes, or you do not have permissions.

No constraints are defined on object 'emp', or you do not have permissions.

No foreign keys reference table 'emp', or you do not have permissions on referencing tables.

No views with schema binding reference table 'emp'.



1> rollback
2> go
1> sp_help emp
2> go
Name Owner Type Created_datetime
---- ----- ---- ----------------
emp dbo user table 2016-04-04 12:27:01.283


Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
----------- ---- -------- ------ ---- ----- -------- ------------------ -------------------- ---------
id int no 4 10    0     yes (n/a) (n/a) NULL
name varchar no 50             yes no yes SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment Not For Replication
-------- ---- --------- -------------------
No identity column defined. NULL NULL NULL

RowGuidCol
----------
No rowguidcol column defined.

Data_located_on_filegroup
-------------------------
PRIMARY

The object 'emp' does not have any indexes, or you do not have permissions.

No constraints are defined on object 'emp', or you do not have permissions.

No foreign keys reference table 'emp', or you do not have permissions on referencing tables.
No views with schema binding reference table 'emp'.