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

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

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

 

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