In this article, we will learn the top Oracle 12c new features for developers.
- Top N Queries:
This is a way of limiting the number of rows returned to the first ‘N’ meeting the relevant criteria. For example you might wish to retrieve the top 10 highest paid employees.
Before Oracle 12c there were several ways of doing this. You could use a sub query like this:
select * from ( select employee_id ,first_name||' '||last_name emp_name ,salary from employees order by salary desc ) where rownum < 11
You can also see multiple ways in below link:
http://techkatak.com/nth-highest-salary-in-oracle-interview-question/
For this Oracle 12c new feature, Oracle introduced a new syntax.
select employee_id ,first_name||' '||last_name emp_name ,salary from employees order by salary desc fetch first 10 rows only
This is much simpler but returns the same results as the original query.
If we replace the word “only” with the phrase “with ties” we will return more than the specified number of rows if the nth value has 2 or more rows with the same value.
What we need to do if we need next 10 rows?
We have to use a variation of this Oracle 12c new feature – the offset clause:
select employee_id ,first_name||' '||last_name emp_name ,salary from employees order by salary desc offset 10 rows fetch next 10 rows only
Instead of specifying a fixed number of rows to fetch we can specify a percentage but unfortunately we can’t specify a percentage for the offset, so the the following is NOT valid:
select employee_id ,first_name||' '||last_name emp_name ,salary from employees order by salary desc offset 5 percent rows fetch next 5 percent rows only
but you can retrieve a percentage of rows using below query:
select employee_id ,first_name||' '||last_name emp_name ,salary from employees order by salary desc offset 10 rows fetch next 5 percent rows only
2. Invisible Columns
Invisible column is introduced in Oracle 12c. Don’t get confused with virtual column introduced on 11g.
A virtual column is effectively a function-based column – its value is derived from the values of other columns in the table and the value is not stored in the Oracle database.but the values of invisible columns are stored in Oracle (unless they’re virtual invisible columns) but the columns are not displayed unless explicitly named in a query and also have to be referred explicitly in insert statements.
Let’s create a table with an invisible column:
SQL> create table emp 2 (emp_id number 3 ,emp_name varchar2(100) 4 ,salary number INVISIBLE);
When we describe the table the invisible column isn’t shown.
sql> desc emp
Name Null Type
EMP_ID NUMBER
EMP_NAME VARCHAR2(100)
You can change this default behaviour in SQL*Plus by typing
SQL> set colinvisble on
Querying a table with invisible column:
When we query the table, unless the invisible columns are named in the query they are not displayed.
SQL> select * from emp;
EMP_ID EMP_NAME
No data is displayed as the table is empty.
When we specify the invisible column in the query it is displayed
SQL> select emp_id,emp_name,salary from emp;
EMP_ID EMP_NAME SALARY
Again, no data is displayed as the table is empty.
Inserting data into Table with an Invisible Column:
When we insert data into the table we must either explicitly include the invisible column in the insert statement or not provide a value for it.
SQL> insert into emp values (1,'A') –THIS IS VALID
SQL> /
1 row created
In the above insert statement we didn’t specify the columns and only provided values for the twovisible columns.
SQL> insert into emp (emp_id, emp_name, salary)
2 values (3,'B',30000) -- THIS IS ALSO VALID
SQL> /1 row created
In the above insert statement we specified the columns and provided values for the two visible columns and the invisible column.
SQL> insert into emp values (2,'C', 20000)--INVALID
SQL> /
insert into emp values (2,'C', 20000);*
ERROR at line 1:
ORA-00913: too many values
In the above insert statement we didn’t specify the columns but provided values for the two visible columns and the invisible column, resulting in an error.
Querying Data Dictionary Views:
Even though the invisible columns are not displayed by default when we describe the table, they are shown. when we query data dictionary views such as all/dba/user_tab_columns.
SQL> select column_name from user_tab_columns
2 where table_name='EMP';column_name
EMP_ID
EMP_NAME
SALARY
Changing Visibility of Column:
Like other column properties, the visible/invisible property can be changed by using the alter table command:
SQL> desc emp
Name Null Type
EMP_ID NUMBER
EMP_NAME VARCHAR2(50)
SQL> alter table emp modify salary visible;
Table altered.
SQL> desc emp
Name Null Type
EMP_ID NUMBER
EMP_NAME VARCHAR2(50)
SALARY NUMBER
The other new features on Oracle 12c, you will see in below link:
Oracle 12c new features (Part – 2)
© 2015, https:. All rights reserved. On republishing this post, you must provide link to original post