Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

Useful 12c new features for developers

Mustafa, 2019-02-252019-02-27

Hello Everyone,

I just upgraded one of my customers database to 12c R2 and it has really nice features so I wanted to share some of them for developers.

  1. Top N rows:
    this is a late feature but finally arrived. Before 12c if we want to first N rows from an ordered dataset we would have to write something like this:
    Oracle PL/SQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    Select Employee_id, First_name, Salary
    from   (select Employee_id, First_name, Salary
            from Hr.Employees
            order by salary desc)
    where rownum <= 10;
     
    --or as a cooler version
     
    Select Employee_id, First_name, Salary
    from   (select Employee_id, First_name, Salary, Row_number() Over(order by Salary desc) row_order
            from Hr.Employees)
    where row_order <= 10;

    with 12c we can finally can get rid of that subquery with new FETCH clause. Equivalent version of sql’s above in 12c is:
    Oracle PL/SQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    Select Employee_id, First_name, Salary
    from   Hr.Employees
    order by salary desc
    Fetch first 10 rows only;
     
    --also you can skip first X rows
     
    Select Employee_id, First_name, Salary
    from   Hr.Employees
    order by salary desc
    offset 5 rows Fetch first 10 rows only;

    if you check execution plan for this new query you will see that it uses Row_Number() analytic function to fetch rows:
    Oracle PL/SQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    explain plan for
    Select Employee_id, First_name, Salary
    from   Hr.Employees
    order by salary desc
    Fetch first 10 rows only;
     
    --explained
     
    select * from table(dbms_xplan.display());
     
    --output:
    --------------------------------------------------------------------------------------
    | Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |           |    10 |   640 |     4  (25)| 00:00:01 |
    |*  1 |  VIEW                    |           |    10 |   640 |     4  (25)| 00:00:01 |
    |*  2 |   WINDOW SORT PUSHED RANK|           |   107 |  1605 |     4  (25)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL     | EMPLOYEES |   107 |  1605 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
       2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC
                  )<=10)
  2. Table Column Default On Null:
    Before 12c we can assign a default value to a column but this will work only if user does not use that column in insert script. Also if user sends NULL as value, your default value won’t be use again. after 12c we are able to define a value even user sends NULL in insert script:
    Oracle PL/SQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    drop table dummy;
    create table dummy(id number, mydata varchar2(10) default on null 'Thor');
     
    insert into dummy (id,mydata) values (1, null);
    select * from dummy;
     
            ID MYDATA
    ---------- ----------
             1 Thor

    if you are someone like me who almost hates NULL values, this is a great feature.
  3. Identity Column and Sequence Nextval as Default Column Value
    First, you can assign a sequence next value to a column default value.
    Oracle PL/SQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    create sequence seq;
     
    create table dummy(id number default seq.nextval, mydata varchar2(10));
     
    insert into dummy (mydata) values ( null);
    insert into dummy (mydata) values ( 'second row');
    select * from dummy;
     
            ID MYDATA
    ---------- ----------
             1
             2 second row

    so this is not just a shortcut for writing insert statements. many companies (unfortunately) use triggers to assign id column value which is a complete disaster!
    Rule of Thumb: Triggers are evil! if you have option not to use trigger, do not use trigger!
    Let’s check performance of sequence default value and using trigger for that job.
    Oracle PL/SQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    drop sequence seq;
    drop table dummy;
    create sequence seq;
    create table dummy(id number , mydata varchar2(10));
     
    create or replace trigger tr_bi_dummy
    before insert on dummy
    for each row
    begin
      :new.id := seq.nextval;
    end;
    /
     
    insert into dummy (mydata)
      select 'test rows' from dual
      connect by level < 100000;
     
    --99999 rows created.
     
    --Elapsed: 00:00:07.69
     
    /***********************************************************************/
    drop sequence seq;
    drop table dummy;
    create sequence seq;
    create table dummy(id number default seq.nextval, mydata varchar2(10));
     
    insert into dummy (mydata)
      select 'test rows' from dual
      connect by level < 100000;
     
    --99999 rows created.
     
    --Elapsed: 00:00:01.04

    so it is around 7 times faster then trigger! Yupp!

    Also you can create identity columns anymore and don’t have to deal with sequences:

    Oracle PL/SQL
    1
    2
    3
    4
    5
    6
    7
    create table dummy(id number generated always as identity , mydata varchar2(10));
     
    insert into dummy (mydata) values ('test data');
    select * from dummy;
            ID MYDATA
    ---------- ----------
             1 test data

    this is not magical of course, let’s check which objects we just created:

    Oracle PL/SQL
    1
    2
    3
    4
    5
    6
    7
    select cast(object_name as varchar2(20)), object_id, created from user_objects order by created desc
    fetch first 2 rows only;
     
    OBJECT_NAM           OBJECT_TYPE CREATED
    -------------------- ----------  ---------
    ISEQ$$_76120         SEQUENCE    25-FEB-19
    DUMMY                TABLE       25-FEB-19

    as you can see there is a new sequence called ISEQ$$_76120. This is created for our identity column. In the end, identity data is generated from a sequence. check default data for identity column:

    Oracle PL/SQL
    1
    2
    3
    4
    5
    6
    7
    select table_name, column_name, data_default
    from user_Tab_cols where table_name  ='DUMMY';
     
    TABLE_NAME           COLUMN_NAME          DATA_DEFAULT
    -------------------- -------------------- --------------------------------
    DUMMY                ID                   "MUSTAFA"."ISEQ$$_76120".nextval
    DUMMY                MYDATA

    Oracle just created a sequence and assign it as default value of that column.

  4. Creating PLSql Functions in SQL:
    This is a great feature. It is probably one of the worst thing to use a plsql function in Sql! this will cause context switch and decrease your Sql Performance.

    Oracle PL/SQL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    select sum(sf_round(data_object_id)) from tmp_objects;
    SUM(SF_ROUND(DATA_OBJECT_ID))
    -----------------------------
                        164146565
     
    Elapsed: 00:00:01.14
     
    /***********************************************************/
     
    with function my_round(p number) return number as
    begin
      return round(p,4);
    end;
    select sum(my_round(data_object_id)) from tmp_objects;
    /
     
    Elapsed: 00:00:00.30

     

There are many different new features of course but those are my favorite ones. Hope you like it.

See you soon.

12c Development SQL / PLSQL new_featuresplsqlsql

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes