Category Archives: Oracle

Small test with FORALL SAVE EXCEPTIONS

This one I think I know the answer allready, but just wanted to test for sure.The question I asked myself is “When does FORALL SAVE EXCEPTIONS jump to the EXCEPTION clause?”. I’m pretty sure the answer is “After it completes the FORALL, and if – and only if – there are exceptions”.

Script: Synchronizing sequences to max(value)

Today I needed to synchronise several sequences with the max(value) existing in the primary key column. I did a quick google search to see if I found a script which could do this for me, but didn’t find anything (well didn’t spend to long either). So I wrote a script instead.

Strange behavior with EBR II

This is an update on the previous article “Strange behavior with EBR“.To try to understand a little more I have tested some more. The result suggest that what’s inherited in my latest edition is actually not what I first thought (read last article). Let’s have a look.

Strange behavior with EBR

I got some strange behavior with the Edition Based Redefinition feature this other day. I have 4 editions (RELEASE_1, RELEASE_2, RELEASE_3 and RELEASE_4). I create a package FOO with one procedure P1 in RELEASE_1. Then i recreate the package in RELEASE_2 adding a second procedure P2. Now I expect RELEASE_3 (and RELEASE_4) to have inherited […]

Testing solution to MERGE with sequence NEXTVAL

In my last blogpost (“Strange behavior in MERGE with sequence NEXTVAL”) I had a link to article written by Bob Jankovsky. In this article is suggest solving the issue with MERGE and nextvalue by returning the nextvalue from a function. In this blogpost I’ll do some tests and see how this behaves (regarding timings).

Strange behavior in MERGE with sequence NEXTVAL

Today we learned the following: “When using a sequence in a MERGE statement, the nextvalue is generated for all rows both updated and inserted (hence all rows in the source).” Our initial reaction was that this must be a bug, but is it really?

Use default parameter/setting (row) if not set specific for entity

The other day I was asked about the following: “We have a table VARIABLE and Variable entity. A new requirement is to restrict availability of a limited number of these entities to chosen customers.” I replied: “The volumes?” And the response was: “Quite low – now around 70 rows, it might slightly exceed 100 in […]

SQL Developer, Database Copy and TRUNCATING unwanted tables

I just experienced something scary with SQL Developer and the “Database Copy” utility. One table in production was truncated by a mistake. I have always been a little against using the SQL Developer functionality “Database Copy …” in production, but for this one task during deployment of a new release, this customer (including me) have […]