2

Things that make you go VAAARRRGGGHHHH ! – ORA-38104 in a Stored Program Unit

 1 year ago
source link: https://mikesmithers.wordpress.com/2023/08/14/things-that-make-you-go-vaaarrrggghhhh-ora-38104-in-a-stored-program-unit/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Things that make you go VAAARRRGGGHHHH ! – ORA-38104 in a Stored Program Unit

I do have some empathy with Lauren James getting quite cross at work.
OK, it’s not as pressurised as striving to reach a World Cup Quarter Final, but I am known to become somewhat annoyed when Oracle presents me with :

ORA-38104: Columns referenced in the ON Clause cannot be updated

The error itself is reasonable enough. The thing is though, that the offending merge statement will not trigger a compile-time error if it’s in a Stored Program Unit. Oh no.
Instead, Oracle will let you carry on in blissful ignorance and only give you the bad news at runtime…

A simple example

Let’s say we have a table called TEAM_AVAILABILITY :

create table team_availability
(
squad_number number,
first_name varchar2(250),
last_name varchar2(250),
available_flag varchar2(1)
)
/
insert into team_availability( squad_number, first_name, last_name)
values( 1, 'Mary', 'Earps');
insert into team_availability( squad_number, first_name, last_name)
values( 2, 'Lucy', 'Bronze');
insert into team_availability( squad_number, first_name, last_name)
values( 3, 'Niamh', 'Charles');
insert into team_availability( squad_number, first_name, last_name)
values( 4, 'Keira', 'Walsh');
insert into team_availability( squad_number, first_name, last_name)
values( 5, 'Alex', 'Greenwood');
insert into team_availability( squad_number, first_name, last_name)
values( 6, 'Millie', 'Bright');
insert into team_availability( squad_number, first_name, last_name)
values( 7, 'Lauren', 'James');
insert into team_availability( squad_number, first_name, last_name)
values( 8, 'Georgia', 'Stanway');
insert into team_availability( squad_number, first_name, last_name)
values( 9, 'Rachel', 'Daly');
insert into team_availability( squad_number, first_name, last_name)
values( 10, 'Ella', 'Toone');
insert into team_availability( squad_number, first_name, last_name)
values( 11, 'Lauren', 'Hemp');
insert into team_availability( squad_number, first_name, last_name)
values( 12, 'Jordan', 'Nobbs');
insert into team_availability( squad_number, first_name, last_name)
values( 13, 'Hannah', 'Hampton');
insert into team_availability( squad_number, first_name, last_name)
values( 14, 'Lotte', 'Wubben-Moy');
insert into team_availability( squad_number, first_name, last_name)
values( 15, 'Esme', 'Morgan');
insert into team_availability( squad_number, first_name, last_name)
values( 16, 'Jess', 'Carter');
insert into team_availability( squad_number, first_name, last_name)
values( 17, 'Laura', 'Coombs');
insert into team_availability( squad_number, first_name, last_name)
values( 18, 'Chloe', 'Kelly');
insert into team_availability( squad_number, first_name, last_name)
values( 19, 'Bethany', 'England');
insert into team_availability( squad_number, first_name, last_name)
values( 20, 'Katie', 'Zelem');
insert into team_availability( squad_number, first_name, last_name)
values( 21, 'Ellie', 'Roebuck');
insert into team_availability( squad_number, first_name, last_name)
values( 22, 'Katie', 'Robinson');
insert into team_availability( squad_number, first_name, last_name)
values( 23, 'Alessia', 'Russo');
commit;

We have a procedure that we can use to mark a player as being unavailable for selection :

create or replace procedure mark_player_unavailable( i_squad_number team_availability.squad_number%type)
is
begin
merge into team_availability
using dual
on
(
squad_number = i_squad_number
and available_flag is null
)
when matched then update
set available_flag = 'N';
end mark_player_unavailable;
/

When we create the procedure, everything seems fine…

Procedure MARK_PLAYER_UNAVAILABLE compiled

However, running it causes Oracle to do the equivalent of a VAR check, which results in :

ora38104_runtime.png?w=1017

As to why Oracle does this, other than to annoy me, I have no idea.
Then again, I’m not sure why I keep falling into this trap either.

To avoid this altogether, there are a couple of options…

Workarounds

We could simply use an update instead of a merge

create or replace procedure mark_player_unavailable( i_squad_number team_availability.squad_number%type)
is
begin
update team_availability
set available_flag = 'N'
where squad_number = i_squad_number
and available_flag is null;
end mark_player_unavailable;
/

update_workaround.png?w=501

However, if we still want to use a merge, then we need to use an inline view in the USING clause :

create or replace procedure mark_player_unavailable( i_squad_number team_availability.squad_number%type)
is
begin
merge into team_availability ta
using
(
select tau.squad_number
from team_availability tau
where tau.squad_number = i_squad_number
and tau.available_flag is null
) ilv   
on
(
ta.squad_number = ilv.squad_number
)
when matched then update
set ta.available_flag = 'N';
end mark_player_unavailable;
/

…which has the same result.

With my equanimity restored I can now reflect that yes, Womens football is different from the Mens. In the Womens game, England DO win penalty shoot-outs !

9b3f1c72f7506288479c021b361774553929d2c0e2eda91a4997a66e8e01acd9?s=42&d=https%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D42&r=G

Author: mikesmithers

Back in 1993, I discovered that I could get paid money for doing fun stuff with computers. Over the years, I've specialised in Oracle Databses as a developer, a DBA and sometimes, an architect. It's my evil alter-ego - The Antikyte - who writes a blog about my various technical adventures. Yes, that is his Death Star parked in the Disabled Bay. I currently live in the South-West of England with Deb, my long-suffering wife. View all posts by mikesmithers


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK