18c versus 19c

Posted by

I had someone say to me at an event recently: “We’re are going to upgrade to 18c, because 19c is new and is probably less stable”.

Let me sum up that sentiment simply: It’s Wrong Smile

Now, don’t get me wrong. I am not claiming that every Oracle release is perfect, contains zero bugs, never has a regression, will mow your lawn, take your kids to school, clean your house and sort out all the climate change issues in the world.

19c is the last of the (for lack of a better term) “generation” of 12c databases. Some customers always leap to every new release as soon as it comes out, because one or more of the new features in that release will yield a competitive advantage or cost saving innovation for them. Other customers view upgrading solely as a necessary evil to stay supported and more importantly stay secure. If you are in the latter category, and you prefer to upgrade infrequently, then 19c is your “go to” release.

Plenty more details about support timeframes etc can be found here but I figured the easiest way to demonstrate picking 19c over 18c is with this simple demo Smile

19c with PL/SQL types



SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

1 row selected.

SQL> create table tb_test (id number, description varchar2 (50));

Table created.

SQL>
SQL> create or replace package pkg_test is
  2      cursor cur_test is
  3          select *
  4          from tb_test
  5          where 1=2;
  6      type typ_cur_test is table of cur_test%rowtype;
  7      function fn_test(p_rows in number) return typ_cur_test pipelined;
  8  end;
  9  /

Package created.

SQL> create or replace package body pkg_test is
  2      function fn_test(p_rows in number) return typ_cur_test pipelined as
  3      l_tab typ_cur_test := typ_cur_test();
  4      begin
  5          for i in 1..p_rows loop l_tab.extend;
  6              l_tab(i).Id := i;
  7              l_tab(i). Description := 'test';
  8              pipe row(l_tab(i));
  9          end loop;
 10      return ;
 11      end;
 12  end pkg_test;
 13  /

Package body created.

SQL>
SQL> select * from table(pkg_test.fn_test(2));

        ID DESCRIPTION
---------- --------------------------------------------------
         1 test
         2 test

2 rows selected.

SQL>

There does not seem anything too spectacular about that demo. We created some types, and a table function and everything works like it is supposed to. Let’s move on to 18c

18c with PL/SQL types



SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

1 row selected.

SQL> create table tb_test (id number, description varchar2 (50));

Table created.

SQL>
SQL> create or replace package pkg_test is
  2      cursor cur_test is
  3          select *
  4          from tb_test
  5          where 1=2;
  6      type typ_cur_test is table of cur_test%rowtype;
  7      function fn_test(p_rows in number) return typ_cur_test pipelined;
  8  end;
  9  /

Package created.

SQL> create or replace package body pkg_test is
  2      function fn_test(p_rows in number) return typ_cur_test pipelined as
  3      l_tab typ_cur_test := typ_cur_test();
  4      begin
  5          for i in 1..p_rows loop l_tab.extend;
  6              l_tab(i).Id := i;
  7              l_tab(i). Description := 'test';
  8              pipe row(l_tab(i));
  9          end loop;
 10      return ;
 11      end;
 12  end pkg_test;
 13  /

Package body created.

SQL>
SQL> select * from table(pkg_test.fn_test(2));

    ATTR_1 ATTR_2
---------- --------------------------------------------------
         1 test
         2 test

2 rows selected.

SQL>

It seems all fine at first glance, but look at those column names. Yup, we had a bug in 18c where we got a bit confused with column names in those table types. That’s a regression and so we rushed to fix it.  And where did we fix it first? Yup, in 19c.

Stick with 19c folks.

3 comments

  1. The 12.2, 18c, and 19c Grid Infrastructure Installation and Upgrade Guide for Linux, in “Options for Oracle Grid Infrastructure Upgrades” state:

    When you upgrade to Oracle Grid Infrastructure 19c [ 18c, 12c Release 2 (12.2)] , you upgrade to an Oracle Flex Cluster configuration.

    That sounds like a major change, but I am not knowingly “upgrade to an Oracle Flex Cluster configuration”.

    In what way does my cluster configuration change?

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.