• About Heli

HeliFromFinland

~ Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

HeliFromFinland

Monthly Archives: February 2016

Invisible Column

24 Wednesday Feb 2016

Posted by Helifromfinland in Oracle

≈ 3 Comments

I like it! Have you ever been in a situation you should add a column to a table but you cannot because the current application would stop working? I have and many times. Now we can do it immediately and give some time for the developers to fix the application.

This feature, invisible column, was introduced in Oracle Database 12c. An invisible column is a table column that cannot be seen using for instance SELECT * FROM or DESCRIBE command. It cannot be seen with %ROWTYPE attribute in PL/SQL variable declarations either. To be able to see it you must explicitly specify it in queries.

Let’s create EMP table with column ENAME defined as invisible.

 

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10) INVISIBLE,
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
/

 

When inserting rows to a table that has invisible columns, and also to that column, make sure to define the column names. If you do not do that you will get an ORA-00913: too many values error.

If you do not want to insert a value to the invisible column, inserts without column lists can be done. If the invisible column has been defined obligatory there is no problem but if it has been defined mandatory make sure to define the default for it using DEFAULT clause.

Another fun thing with invisible columns is that when you define the column invisible and then visible, the column moves to the last column in the table (logically of course, no physical moves).

ALTER TABLE EMP
MODIFY ENAME VISIBLE;

 

DESC EMP

Name     Null     Type        

——– ——– ————

EMPNO   NOT NULL NUMBER(4)  

JOB               VARCHAR2(9)

MGR               NUMBER(4)  

HIREDATE         DATE        

SAL               NUMBER(7,2)

COMM             NUMBER(7,2)

DEPTNO           NUMBER(2)  

ENAME             VARCHAR2(10)

Sometimes this might be useful for you…

 

Cheers

Heli

Datatypes in Oracle

07 Sunday Feb 2016

Posted by Helifromfinland in Oracle

≈ Leave a comment

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

 

Cheers,

Heli

Tables in Oracle

07 Sunday Feb 2016

Posted by Helifromfinland in Oracle

≈ 2 Comments

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.

 

Cheers,

Heli

 

A new series of posts and a new category

07 Sunday Feb 2016

Posted by Helifromfinland in Oracle

≈ Leave a comment

I have decided to start a new series of blog posts and a new category, Oracle, for them. This category will include features and functionalities I think would be worth knowing when designing Oracle databases and working with them. I hope you find these posts useful.

 

Cheers,

Heli

 

Preparing for Collaborate16!

01 Monday Feb 2016

Posted by Helifromfinland in Events

≈ Leave a comment

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

I will

  • present a keynote at the #WIT luncheon; An Unexpected Journey: Charting a Career Path in Oracle Technology (https://collaborate.zerista.com/event/member?item_id=4275051), remember to register, the seats are limited!
  • present about my favorite topic: Database Designing (https://collaborate.zerista.com/event/member?item_id=4274115)
  • be a panelist on a panel Communications – the Good, the Bad, and the Best led by Michael Abbey (https://collaborate.zerista.com/event/member?item_id=4163949).

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

Cheers,

Heli

 

Subscribe

  • Entries (RSS)
  • Comments (RSS)

Archives

  • October 2022
  • October 2021
  • April 2020
  • October 2019
  • October 2018
  • June 2018
  • December 2017
  • October 2017
  • September 2017
  • July 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • January 2015
  • December 2014
  • November 2014
  • October 2014
  • September 2014
  • August 2014
  • June 2014
  • May 2014
  • April 2014
  • January 2014
  • December 2013
  • November 2013

Categories

  • Data Modeler
  • Database design
  • Events
  • General
  • Oracle
  • SQL Developer

Meta

  • Register
  • Log in

Create a free website or blog at WordPress.com.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • HeliFromFinland
    • Join 48 other followers
    • Already have a WordPress.com account? Log in now.
    • HeliFromFinland
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar