Datatypes in Oracle

Each value Oracle database uses must have a datatype specified, whether the element is a column in a table or an argument or a variable in a program code. A data type is either scalar or non-scalar. A scalar type contains an atomic value, for instance Salary is 3000. A non-scalar datatype contains a set of values, for instance PostalCode collection. A datatype defines the domain of the element and how Oracle will treat that element. For instance the datatype for Salary (NUMBER(6,2)) defines that the salary is numeric and it can only contain numeric information. A datatype can be one of these categories:

  • Oracle build-in datatype
  • ANSI, DB2, and SQL/DS Data Types
  • User-Defined Types
  • Oracle-Supplied Types

Oracle build-in datatypes are of type character, numeric, long/long raw, datetime, large object (LOB) and rowid. When creating tables also ANSI, DB2, and SQL/DS Data Types can be used. Both DB2 and SQL/DS are products from IBM. Oracle recognizes the ANSI or IBM data type by its name and converts the data type to the equivalent Oracle data type. User-defined types are datatypes users can define themselves using the tools Oracle provides for this. The Oracle-supplied types are user-defined types that Oracle has defined using the same tools we can use for defining user-defined types. Oracle-supplied types are:

  • Any Types
  • XML Types
  • URI Types
  • Spatial Types
  • Media Types




Tables in Oracle

When designing a database the end results is the DDLs for creating the database elements. For instance the tables. In Oracle there are two kinds of tables: relational and object tables. There are different kinds of relational tables: heap-organized table, index-organized table and external table. A table can be either permanent or temporary.

The regular table is a heap-table where the data is saved in a heap without no particular order. In Oracle Database 8.0 the index-organized table was introduced. An index-organized table is an index structure ordered by the primary key: each leaf block in the index structure stores both the key and non-key columns (the actual data). Since Oracle Database 9i there been a table type called external table. An external table is stored outside the database.

An object table is one of the object-relational features that were added to Oracle Database to support object-oriented functionalities introduced in object-oriented databases in 1990’s. In an object table each row represents an object or implementation of it. Object table can be defined using user-defined types, possibly supertypes and subtypes. Later we will talk about how to do this using Data Modeler.

All these table types described earlier are permanent tables where the data stays saved on a disc until somebody deletes it permanently. There is also another kind of table type: a temporary table. The data in those tables are saved only temporarily and the data is deleted automatically either after the transaction or a session. The definition of a temporary table is not dropped automatically; it remains until somebody deliberately drops it. A temporary table is meant to be used when you need to temporarily store a set of rows to be processed against other tables or temporary tables. Temporary tables are very useful if you need the result set in many queries, for instance when you need to update several tables using the data in a temporary table. Do not use it to split a query to smaller queries, it will be less efficient than the original query since Oracle database is specialized in performing queries in a database and will do them as efficiently as possible.





Preparing for Collaborate16!

I am so excited to be attending the Collaborate the first time in my life!

I will

It will be such a busy week but so exciting! I really hope to see you there!




My plans for 2016

To me 2016 looks very exciting. I will start my doctoral studies at Helsinki University. I will be studying and researching Big Data, semistructured data and schema discovery. That research is related to the work I have done more than 20 years but it is also something completely new. I am really excited!

Another interesting new thing is that I have been invited to speak in many conferences just like Kellyn Pot’Vin-Gorman predicted (@dbakevlar) :-) and I am so glad about that because as a typical Finn I am not very good in abstracts and convincing I should be selected… I am really sorry I have said NO to some of those invitations :-( I hope I can say YES next time…

In 2016 I will have completely new topics and areas as well as my “old” topics and areas to talk about. Topics for 2016 include SQL Developer, WIT, cloud, APEX and I am sure that later this year I cannot stop myself from talking about Big Data, schema discovery and other things I learn and study during this year. Now I am studying Machine Learning…. Of course I will be talking about database designing and Oracle SQL Developer Data Modeler!

So far I am planning to talk in these events in 2016:

GaOUG Tech Day 2016, March 3 in Atlanta, Georgia, USA

OUGN, March 10-12, Oslo, Norway

Ari Hovi Oy April 7.-8., Tietokannan suunnittelu Data Modelerilla, Helsinki, Finland

Collaborate, April 10-14, 2016, Las Vegas, USA

OUGF, EXA-SIG, April 21, Helsinki, Finland

APEX Connect 2016, 26.-28.4.2016 Berlin, Germany

AMIS 25 – Beyond the Horizon, June 2-3

ODTUG KScope16, June 26-30, Chicago, USA


And hopefully by OOW16 a book I have been coauthoring will be out, it is about SQL and PL/SQL.


Have a wonderful end of 2015 and a very happy and joyful 2016! Hope to see you in 2016!


Data Modeler 4.1.3 is OUT

The development team has been busy! 4.1.3 is now out and plenty of bugs fixed.

Bugs fixed (from the download pages):

22350565 String index out of bounds exception while importing structured type method bodies
22311829 Table report generating duplicate FK columns
22350611 Cannot select relationship/foreign key line
22339253 Change request report does not generate data in the report
22187312 Error when running design rules using Data Modeler version

Year 2015, wrap-up

I know, I know, 2015 is not over yet :-D

But I was thinking to wrap-up now so I can continue planning 2016 ;-) 2016 will be sooooo exciting….


2015 was definitely the busiest year ever but I might as well say that it was a great year. My first book came out, I have been writing my second book (it should be out by OOW16) and speaking in (at least) 24 events. Please tell me if your event is not on this list, I have totally lost the track:

Oracle Cloud Day (2.12.2015), Helsinki, Finland

BGOUG (20.-22.11.2015), Pravets, Bulgaria

DOAG (17.-19.11.2015), Nuremberg, Germany

Oracle OpenWorld (25.-29.10.2015), San Francisco, USA

HrOUG (13.-16.10.2015) , Rovinj, Croatia

SIOUG 2015 (12.-13.10.2015) , Portoroz, Slovenia

Oracle Innovation Day (7.10.2015), Tallinn, Estonia

OUGF, Nordic ACE Director Tour (15.9.2015) , Helsinki, Finland

OTN LAD Tour 2015

  • 14.8. Mexico
  • 17.8. Guatemala
  • 19.8. El Salvador
  • 21.8. Panama
  • 24.-25.8. Ecuador

Kscope15 (21.-25.6.2015), Hollywood, Florida ,USA

BGOUG (12-14.6.2015), Plovdiv, Bulgaria

ilOUG Tech Days (9.-10.6.2015), Israel

OTN EMEA Tour 2015

  • Portugal 21.5.2015
  • Jordan 23.5.2015
  • South Africa 26.5.2015

Wildcard Unconference (16.5.2015), Riga, Latvia

Oracle Expert Summit (21.-24.4.2015), Bucharest, Romania

RoOUG 22.4.2015, Bucharest, Romania

OUGN (12.-14.3.2015), Norway

Riga Dev Day (22.1.2015), Riga, Latvia

In 2015 I was re-elected as the Ambassador for EOUC (EMEA Oracle Users group Community) for the next two years. At the OOW I was jumping to the cloud in Larry’s Keynote, met Mark Hurd and was awarded with a Oracle Database Developer Choice Award in Database Design category. And in December I was listed as the 12th of 100 TOP influencers on IT in Finland (, Just to mention some of the highlights of 2015.

I want to thank everybody who has bought my book, attended my sessions, invited me to speak and especially those wonderful collegues of mine who have been travelling with me around this beautiful globe. And of course the biggest THANK YOU to Roland, Vikki, Jennifer and the whole ACE Program and OTN for supporting many of my trips. Without your support this would not have been possible.


Thank you and have a wonderful December!


Oracle Cloud Day, Helsinki

My last presentation this year was extremely interesting: it was both my first presentation (EVER!) about cloud as well as APEX. I was speaking at the Oracle Cloud Day in Helsinki, Finland on a real customer case on building APEX applications on Oracle Cloud. Thank you Carus for letting me tell your story!


Thank you for attending the session and thank you so much for all the positive feedback! I guess I will keep on talking about both Cloud and APEX :-)





My last trips this year were two amazing conferences: DOAG in Germany and BGOUG in Bulgaria. I am not sure if there would be a better way to end your travelling year!

Thank you very much for having me and thank you so much for the great hospitality! And please forgive me this blog post only comes out now: I have been far too busy lately.


Best wishes,




Data Modeler is out!

Data Modeler is out with plenty of important bug fixes. Go and try!

From the download pages:

Oracle SQL Developer Data Modeler Bugs Fixed

Date: December 2015

This list contains only a selection of higher priority bugs, customer bugs and issues raised by the community on the OTN Forum since the release of version 4.1.2.

22109015 Table structure not available in browser for versioned designs
22120662 “Oracle errors to mask” tab is always disabled
22112910 Table has columns with undefined security properties’ for xmltype column
22186607 Constraint definition is missing in ddl when custom ddl script is used
22187312 Error when running design rules using Data Modeler version




Get every new post delivered to your Inbox.