For all those pushing data around, especially dirty data, this one is for you.

Today I was preparing to process data I loaded from a spreadsheet.
A simple filter was required - to ignore the header row, had it been included.

I'm lucky enough to be working on 19c, and I remembered that a reasonably new function should help me out with all many of data loading issues. With a quick scan of my favourite reference manual, I found VALIDATE_CONVERSION.

For example, this gives me 'ORA-01722 invalid number' because of the header row I failed to exclude.

select c.*
from my_data_load c
order by to_number(seq);

But without the to_number, the order returns incorrectly.

SEQ
-----
1
10
12
140
2
Order

Sure, we could say
where seq != 'Order'

But this tool will have more than one use

select c.*
from my_data_load c
where validate_conversion(seq as number) = 1
order by to_number(seq);
SEQ
-----
1
2
10
12
140

Recreate this result using

select * from (
select 'Order' seq from dual
union all select '1' from dual
union all select '2' from dual
union all select '10' from dual
union all select '12' from dual
union all select '140' from dual
)
where validate_conversion(seq as number) = 1
order by to_number(seq)

And see typical return values (0 or 1) for conversion attempts using

select
 validate_conversion('1' as number) num1
 ,validate_conversion('2' as number) num2
 ,validate_conversion('1b' as number) num_not
 ,validate_conversion('01-01-2001' as date) date1
 ,validate_conversion('30-02-2000' as date, 'dd-mm-yyyy') date2
from dual; 
 NUM1 NUM2 NUM_NOT DATE1 DATE2
---------- ---------- ---------- ---------- ----------
 1 1 0 0 0

It's one of a few tools I'm using to make data loading life easier, and processing data in sets using SQL, not looping & context switching within PL/SQL.

The kicker, turns out this has been available since 12.2.

It turns out the usage of validate_conversion in PL/SQL will give the compilation warning PLW-06009. And so does the alternative to check if this returns null:
to_date('z-z-2001' default null on conversion error, 'dd-mm-yyyy')

More examples available from
LiveSQL
Tim Hall
Oren Nakdimon
19c Documentation

My journey into 12c continues with the use of identity columns, this time regarding data that had been imported from another database, but sequences haven't been updated.

Ensuring the next number returned from a sequence matches the current value from the table appears to be a common problem, my thoughts are described here. The biggest trouble is linking up the sequence to the column so we could automate the process.

TL;DR

We can now execute an ALTER statement to reset/align the sequence to a value appropriate to the column
alter table my_table modify (id generated as identity START WITH LIMIT VALUE);

The next insert will be problem free, and we don't need to do anything else.

The Underlying Sequence

Identity columns use a sequence under the hood, as hinted in the statement diagrams for the create table syntax.

I thought perhaps I could use my old technique on these sequences, but I forgot where to look for the name of the sequence associated with the identity column.

As usual Tim Hall pointed me in the right direction

select table_name, column_name, generation_type, sequence_name
from all_tab_identity_cols
where table_name = 'MY_TABLE'
TABLE_NAME COLUMN_NAME GENERATION_TYPE SEQUENCE_NAME
------------- ----------- --------------- -------------
MY_TABLE ID BY DEFAULT ISEQ$$_430382

I was reminded shortly after that you can also see the data default referenced within SQL Developer


However it turns out if you try to apply alter sequence to those generated by the system from the table DDL, you get the following error.
ORA-32793: cannot alter a system-generated sequence
Fancy that.

So to increment the sequence beyond the most recent ID, I could make a bunch of requests to the next value of the sequence.

declare
 l_val pls_integer;
begin
 for i in 1..240 -- use the difference between .nextval and max(id)
 loop
 l_val := ISEQ$$_430382.nextval; -- change to sequence returned from all_tab_identity_cols
 end loop;
end;
/

This might be considered un-elegant, a dirty way to fix the problem. We're on 12c, surely there's a better way.

12c solution

The sequence creation is built into the DDL, so why not maintenance? Check out the help for the ALTER TABLE command.

STARTWITHLIMIT VALUE, which is specific to identity_options, can only be used with ALTERTABLEMODIFY. If you specify STARTWITHLIMIT VALUE, then Oracle Database locks the table and finds the maximum identity column value in the table (for increasing sequences) or the minimum identity column value (for decreasing sequences) and assigns the value as the sequence generator's high water mark. The next value returned by the sequence generator will be the high water mark + INCREMENTBYinteger for increasing sequences, or the high water mark - INCREMENTBYinteger for decreasing sequences.

Now illustrate this in action.

drop table seq_reset_test;
create table seq_reset_test -- basic table with my suggested identity column options
 (id number generated by default on null as identity
 ,CONSTRAINT seq_reset_test_pk PRIMARY KEY (id)
 ,label varchar2(20)
);
Table SEQ_RESET_TEST created.
-- Will be given first ID of 1
insert into seq_reset_test (label) values ('Initial');
1 row inserted.
-- Simulate update of db without synchronising sequence
update seq_reset_test set id = 1000 where id = 1;
1 row updated.
-- ID too high for sequence
select * from seq_reset_test;
 ID LABEL 
---------- --------------------
 1000 Initial 
-- Find sequence name from all_tab_identity_cols
select ISEQ$$_404558.currval from dual;
 CURRVAL
----------
 1
-- This will use ID 2, risking PK violation
insert into seq_reset_test (label) values ('Second');
1 row inserted.
-- Magic alter statement
alter table seq_reset_test modify (id generated by default on null as identity start with limit value);
Table SEQ_RESET_TEST altered.
-- This will use the updated sequence, avoiding max(id) value
insert into seq_reset_test (label) values ('Third');
1 row inserted.
-- Proof
select * from seq_reset_test;
 ID LABEL 
---------- --------------------
 1000 Initial 
 2 Second 
 1001 Third 
select ISEQ$$_404558.currval from dual;
 CURRVAL
----------
 1001

Conclusion

So it seems we can throw away that old reset_seq.sql file?
Particularly if the replacement is now an ALTER TABLE command that doesn't need to know about any values.

alter table my_table modify (id generated /*by default on null*/ as identity START WITH LIMIT VALUE);

In comments the optional definition settings I find most useful, which would have been defined within the table DDL.

If Oracle can manage by IDs with sequences and do all the grunt work for me, go right ahead.

Related Posts

12 Column Upgrades
Decommissioning Triggers in 12c

I've been operating with a 12c environment this year and I can see some standard patterns of mine changing.

One is the use of triggers, or lack thereof. I live in a city with a certain evangelist who does not like triggers, so I was happy to see Sven Weller's "perfect trigger" post. The answer is: there is no trigger.


Well, it's one thing to say 'create all new tables like this', but what about our existing structure? What process should we follow to decommission these triggers?

If you take the weekly DB Design quiz at the PL/SQL Challenge website, you may recognise this example as a quiz from Sept 2016.

Existing Framework

Consider a table with a structure similar to the one I described in a post of mine from 2010

drop table orbiters;
drop sequence orbiter_seq;
create sequence orbiter_seq;
create table orbiters
 (id number not null
 ,CONSTRAINT orbiter_pk PRIMARY KEY (id)
 ,position number not null
 ,planet varchar2(15) not null
 ,name varchar2(30) not null
 ,distance number not null
 ,created_date date not null
 ,created_by varchar2(50) not null
 );
create or replace TRIGGER orbiters_biur BEFORE INSERT OR UPDATE ON orbiters FOR EACH ROW DECLARE
BEGIN
 IF INSERTING THEN
 :NEW.id := COALESCE(:NEW.id , orbiter_seq.NEXTVAL);
 :NEW.created_date := COALESCE(:NEW.created_date , SYSDATE);
 :NEW.created_by := COALESCE(:NEW.created_by , apex_application.g_user, USER);
 END IF;
END;
/
insert into orbiters (position,planet,name,distance) values (3,'Earth', 'Luna', 384) ;
insert into orbiters (position,planet,name,distance) values (3,'Earth', 'SpaceX', 400/1000);
insert into orbiters (position,planet,name,distance) values (3,'Earth', 'ISS', 400/1000);
select id, name from orbiters;
ID NAME
1 Luna
2 SpaceX
3 ISS

It's not rocket surgery...

Audit columns

There are plenty of options about in regard to auditing your columns and tracking history, but for the pattern you're likely to see regularly, there is no need for a trigger.
alter table orbiters modify created_date default sysdate ;

alter table orbiters modify created_by default coalesce(
 sys_context('APEX$SESSION','app_user')
 ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
 ,sys_context('userenv','session_user')
 );

Sven's coalesce to resolve the username when the environment may or may not be APEX is neat.

Default using sequence

The sequence itself has no performance benefit using an assignment vs selecting from dual. Instead, it needs to either be in the insert statement, or as a default value as 12c (finally) allows.

alter table orbiters modify id default orbiter_seq.NEXTVAL;
drop trigger orbiters_biur;

If you get ORA-02262, check your sequence exists and you've typed it properly.

Identity columns

Identity columns are ideal for new tables, but existing tables would require more work.

Identity columns actually use a sequence under the hood, so while I thought I was on the right track by using this syntax:
alter table orbiters modify id GENERATED BY DEFAULT ON NULL AS IDENTITY start with 10;
It returns the following error.
ORA-30673: column to be modified is not an identity column
A few bloggers mention this fact, someone posted the question on Reddit of all places.

But I think the value equation of creating a new column and shifting data, foreign key references etc is outweighed by the fact that performance is better regardless of using identity vs sequence. Tim demonstrates this here. I discuss resetting sequences in identity columns here.

Definitely remove the trigger

Here is the best part:
drop trigger orbiters_biur;

Conclusion

To decommission triggers and replace them with new 12c features, use these steps:

alter table orbiters modify id default orbiter_seq.NEXTVAL;
alter table orbiters modify created_date default sysdate;
alter table orbiters modify created_by default sys_context('APEX$SESSION','app_user');
drop trigger orbiters_biur;

Happy #db12c!

Related Posts

12 Column Upgrades
Synchronise Identity Column

While playing with 12c I tried the upgrade to the DEFAULT column syntax that now allows sequences.

I came across a basic error, but it's just a small trap for new players.

CREATE TABLE seq_test(a NUMBER)
/
ALTER TABLE seq_test MODIFY (a NUMBER DEFAULT sage_seq.NEXTVAL)
/
SQL Error: ORA-02262: ORA-2289 occurs while type-checking column default value expression
*Cause: New column datatype causes type-checking error for existing column
 default value expression.
*Action: Remove the default value expression or don't alter the column
 datatype.

Investigating ORA-02262 returns next to nothing. It's a red herring anyway because I didn't realise the ORA-2289 staring me in the face before I checked if my database had that sequence yet.
ORA-02289: sequence does not exist

Typical APEX table definitions will never be the same again, though an even more elegant solution is to use IDENTITY columns. See the performance benefits at oracle-base.com.

Related Posts

Decommissioning Triggers in 12c
Synchronise Identity Column

I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha I encountered.

Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.

Creating inline functions within a SQL statement was relatively easy.

WITH
 FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
 BEGIN
 RETURN p_id;
 END;
SELECT with_function(event_no)
FROM events
/

However a slight adjustment is required for DML. The documentation suggests that

• If the top-level statement is a DELETE, MERGE, INSERT, or UPDATE statement, then it must have the WITH_PLSQL hint.

but does not give any examples, which I think is unfortunate - but thanks Tim for getting us started ;-)

UPDATE /*+ WITH_PLSQL */ events e
SET e.org_id =
 (WITH
 FUNCTION inline_fn(p_id IN NUMBER) RETURN NUMBER IS
 BEGIN
 RETURN p_id;
 END;
 SELECT inline_fn(e.org_id)
 FROM dual);
/

Without the hint Oracle returns

ORA-32034: unsupported use of WITH clause

but I was getting

ORA-00933: SQL command not properly ended

What clued me in was the brief highlight SQL Developer makes over the statement before it executes. For me this paused at the return statement within the function.

I happened to be using one of the pre-built Oracle Developer VMs to play around, and it turns out the one I'm using has SQL Developer 4.0.0.13 supplied.

That particular version doesn't seem to be aware of this bleeding edge feature. I vaguely recall seeing this mentioned somewhere probably in the vicinity of thatJeffSmith fellow. I tried it in the command line SQL*Plus and it worked fine against the 12.1.0.1 instance.

It does ring a clear bell for once upon a time circa 2006 working on Oracle 9i or 10g when I sent an email to a colleague containing a SQL statement including a WITH clause.

He didn't have success in his Oracle 8i SQL*Plus windows client either... oh how I miss thee.

Did you know that Oracle 12c has been available for Windows for a week already?

https://blogs.oracle.com/UPGRADE/entry/oracle_database_12c_is_available

I've downloaded it, but let's see how long it takes to find a chance to install it, let alone play!

I haven't installed 12c yet because
a) I haven't had the time
b) my Linux skills aren't the best

What I have done though is read through some of the documentation in the New Features manual.

The APEX features are just regurgitating what's in 4.2, since that's what's shipped out of the box with 12c - but I always like to look through what's improved with SQL and PL/SQL.

No doubt many of you may have seen or read presentations on what's coming up as early as last year, but for those who haven't - here are some I've spotted that made the cut for 12c and I look foward to tinkering with

1. PL/SQL function in the WITH clause. I think this may sometimes come in handy, and other times it will be abused and lead to some real ugly code. Performance will be interesting.
2. Increased size limit for VARCHAR2 etc - no doubt people have been asking for this since at least 8i...
3. dbms_utility.expand_sql_text - Recursively replace any view references with relevant tables. This would have been brilliant in a project I often visit where there are views upon views upon views.
4. utl_call_stack - returns structured information about an exception stack. Tom Kyte has already blogged about this one, I'd suggest he's been pushing for something like this for a while.
5. Sequences can be used as column DEFAULT. Yes. So very yes. Though again I will be interested in performance, since 11g's :new.id := my_seq.nextval was the same as selecting from dual.
6. Default value on explicit null insertion - for those times where you really must have a default value in the column!
   
7. Identity columns - full examples are scant, but looks interesting.
8. row_limiting_clause - for top-N reporting, apparently an ANSI standard. This definitely needs exploring.
9. Row pattern matching. Um, wow. Big data influences?
10. Cross apply; Lateral clauses. From what I think is going on here, this could be mighty handy.
11. SYS_CONTEXT sys_session_roles - role interrogation for current session - cool, and will be very handy. A little late for many Forms applications, though.
12. TRUNCATE TABLE CASCADE - where is that sledgehammer?

I listened to an Oracle Magazine podcast recently talking about MySQL - I wonder if the collaboration going on here has influenced 12c features, because it sounds like the quality of MySQL is enhanced with Oracle IP.

Of course, if you're in Australia or would like to visit, the Insync13 national conference series already has a bunch of 12c presentations that span JDeveloper, Optimizer, Coherence, Weblogic and general features.

For many geeks out there, throw todays productivity out the window - Oracle 12c is now available for Linux/Solaris.

I'm not sure the official press release is out, the documentation sure isn't - but Twitter sure is abuzz.

While searching, I also spotted an article saying the pluggable database component will be a costed option.

Let's see how long it takes Tim Hall to release a string of articles on new features.

Here I was betting on a July release... at least all our Insync13 onference submissions for 12c can be ratified!