Automatically Not Null when adding a DEFAULT Clause

Today I learned something new about the DEFAULT ON NULL clause.
Since Oracle database release 12 it is possible to define a DEFAULT ON NULL clause which is used when a NULL is provided as value. The thing I didn't know is that the column is automatically defined as NOT NULL.
Is this documented behaviour? Yes, it is. Quote from the documentation:

When you specify ON NULL, the NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified.

SQL> select banner
  2    from v$version
  3  /

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

SQL> create table t 
  2  (col1 varchar2(10) default on null 'hello')
  3  /

Table T created.

SQL> desc t
Name Null?    Type         
---- -------- ------------ 
COL1 NOT NULL VARCHAR2(10) 

And in case you are wondering if you can change the column afterwards to NULL, the answer is "no".

SQL> alter table t 
  2  modify (col1 null)
  3  /

Error starting at line : 1 in command -
alter table t 
modify (col1 null)
Error report -
ORA-01451: column to be modified to NULL cannot be modified to NULL
01451. 00000 -  "column to be modified to NULL cannot be modified to NULL"
*Cause:    the column may already allow NULL values, the NOT NULL constraint
           is part of a primary key or check constraint.
*Action:   if a primary key or check constraint is enforcing the NOT NULL
           constraint, then drop that constraint.

JSON, BLOB column and Check Constraint

Last week I attended Neil Chandler's session on JSON during the POUG conference in beautiful Sopot, Poland.
The JSON Developer's Guide recommends using BLOB for storing data, and this is what Neil also recommended.
I was under the (FALSE!) impression that it was not possible to put an IS JSON check constraint on a BLOB column, simply because I tried once and got an exception. After the session I asked Neil if this was the case, but (of course) he said that it was possible.
"Trust, but verify", so here is my test case.

My tests are done on a 12.1.0.2.0 release of the Oracle database, as can be seen in the output below.

SQL> select banner
  2    from v$version
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Solaris: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

First of all create a table with a BLOB column to hold the JSON data.

SQL> create table t
  2  (json_data blob)
  3  /

Table T created.

The most easiest way of placing an IS JSON check constraint on the column is to use the following statement:

SQL> alter table t add constraint json_chk check (json_data is json)
  2  /

Error starting at line : 1 in command -
alter table t add constraint json_chk check (json_data is json)
Error report -
ORA-40499: no format defined for binary data type

Because it failed, I was under the assumption that it is not possible to place an IS JSON check constraint on the column.
Turns out that I was not complete in my check constraint definition (guess I should have read the exception and documentation more closely).

When you place the check constraint in the following manner on the column, the IS JSON constraint is added.

SQL> alter table t add constraint json_chk check (json_data is json format json)
   2  /
 
 Table T altered.

In later versions of the database (I've also tried on 12.2 and 18c) it is possible to use the former syntax, there is no need to use "FORMAT JSON" in the check constraint definition.

Let's see the BLOB column with the check constraint in action.
First attempt to insert a valid JSON-string into the table:

   SQL> insert into t values ('{"this":"that"}' )
      2  /
    
    Error starting at line : 1 in command -
    insert into t values ('{"this":"that"}' )
    Error report -
    ORA-01465: Ongeldig hexadecimaal getal.

Even though the string is valid JSON, it is not a binary representation. For this to work UTL_RAW is required:

      SQL> insert into t values (utl_raw.cast_to_raw('{"this":"that"}') )
      2  /
    
    1 row inserted.

Trying to create an entry in the table which is not JSON will result in a violation of the JSON check constraint.

      SQL> insert into t values (utl_raw.cast_to_raw('just a string') )
      2  /
    
    Error starting at line : 1 in command -
    insert into t values (utl_raw.cast_to_raw('just a string') )
    Error report -
    ORA-02290: CHECK-beperking (ALEX.JSON_CHK) is geschonden.

When the value that you're trying to insert is not a binary value, it will always fail with "ORA-01465: invalid hex number":

      SQL> insert into t values ('just a string' )
      2  /
    
    Error starting at line : 1 in command -
    insert into t values ('just a string' )
    Error report -
    ORA-01465: Ongeldig hexadecimaal getal.

One of the benefits of placing an IS JSON check constraint on the column is that you can use a "dot-notation" to query out the data:

      SQL> select tst.json_data.this
      2    from t tst
      3  /
    
    THIS
    --------------------------------------------------------------------------------
    that

Keep in mind that you must use a table alias for this to work. Omitting the table alias will result in an error.

      SQL> select json_data.this
      2    from t tst
      3  /
    
    Error starting at line : 1 in command -
    select json_data.this
      from t tst
    
    Error at Command Line : 1 Column : 8
    Error report -
    SQL Error: ORA-00904: "JSON_DATA"."THIS": ongeldige ID

Good old BIN_TO_NUM to check the overall status

A good while ago Chris Saxon, member of the AskTom answer team, asked on twitter which datatype you use when defining tables when you need a Boolean-representation. As you might know there is no Boolean datatype in SQL.
A lot of discussion followed which I'm not going to repeat.
Usually I use a VARCHAR2(1) with a check constraint for Y and N, but for a recent requirement I decided to use a NUMBER instead.

The requirement that I needed to implement was the following:

A number of tasks need to be fulfilled, the order doesn't matter, and they need to be checked by a supervisor. Before the supervisor gives his/her stamp of approval, the tasks all need to be completed.

For this example I will leave the authorization out, it is irrelevant.

create table tasks
(task_date       date   not null
,task_a          number not null check (task_a in (0,1))
,task_b          number not null check (task_b in (0,1))
,task_c          number not null check (task_c in (0,1))
,tasks_checked   number not null check (tasks_checked in (0,1))
)
/

The table contains a date for which the tasks need to be completed, indicators for the individual tasks (A,B, and C) and the Tasks Checked indicator.
All indicators are NOT NULL, datatype NUMBER and only allowed to contain a zero or one. An zero indicates FALSE, a one indicates TRUE.

The requirement states that the verification of the tasks should take place before the tasks are done. There are several ways to implement this requirement, and this time I'm going to use the combination of zeros and ones to create a binary representation.
I will add to overall status as a virtual column to the table using the built-in function BIN_TO_NUM.

alter table tasks
add (bin_status as (bin_to_num (task_a, task_b, task_c, tasks_checked)))
/

Now the (not so) magic part:
When the binary status equals 14, all tasks are completed and can be checked by a supervisor.
When the binary status is an odd number, the checking of the tasks is done before the tasks are completed and this is not allowed. The only exception is when the binary status equals fifteen (15), then all tasks are done and it is checked by a supervisor.
When the binary status an even number, all is good.
All this can easily be captured in a CHECK constraint:

alter table tasks
add constraint chk check (
   case when bin_status = 15 then 1
        when mod (bin_status, 2) = 0 then 1 
        else 0 end = 1
)
/

Finally two inserts to show that it works as expected.

insert into tasks
   (task_date
   ,task_a
   ,task_b
   ,task_c
   ,tasks_checked   
   )
values
   (sysdate
   ,0
   ,0
   ,1
   ,0
 14     );

1 row created.

insert into tasks
   (task_date
   ,task_a
   ,task_b
   ,task_c
   ,tasks_checked   
   )
values
   (sysdate
   ,1
   ,0
   ,1
   ,1
   );

insert into tasks
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.CHK) violated

For the first time I've used LiveSQL to create the scripts that go along this blogpost, you can find that right here.

Links

• Oracle documentation: BIN_TO_NUM
• LiveSQL demo

RegExp: Constraint to prevent spaces at the beginning or end.

Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.

To try things out, let's just start with a simple table with a single column.

   create table test
   (str varchar2(10));

Now the magic part: add a check constraint using a regular expression

   alter table test
   add constraint str_chk check (
      regexp_like (str, '^[^[:space:]].+[^[:space:]]$')
    );

The regular expression reads: The string should start (the first caret) with any character which is not in the character class of [:space:], followed by one or more characters (the period) and it should end with (the dollar) any character as long as it's not in the character class of [:space:].

UPDATE - 07-Sept-2016

The expression that is used in the above constraint will also prevent from a single allowed character or double character to be entered. This omission has been corrected by David Grimberg. The correct regular expression should be:

^[^[:space:]](.*[^[:space:]])?$

To test the constraint, the following insert statement were used.

   insert into test values ('hello');
   insert into test values ('hel lo');
   -- Not allowed:
   --   starting with a space
   insert into test values (' hello');
   --   ending with a space
   insert into test values ('hello ');
   --   just a space
   insert into test values (' ');
   --   multiple spaces
   insert into test values ('   ');
   --   Tab
   insert into test values (chr(9));
   --   Line feed
   insert into test values (chr(10));
   --   Carrige Return
   insert into test values (chr(13));

Top N- queries: using the 12c syntax.

One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".

For the examples I will use my all time favourite demo data: the EMP table.

SQL> select ename
  2        ,sal
  3    from emp
  4   order by sal desc
  5  /

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300
WARD             1250
Widlake          1250
ADAMS            1100
JAMES             950
SMITH             800

14 rows selected.

As you can tell from the output above, KING has the highest salary and FORD and SCOTT have the same salary which is the second highest.

If you wanted to write a Top N query before Oracle database 12c, let's say the Top 2 of most earning EMP, you would probably have written something with an inline view.

SQL> select ename
  2        ,sal
  3    from (select ename
  4                ,sal
  5            from emp
  6           order by sal desc
  7         )
  8   where rownum <= 2
  9  /

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000

This query might do the job most of the time, but the results might not be what you were looking for. In this case the requirement is "the Top 2 of most earning EMP", SCOTT and FORD should have both been in the results as they have the same salary.
To resolve this, you would have to rewrite your query using an Analytic Ranking Function in the inline view:

SQL> select ename
  2        ,sal
  3    from (select ename
  4                ,sal
  5                ,rank() over (order by sal desc) rn
  6            from emp
  7         )
  8   where rn <= 2
  9  /

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000

Using the new Top N syntax in Oracle database 12c, the query is a lot easier to understand.

SQL> select ename
  2        ,sal
  3    from emp
  4   order by sal desc
  5   fetch first 2 rows with ties
  6  /

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000

On line 4 the results are sorted based on the salary (highest on top) and line 5 instructs to get the first two rows. Because of the addition "with ties" both SCOTT and FORD are shown in the results.
To see what happens under the covers, an explain plan is created for this query.

SQL> explain plan for
  2  select ename
  3        ,sal
  4    from emp
  5   order by sal desc
  6   fetch first 2 rows with ties
  7  /

Explained.

SQL> select *
  2    from table (dbms_xplan.display())
  3  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3291446077

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    14 |   644 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |      |    14 |   644 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |    14 |   154 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMP  |    14 |   154 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rank"<=2)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("SAL") DESC )<=2)

16 rows selected.

The output above shows that the Analytic Function RANK is used.

Rounding amounts, divide cents over multiple lines

In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):

What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If i multiply this by 42 it would amount to just 99.96. What if want to spread the .04 difference on 4 records, not just the last record. In effect i'll be having 4 records with 2.39. Right now i'm doing this via cursor. Im kinda hoping i can do this using sql or analytic functions

Let's create a table first, called T:

create table t
as
select rownum id
  from dual
 connect by level <= 42

In order to determine the number of rows, we need three pieces of information:

1. The amount that we need to divide
2. The total number of rows in the set
3. The difference between the rounded amount and the amount that we need to divide

 select id
      ,100 amount
      ,count(*) over () entries
      ,round (100 / count(*) over (), 2) rounded
    from t

To calculate how many rows will have the extra cent added or subtracted, the following formula is used.

abs (amount - (entries * rounded)) * 100

When you want the rounding done on the "first" rows, a simple CASE expression can be used to mark the rows

case 
when rownum <= abs ((amount - (entries * rounded)) * 100)
then 'x'
end as indicator

The query now looks like the following, with the first ten rows:

with amounts
as
( select id
      ,100 amount
      ,count(*) over () entries
      ,round (100 / count(*) over (), 2) rounded
    from t
)
select id
      ,amount
      ,entries
      ,rounded
      ,case 
       when rownum <= abs ((amount - (entries * rounded)) * 100)
       then 'x'
       end as indicator
 from amounts
;
 ID     AMOUNT  ENTRIES    ROUNDED F
---------- ---------- ---------- ---------- -
  1   100       42       2.38 x
  2   100       42       2.38 x
  3   100       42       2.38 x
  4   100       42       2.38 x
  5   100       42       2.38
  6   100       42       2.38
  7   100       42       2.38
  8   100       42       2.38
  9   100       42       2.38
 10   100       42       2.38

The last piece of the puzzle is to determine if we need to add or subtract the cent. Using the SIGN function is an easy way to determine this.

sign (amount - (entries * rounded)) as pos_neg

Putting everything together will give you the following query (with the first 10 rows)

with amounts
as
( select id
      ,100 amount
      ,count(*) over () entries
      ,round (100 / count(*) over (), 2) rounded
    from t
)
,indicators as 
(
select id
      ,amount
      ,entries
      ,rounded
      ,case 
       when rownum <= abs ((amount - (entries * rounded)) * 100)
       then 'x'
       end as indicator
      ,sign (amount - (entries * rounded)) as pos_neg
 from amounts
)
select id
      ,rounded +
       (nvl2 (indicator, 0.01, 0) * pos_neg) final_amount
  from indicators
;
 ID FINAL_AMOUNT
---------- ------------
  1    2.39
  2    2.39
  3    2.39
  4    2.39
  5    2.38
  6    2.38
  7    2.38
  8    2.38
  9    2.38
 10    2.38

And there it is. The rounding is divided over the first four rows.

Don't want to use the first rows, but the last rows instead? Use the following expression to set the indicator

case 
when rownum >
 case sign ((amount - (entries * rounded)))
   when -1 then entries - abs ((amount - (entries * rounded)) * 100)
   else entries - (amount - (entries * rounded)) * 100
   end
then 'x'
end as indicator

Links

• Rounding Amounts, the missing cent
• Rounding Amounts, the missing cent: with the Model clause
• ABS function
• SIGN function
• NVL2 function

Temporal validity, multiple end dates

Recently I got involved in a question on Temporal Validity Periods together with Chris Saxon, one of the askTom-answer team.

The question was along the lines of: "What if I have a single start date but two possible end dates. One of the end dates is filled automatically by a background proces (could be a job) while the other one is to signal that the end date is set manually by the user. Could you use Temporal Validity to get the correct rows?"

My first reaction was: "Yes, you can have multiple validity periods on a single table... but wouldn't it be better to have a single end date and another column to signal if it was set by an automatic proces or by a manual process?". In the latter case you would only need a single validity period.

It got me thinking though... would it be possible to define a single validity period when three (one start and two end dates) are involved?

Let's start by creating a table and populate it with some sample data.

   create table t
   (id              number       primary key
   ,name            varchar2(50) not null
   ,start_date      date         not null
   ,end_date        date
   ,manual_end_date date
   );

The table is fairly straight forward: a surrogate primary key, a name, a start date and two end dates: end_date and manual_end_date.

Next populate the table with some sample data. The name column is used to signal what the row represent, being a row that is current or not (based on sysdate).

   insert into t (id, name, start_date) values (1, 'no end, current', sysdate - 10);
   insert into t (id, name, start_date) values (2, 'no end, not current', sysdate + 10);

   insert into t (id, name, start_date, end_date) values (3, 'current', sysdate - 10, sysdate + 10);
   insert into t (id, name, start_date, end_date) values (4, 'not current, past', sysdate - 10, sysdate -9);
   insert into t (id, name, start_date, end_date) values (5, 'not current, future', sysdate + 10, sysdate +20);

   insert into t (id, name, start_date, manual_end_date) values (6, 'current', sysdate - 10, sysdate + 10);
   insert into t (id, name, start_date, manual_end_date) values (7, 'not current, past', sysdate - 10, sysdate -9);
   insert into t (id, name, start_date, manual_end_date) values (8, 'not current, future', sysdate + 10, sysdate +20);

   commit;

select *
  from t;

        ID NAME                                               START_DAT END_DATE  MANUAL_EN ABSOLUTE_
---------- -------------------------------------------------- --------- --------- --------- ---------
         1 current, no end                                    08-MAR-16
         2 not current, no end                                28-MAR-16
         3 current                                            08-MAR-16 28-MAR-16           28-MAR-16
         4 not current, past                                  08-MAR-16 09-MAR-16           09-MAR-16
         5 not current, future                                28-MAR-16 07-APR-16           07-APR-16
         6 current                                            08-MAR-16           28-MAR-16 28-MAR-16
         7 not current, past                                  08-MAR-16           09-MAR-16 09-MAR-16
         8 not current, future                                28-MAR-16           07-APR-16 07-APR-16

8 rows selected.

Now that the table is populated with the sample data, we can start the experiment. Can you have a temporal validity period based on an expression?

   SQL> alter table t
     2  add period for regular (start_date, coalesce (end_date, manual_end_date))
     3  /
   add period for regular (start_date, coalesce (end_date, manual_end_date))
                                                *
   ERROR at line 2:
   ORA-02000: missing ) keyword

Uhm,... no.
What about moving the expression over to a virtual column and use the virtual column in the temporal validity period?

   SQL> alter table t
     2  add absolute_end_date generated always
     3  as (coalesce (end_date, manual_end_date))
     4  /

   Table altered.


   SQL> alter table t
     2  add period for virt_col (start_date, absolute_end_date)
     3  /

   Table altered.

So far, so good. When playing around with temporal validity periods I ran into a bug by extracting the DDL using DBMS_METADATA, so let's see what Oracle makes of this combination of virtual column and the validity period.

   SQL> set long 9000
SQL> select dbms_metadata.get_ddl ('TABLE', 'T')
  2    from dual
  3  /

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "ALEX"."T"
   ( "ID" NUMBER,
 "NAME" VARCHAR2(50) NOT NULL ENABLE,
 "START_DATE" DATE NOT NULL ENABLE,
 "END_DATE" DATE,
 "MANUAL_END_DATE" DATE,
 "ABSOLUTE_END_DATE" DATE GENERATED ALWAYS AS (COALESCE("END_DATE","MANUAL_END_D
ATE")) VIRTUAL ,
 PERIOD FOR "VIRT_COL"("START_DATE","ABSOLUTE_END_DATE") ,
  PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

As you can see from the output above, all seems to be just fine.
But now for the real test and query the table using the temporal validity.

   SQL> select *
     2    from t as of period for virt_col sysdate
     3  /

           ID NAME                                               START_DAT END_DATE  MANUAL_EN ABSOLUTE_
   ---------- -------------------------------------------------- --------- --------- --------- ---------
            1 current, no end                                    08-MAR-16
            3 current                                            08-MAR-16 28-MAR-16           28-MAR-16
            6 current                                            08-MAR-16           28-MAR-16 28-MAR-16

That worked! Awesome!.. Virtual Columns with a Temporal validity period, wonderful.

I ran these test on my virtual box, using the pre-built developer appliance, so it might not be the latest patch release or anything.

   SQL> select *
  2    from v$version
  3  /

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE 12.1.0.2.0 Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

Links

• Oracle Docs: Temporal validity
• Temporal validity, multiple on one table.
• Temporal validity, part deux

Updating Identity Columns

During my presentation "Oracle 12c for Developers" at the Sloveninan and Croatian User Groups I got the same question twice about Identity Columns:

Is it possible to update an Identity Column?

During the presentation I show how it is not possible to insert a value for a "Generated Always Identity" column.
Let's take a look at an example:

SQL> create table t
  2  (id number  generated as identity
  3  ,name varchar2(35)
  4  );

Table created.

SQL> insert into t(name) values ('hello')
  2  /

1 row created.
   

In the first section the table is created with a "Generated Always Identity" column.
The second part shows that you can insert into the table, as long as the identity column isn't used in the insert statement. Trying to do so will lead to an exception.

SQL> insert into t values (1, 'World')
  2  /
insert into t values (1, 'World')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
   

The same is true when you attempt to insert a record type:

SQL> declare
  2     r t%rowtype;
  3  begin
  4     r.id := 42;
  5     r.name := 'world';
  6     insert into t values r;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
ORA-06512: at line 6
   

Now back to the question, what about an update? Let's try some different methods:

SQL> update t
  2     set id = id
  3  /
   set id = id
       *
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
SQL> update t
  2     set id = id + 1
  3  /
   set id = id + 1
       *
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
SQL> update t
  2     set id = id + 0
  3  /
   set id = id + 0
       *
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
   

In short: updating a "Generated Always Identity" column is not allowed either.
One final remark: an identity column is not the same as a primary key. If you want to use a "Generated Always Identity" column as your primary key, then you will have to specify that explicitly.

SQL> create table t
  2  (id number  generated as identity primary key
  3  ,name varchar2(35)
  4  );

Table created.
   

Rounding Amounts, the missing cent: with the MODEL clause

Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions.
Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.

To create an example, first let's create a table with only three records in it.

   SQL> create table t
     2  as
     3  select rownum + 42 id
     4    from dual
     5  connect by level <= 3
     6  ;

   Table created.
   SQL> select *
      2    from t
      3  /

                ID
        ----------
                43
                44
                45

In the code below the DIMENSION is the ID from the table. The measures (the "cells" that we can manipulate in the MODEL clause) are used as follows

Amount	The amount that is the result of the division per record
Total	The amount that we want to divide over the records
Diff	Placeholder for the difference between the sum of the rounded amounts and the amount that needs to be divided.
Indicator	We need to know the highest value for the DIMENSION (to add the difference to). Since we can't determine the highest value of the DIMENSION, this MEASURE is used for that.

When we execute the query we get the following results, except for the DIMENSION column (ID) and the MEASURE Indicator all columns are empty.

      SQL> select *
        2    from t
        3  model
        4  dimension by (id)
        5    measures (cast (null as number) amount
        6    ,cast (null as number) total
        7    ,cast (null as number) diff
        8    ,id indicator
        9    )
       10  rules (
       11  )
       12  /

       ID           AMOUNT      TOTAL       DIFF  INDICATOR
      ---------- ---------- ---------- ---------- ----------
              43                                          43
              44                                          44
              45                                          45

It is time to add some rules to the MODEL clause.
The first rule is to add the amount which needs to be divided.

total [0] = 100   

This will add a "magic" row to the resultset with Dimension 0 where the measure column total is filled with 100, the amount that we want to divide.
The reason I call it a "magic" row is, is because it is not in the table and made up. In this row I will store some values that I need to do my calculations and such.
The second rule is

 indicator [0] = max (indicator) [any]
 

In this rule I will determine which row is the "last" row, the one with the highest ID.
Next rule is to do the actual calculation: divide the amount by the number of rows in the resultset. Of course don't count the "magic" row, hence the condition id > 0.

 amount[id > 0] = round (total[0]/(count(*)[id > 0]), 2)
 

To determine the total of the rounded values, we will use the following rule:

 amount[0] = sum (amount)[id > 0]
 

This total amount is also placed on the "magic" row.
Calculating the difference between the amount that we want to divide and the actual divided amount is done in the following rule:

 diff[0] = total[cv()] - amount[cv()]
 

The difference is added to the "last" row in the last rule:

 amount[indicator[0]]  = amount[cv()] + diff[0]
 

To see the complete query in action:

   SQL> select *
     2    from t
     3  model
     4  dimension by (id)
     5    measures (cast (null as number) amount
     6    ,cast (null as number) total
     7    ,cast (null as number) diff
     8    ,id indicator
     9    )
    10  rules (
    11    total [0] = 100
    12   ,indicator [0] = max (indicator) [any]
    13   ,amount[id> 0] = round (total[0]/(count(*)[id>0]), 2)
    14   ,amount[0] = sum (amount)[id>0]
    15   ,diff[0] = total[cv()] - amount[cv()]
    16   ,amount[indicator[0]]  = amount[cv()] + diff[0]
    17  )
    18  /

       ID     AMOUNT      TOTAL       DIFF  INDICATOR
   ---------- ---------- ---------- ---------- ----------
           43      33.33                               43
           44      33.33                               44
           45      33.34                               45
            0      99.99        100        .01         45
   

As you can see in the output above the values are rounded (in the AMOUNT column) and the last row takes the difference.
But also our "magic" row is added to the output, to filter that one out of the resultset simply add a where clause.

      SQL> select id
        2      ,amount
        3    from (select *
        4    from t
        5  model
        6  dimension by (id)
        7    measures (cast (null as number) amount
        8    ,cast (null as number) total
        9    ,cast (null as number) diff
       10    ,id indicator
       11    )
       12  rules (
       13    total [0] = 100
       14   ,indicator [0] = max (indicator) [any]
       15   ,amount[id> 0] = round (total[0]/(count(*)[id>0]), 2)
       16   ,amount[0] = sum (amount)[id>0]
       17   ,diff[0] = total[cv()] - amount[cv()]
       18   ,amount[indicator[0]]  = amount[cv()] + diff[0]
       19  ))
       20  where id> 0 order by id
       21  /

              ID     AMOUNT
      ---------- ----------
              43      33.33
              44      33.33
              45      33.34
      

Links

• Rounding Amounts, the missing cent

Conditional Compilation and Static Boolean

One of my pet-projects is LoggerUtil, which is a utility for Logger, which is an excellent logging tool for PL/SQL.
This post is not about Logger, but some dealings with Conditional Compilation.

With Conditional Compilation you can create a single code base to handle different functionalities depending on compiler flags.
The latest addition to LoggerUtil was a method to create a custom template. For this to work, LoggerUtil depends on a certain Logger Release (where issue #103 is implemented). The dependency lies in the fact that the custom template is stored in the LOGGER_PREFS table and before issue #103 was resolved there was no way to add data to the LOGGER_PREFS table (or at least not a supported way).

Conditinal Compilation is just what the doctor ordered. With a Conditional Compilation directive you can check if Logger is at least version 3, so we can have a supported way of writing into the LOGGER_PREFS table. Sounds easy enough.

And this is where I made some discoveries about Conditional Compilation.

Let's begin with a package specification with only CONSTANTS in there.

      create or replace package constants_pkg
      is
         version   constant varchar2(10) := '1.2.3';
         major_num constant number := 1;
         major_int constant pls_integer := 1;
         major_vc  constant varchar2(1) := 'a';
      end constants_pkg;
   

There are a few variations in there, starting with the current method that Logger has implemented the version number (the constant called VERSION).
Second there is a NUMBER constant.
Third is an PLS_INTEGER constant.
Fourth a variation to the first constant, just one character.

Following is a procedure, called conditional (how appropriate):

      create or replace
      procedure conditional
      is
      begin
         $if constants_pkg.version like '1%'
         $then
            dbms_output.put_line ('string, with LIKE comparison');
         $end
         dbms_output.put_line ('This will always be displayed');
      end conditional;
   

The $IF, $THEN, $END are part of the syntax used for Conditional Compilation.
On line 5 the packaged constant is checked if the string start with a 1. When it does, line 7 is included in the compiled code. If the packaged constant doesn't start with a 1 then line 7 is not included in the compiled code.
You might say: "Should you do a comparison like this"

      $if to_number (substr (constants_pkg.version, 1, 1)) > 1
   

and you would be right, but... for this example it doesn't matter as both don't work. When you try to compile the code, you will see the following error:

Errors for PROCEDURE CONDITIONAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/8  PLS-00174: a static boolean expression must be used
   

So my next attempt at getting this to work, was using the full version constant:

      $if constants_pkg.version = '1.2.3'
   

With the same results, the same compilation error.

What about just a single character string?

      $if constants_pkg.major_vc = '1'
   

...Nope, again the same compilation error.

Next up, try a NUMBER constant instead:

      $if constants_pkg.major_num = 1.0
   

I thought the ".0" at the end could make a difference, but alas.. same compilation error.

Last attempt: the PLS_INTEGER:

      $if constants_pkg.major_int = 1
   

This may not come as a surprise now, but this works. :D
This is similar to the way that Oracle does it itself.

When you want to know which release of the Oracle database you are on, you can check DBMS_DB_VERSION. There are constants defined in DBMS_DB_VERSION which you can use with Conditional Compilation.

So Martin, if you are still reading: Can I have the version as a PLS_INTEGER, please?

Links to related articles

1. Speed Up Development with Logger
2. Create Custom Template with LoggerUtil
3. DBMS_DB_VERSION

Splitting a comma delimited string the RegExp way, Part Three

The article read most often on this blog is called "Splitting a Comma Delimited String, the RegExp way".
On this blog there are two articles about this technique, one is about splitting up a single string into multiple rows and the other is about multiple strings into multiple rows.
Links to both articles are included at the bottom of this article.
It seems like there is a need for functionality like that frequently. And just to add to those two articles on the subject, here is a third one combining the first two articles.

Recently I was asked for help in a comment on how to go about and split up a string like the following

      'ABC/FDF,RET/YRT,UYT/ERT'
   

The expected outcome would be

      ABC
      FDF
      RET
      YRT
      UYT
      ERT
   

As you can see the input string consists of two different delimiters, namely a comma and a forward slash (/).
To split this string up, you will need both techniques from the other articles.

Let's start with a variable containing the input string.

      SQL> var input varchar2(150)
      SQL> 
      SQL> exec :input := 'ABC/FDF,RET/YRT,UYT/ERT'

      PL/SQL procedure successfully completed.
   

The first step is to split the string up using the first method, split up the string using the comma as a delimiter.

SQL> select regexp_substr (:input, '[^,]+',1, rownum) str
  2    from dual
  3   connect by level <= regexp_count (:input, '[^,]+')
  4  ;

STR
-------
ABC/FDF
RET/YRT
UYT/ERT
   

This will leave us with three records each consisting of a string that needs further splitting up, but this time with the forward slash as the delimiter.

Using these rows as the input in the next phase, use the technique described in the second article.
By introducing Subquery Factoring (lines 1-5), create a named query "commas"

   SQL> with commas
     2  as
     3  (select regexp_substr (:input, '[^,]+',1, rownum) str
     4    from dual
     5   connect by level <= regexp_count (:input, '[^,]+'))
     6   select regexp_substr (str, '[^\/]+', 1, rn) split
     7   from commas
     8   cross
     9   join (select rownum rn
    10       from (select max (regexp_count(rtrim (str, '/')||'/', '\/')) mx
    11        from commas
    12     )
    13    connect by level <= mx
    14    )
    15   where regexp_substr (str, '[^\/]+', 1, rn) is not null
    16   ;

   SPLIT
   ----------------------------------
   ABC
   FDF
   RET
   YRT
   UYT
   ERT

The forward slash has special meaning with regular expressions it needs to be escaped using a backslash.
You can see this on lines 6, 10, and 15.
What is interesting, or at least I find interesting, is the use of the RTRIM on line 10.
Each value per line is not completely delimited by the forward slashes, the trailing one is missing. Just to concatenate one to each line would be to easy, what if there is a trailing slash?
The RTRIM removes the trailing slash and concatenates one at the end, making sure that the string is split up at the right place.

Links

• Part One
• Part Two

Refresh Multiple Materialized Views in One Go: No Data Found

To refresh multiple Materialized Views you can use the DBMS_MVIEW package, with the procedure aptly named Refresh. One method is to provide a comma-separated list of Materialized View names, or you can use an DBMS_UTILITY.UNCL_ARRAY to achieve the same objective.
When using the latter method, I stumbled upon this oddity (which is not in the current documentation, or at least I couldn't find it).

The procedure that I initially wrote was the following:

create or replace 
procedure refresh_mviews
is
   l_mviews dbms_utility.uncl_array;
begin
   l_mviews(1) := 'ABC_MV';
   l_mviews(2) := 'DEF_MV';
   l_mviews(3) := 'GHI_MV';   
   dbms_mview.refresh (tab => l_mviews);
end refresh_mviews;
/

On line 4 a local variable is declared on the type DBMS_UTILITY.UNCL_ARRAY. The declaration of this type is

TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER;

On lines 6 through 8 the array is filled with the names of the Materialized Views that I want to refresh.
The actual refresh is done on line 9.

When executing the code above, the following exception is raised:

Error report -
ORA-01403: Geen gegevens gevonden.
ORA-06512: in "SYS.DBMS_SNAPSHOT", regel 2809
ORA-06512: in "SYS.DBMS_SNAPSHOT", regel 3025
ORA-06512: in "ALEX.REFRESH_MVIEWS", regel 13
ORA-06512: in regel 2
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

Strange...

After some googling I found some old documentation (from Oracle 9i) describing the functionality of the REFRESH procedure in the DBMS_MVIEW pacakge:

If the table contains the names of n materialized views, then the first materialized view should be in position 1 and the n + 1 position should be set to NULL.

This explains the exception that is being raised.

Adding line 9 in the code below fixes this problem:

create or replace 
procedure refresh_mviews
is
   l_mviews dbms_utility.uncl_array;
begin
   l_mviews(1) := 'ABC_MV';
   l_mviews(2) := 'DEF_MV';
   l_mviews(3) := 'GHI_MV';   
   l_mviews(4) := null;
   dbms_mview.refresh (tab => l_mviews);
end refresh_mviews;
/

Documentation

1. Oracle 9i Documentation
2. Oracle 12c Documentation

Oracle 12c: Temporal Validity, multiple on one table - Part Deux

One of the most wonderful things of Oracle Open World are the Demo-Grounds. When you want to learn more about a certain feature, this is the place to go to. The actual developers and product managers are there to answer your questions and more!

In a previous blog I had written about Temporal Validity in Oracle 12c and whether it would be possible to have multiple validity periods on one table. You can read that blog by following this link.

As you can read in that blog it is possible to create multiple Validity Periods on one table, but only via an ALTER TABLE statement.

That kept me wondering whether what I was doing was supported and what would be the proper way to go and create multiple Validity Periods for a single table.

Because I was still at Oracle Open World when I wrote that blog, it was easy for me to go over to the Demo-Grounds and get my answers.. However this was not as easy as it sounds.

After having finally found the correct station the gentleman I spoke to couldn't give me a satisfying answer, which made me doubt if I had indeed found the correct station. Maybe I didn't find the correct station, or maybe it was the language barriere that I couldn't explain what I wanted to know. Or maybe he just didn't know the answer... anyway, he was very kind and showed me some other things.

By chance I ran into Bryn Llewellyn, Distinguished Product Manager for PL/SQL, and talked to him about my quest to find an answer (among other things; like why I didn't join him for the Bike trip he organized right before OOW #BikeB4OOW).

Bryn suggested to contact Kevin Jernigan, who is a Senior Director Product Management for a number of products including Temporal Validity.
So, after getting back from Oracle Open World I contacted Kevin and he was very helpful.

Turns out there is no supporting syntax to define multiple Validity Periods for a single statement with the CREATE TABLE syntax.
This is stated in the CREATE TABLE section of the documentation:

You can specify at most one valid time dimension when you create a table. You can subsequently add additional valid time dimensions to a table with the add_period_clause of ALTER TABLE.

The limitation is with the CREATE TABLE syntax, not with the Temporal Validity implementation.

One final thing I asked Kevin: How about that DBMS_METADATA behaviour that I encountered in my blog?

Re: the problem with DBMS_METADA, I will check with development to see if this is a known issue (and maybe already fixed in a test environment?), and if not, we will file a bug to have it fixed.

Good to know that this issue is (going to be) addressed and that I found my answer about Temporal Validity.

Documentation Links

Create Table documentation Temporal Validity

Oracle 12c: Temporal Validity, multiple on one table

During a trial run for my presentation at Oracle Open World "Oracle 12c for Developers", you can find the slides on slideshare. there was a question regarding "Temporal Validity".
What is Temporal Validity?
The documentation says it best:

Temporal Validity Support lets you associate one or more valid time dimensions with a table and have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of the period for which a given record is considered valid.

The question was: "Is it possible to have multiple temporal valid periods for a table?"
According to the documentation definition above it should be possible to create multiple temporal valid periods. Let's try this.

SQL> create table t
  2  (id number primary key
  3  ,a_start date
  4  ,a_end date
  5  ,b_start date
  6  ,b_end date
  7  ,period for a_valid (a_start, a_end)
  8  ,period for b_valid (b_start, b_end)
  9 );
,period for b_valid (b_start, b_end)
            *
ERROR at line 8:
ORA-55603: invalid flashback archive or valid time period command

That didn't work.
But we're not done yet. Let's try something a little different:

SQL> create table t
  2  (id number primary key
  3  ,a_start date
  4  ,a_end date
  5  ,b_start date
  6  ,b_end date
  7  ,period for a_valid (a_start, a_end)
  8  );

Table created.

SQL> alter table t
  2 add period for b_valid (b_start, b_end);

Table altered.

That worked. There are now two valid period defined on table T. Maybe I got the syntax wrong, let's take a look at the Metadata and find out where I went wrong.

SQL> select dbms_metadata.get_ddl
  2     ('TABLE'
  3     ,'T')
  4    from dual;
ERROR:
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_METADATA", line 6069
ORA-06512: at "SYS.DBMS_METADATA", line 8666
ORA-06512: at line 1



no rows selected

Oh oh.. I think I found a little bug here.
I wonder what will happen if we add data and run some queries.

SQL> insert into t values
  2  (1, trunc (sysdate) -10, trunc (sysdate), trunc (sysdate), trunc (sysdate) +10) ;

1 row created.

SQL> insert into t values
  2  (2, trunc (sysdate), trunc (sysdate) +10, trunc (sysdate) -10, trunc (sysdate))
  3  ;

1 row created.

SQL> insert into t values
  2  (3, trunc (sysdate) -10, trunc (sysdate) +10, trunc (sysdate) -10, trunc (sysdate) +10)
  3  ;

1 row created.
 
SQL> commit;

Commit complete.

SQL> select *
  2    from t;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

3 rows selected.

SQL> select *
  2    from t as of period
  3   for a_valid sysdate;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for b_valid sysdate;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for a_valid sysdate -1;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for b_valid sysdate -1;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

It all seems to work fine.
So yes, you can add multiple temporal validity periods to a table but only with an alter table statement.

Links

Definition Temporal Validity

5 minutes: Grant Role to Package

The EOUC (EMEA Oracle User Group Community) hosts a special session on sunday during Oracle Open World. This session contains twelve things about Oracle 12c. Each of these 12 things is hosted by a different speaker, so this means that each section is only five minutes long (Strictly guarded by Debra Lilley).
Thankfully I was chosen to be one of the speaker during this special session, very excited and also very scared. Five minutes is not a whole lot to tell the story of PL/SQL security enhancements in 12c.

This blogpost will cover my five minutes of fame.

The thing that I will cover is Code Based Access Control (or actually only a small example where you grant privileges to a package).

1. Setting the stage

For this example I will use Schema A which will contain two tables (T1 and T2) and a package (PKG).
The structure of the tables is irrelevant, and so is the actual implementation of the package.

2. The Package's Objective

The actual implementation of the package is not really relevant, what is important to know is that it would query table T1 and would manipulate some data of table T2. However it should work on schema A's table T1 and on the calling schema's table T2.
The reason that schema A has a table T2 is that it would be impossible to compile the package if table T2 was missing. Table T2 only acts as a template table in schema A.
The calling schema should have a table named T2 in order for the package to be able to work.

note: the calling schema is the schema which would call the package that schema A has defined.

The package (located in schema A) should therefor be compiled with Invoker Rights (authid current_user) as opposed to Definer Rights (authid definer).
The reference to table T1 should be fully qualified (a.t1) and the reference to table T2 should not be fully qualified (t2).
Because of the Invoker Rights specification on the package PKG the table T2 of the calling schema is used. And because of the full qualification of table T1 (a.T1) the correct table is referenced (the one in schema A).
For a calling schema to be able to use this package (PKG) - let's assume the calling schema is called B - EXECUTE privileges need to be granted to schema B

grant execute on pkg to b;

3. The Old way (< 2013) and the flaw

Before the release of Oracle 12c, only granting execute privileges on package PKG to schema B would not be sufficient.
When invoking the package:

begin
   a.pkg;
end;

there would be an exception raised because of the reference to table T1 which resides in schema A.

ORA-00942: table or view does not exist

What you needed to do was to grant privileges on schema A's table T1 as well.

grant select on t1 to b;

Now all works fine. The package can be executed from schema B, and they lived happily ever after.
So, what is the security issue here?
Not only can the package be executed from schema B, the table T1 (in schema A) can also be queried directly without using the package. What if there are columns in table T1 that contain sensitive information?

4. Fixing the flaw

The solution is to grant privileges to the package and not to the user.
First of all: revoke the select privileges on schema A's T1 from schema B

revoke select on t1 from b

Next create a role and grant the select privileges on schema A's table T1 to that role. What you couldn't do before was grant a role to a package, but now you can.

create role only_this_role;

grant select on a.t1 to only_this_role;

grant only_this_role to package a.pkg;

And that's it.
The execute privileges on the package still exists of course otherwise schema B wouldn't be able to execute the package.
Table T1 (in schema A) can not be called directly by schema B any longer.
Now only the package can make calls to table T1.
This adheres to the concept of least privileges.

Order, Order.. Sorting Happens Last

While preparing for my session at Oracle Open World on "Oracle 12c for Developers" I ran into a little remarkable thing.

When sorting a dataset, the sorting is always done last. That is what I was taught anyway. There is probably some obscure way to detect the exact execution plan, but personally I never bothered to go and investigate.
When I was created some test scripts I found there was a way to see that sorting really happens as the final step.

Oracle 12c made some enhancements in the Subquery Factoring clause. Specifically now it is possible to create Functions and Procedures in the WITH clause.
Something like the following:

SQL> with
  2     function formatname (p_name in varchar2)
  3        return varchar2
  4     is
  5     begin
  6        return initcap (p_name);
  7     end formatname;
  8  select ename
  9        ,formatname(ename)  formatted
 10    from emp;

ENAME      FORMATTED
---------- -------------------------
SMITH      Smith
ALLEN      Allen
WARD       Ward
JONES      Jones
MARTIN     Martin
...

After the WITH keyword (first line) a simple function is defined (lines 2 - 7). This function is used in the SELECT part of the query (line 9).

When you want to include a named query in the WITH clause, it has to be after the Procedures and Functions that you have defined.
In the following query, I included a named query called ordered_emps which is a resultset of the EMP table sorted by ENAME.

SQL> set serveroutput on
SQL> with
  2    procedure show (p_what in varchar2)
  3    is
  4    begin
  5      dbms_output.put_line ('input is: '||p_what);
  6    end show;
  7    function formatname (p_name in varchar2)
  8      return varchar2
  9    is
 10    begin
 11       show (p_name);
 12      return initcap (p_name);
 13    end formatname;
 14  ordered_emps as
 15    (select ename from emp order by ename asc)
 16  select ename
 17        ,formatname(ename) formatted
 18    from ordered_emps
 19  /
 
 ENAME       FORMATTED
---------- -----------
ADAMS      Adams
ALLEN      Allen
BLAKE      Blake
CLARK      Clark
FORD       Ford
JAMES      James
JONES      Jones
KING       King
MARTIN     Martin
MILLER     Miller
SCOTT      Scott
SMITH      Smith
TURNER     Turner
WARD       Ward

14 rows selected.

input is: SMITH
input is: ALLEN
input is: WARD
input is: JONES
input is: MARTIN
input is: BLAKE
input is: CLARK
input is: SCOTT
input is: KING
input is: TURNER
input is: ADAMS
input is: JAMES
input is: FORD
input is: MILLER

After the WITH keyword, a procedure (SHOW) is defined which acts as a wrapper for DBMS_OUTPUT.PUT_LINE (lines 2 - 6). Next a function (FORMATNAME) is defined which calls the SHOW procedure with the given input and formats the name to INITCAP (lines 7-13). Next a named query is defined ORDERED_EMPS (lines 14 - 15), and finally the "real" query is specified calling the FORMATNAME function.

Because SERVEROUTPUT is turned on, you can see the results as produced by the query as well as the calls to the SHOW procedure.
The resultset by the query is sorted based on the ENAME as we expect, the calls to the function (and procedure) happen as the data is fetched from the table in no particular order which can be seen in the output from DBMS_OUTPUT.

Invisible Not Null Column

Yesterday I attended John King (@royaltwit) session on Oracle 12c for developers. He gave an overview of all the goodies that are available to us as developers. The whole plugging and unplugging of database, though very cool and exiting, is most likely not very relevant to most developers.
When he discussed the possibility to have invisible columns in a table, it triggered the question: What happens when a NOT NULL column is made invisible? To see what would happen, I set up a small test case on my Oracle 12c database.

SQL> select banner
  2    from v$version
  3  /

BANNER
--------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

First create a table, two columns - both NOT NULL, and some data.

SQL> create table t
  2  (id   number not null
  3  ,text varchar2(10) not null
  4  )
  5  /

Table created.

SQL> insert into t values (1, 'Hello')
  2  /

1 row created.

SQL> insert into t values (2, 'World')
  2  /

1 row created.

SQL> commit;

Commit complete.

Next to change the TEXT column to be invisible.

SQL> alter table t modify (text invisible)
  2  /

Table altered.

SQL> desc t
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID        NOT NULL NUMBER

SQL> select * from t
  2  /

 ID
----------
  1
  2

The data that we entered is still there, or at least the ID values are still there.
So the column is now invisible, what if we try to add new data into it?

SQL> insert into t values (3)
  2  /
insert into t values (3)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T"."TEXT")

Even though you can't see the column, the NOT NULL constraint is still enforced.
When you name the column explicitly, you can still do DML on it.

SQL> update t 
  2  set text = upper(text)
  3  /

2 rows updated.

But not like this

SQL> insert into t values (3, 'Goodbye');
insert into t values (3, 'Goodbye')
            *
ERROR at line 1:
ORA-00913: too many values

This following is still valid, and again shows that naming the columns in an INSERT statement is a good practice.

SQL> insert into t (id, text) values (3, 'Goodbye');

1 row created.