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”.

Continue Reading »

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.

Continue Reading »

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.

Continue Reading »

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 the version from RELEASE_2. The strange thing is that it seems like the specification is inherited from RELASE_2 (with both procedures P1 and P2), but the body is “inherited” from RELEASE_1 (marked as INVALID in USER_OBJECTS_AE). The following article shows the commands to reproduce the issue.
(Note! I’m on a Oracle Exadata (running on Linux 7) with Oracle Database version 19.23.0.0.0.)

Continue Reading »

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).

Continue Reading »

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?

Continue Reading »

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 the future. No LOBs.”

The developer continued: “So far we have been doing such filtering by applying conditions on FK columns referencing COMPANY table. We want to do the same here. But there’s a small difference: a Variable can be either visible to all customers (no restrictions) or to 1 or more chosen customers. So, an intersection table is quite a natural choice. E.g. COMPANY_VARIABLE table with 2 columns only: FK_VARIABLE, FK_COMPANY. Only FK_VARIABLE would be not null. So, the FK referencing COMPANY table would be optional.

I’d like to avoid keeping many rows in the new table for a variable without visibility restrictions. For such variables it would be enough to have only 1 row with null in FK_COMPANY. Otherwise, it would be

  • too cumbersome,
  • error-prone (when adding a new company or a no-restriction variable, you’d always have to add many rows to the intersection table)
  • and taking DB storage unnecessarily.

In turn, if a variable were to be restricted, we would add a new (VARIABLE_ID, COMPANY_ID) row to the table.”

Continue Reading »

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 always used this feature. The customer have two somewhat static tables in a meta database (in a test zone), where they store table and columns comments (don’t ask me why they are not using the COMMENT feature in the Oracle database). During a release the customer copy the data from these two tables into two production databases. By accident or some kind of bug SQL Developer started to truncate ALL tables in the schema.

Continue Reading »