Thursday, August 28, 2008

ORA-01008 on PL/SQL MERGE over link

If you get an "ORA-01008: not all variables bound" error when trying to do a MERGE INTO a remote table using local PL/SQL variables and local data from local tables, what do you do besides beat your head against the wall until it is soft and mushy like a sponge?
DECLARE
v_turd VARCHAR2 := 'Stinky';
BEGIN
MERGE INTO table@link m
USING (
SELECT
crap
, poop
, dood
, loaf
FROM local_table
WHERE num = 2
) u
ON (m.loaf = u.loaf)
WHEN MATCHED THEN UPDATE SET m.turd = v_turd, m.poop = u.poop
WHEN NOT MATCHED THEN INSERT VALUES (u.load, v_turd, u.crap, u.poop, u.dood, u.loaf);
COMMIT;
END;
Thankfully, it only took a couple of Google searches to find a solution offered by Carsten Herbe at Oracle's forums. The INSERT and UPDATE statements in the MERGE must only use data defined in the USING or literals (or maybe other stuff too). So...
DECLARE
v_turd VARCHAR2 := 'Stinky';
BEGIN
MERGE INTO table@link m
USING (
SELECT
crap
, poop
, dood
, loaf
, v_turd turd
FROM local_table
WHERE num = 2
) u
ON (m.loaf = u.loaf)
WHEN MATCHED THEN UPDATE SET m.turd = u.turd, m.poop = u.poop
WHEN NOT MATCHED THEN INSERT VALUES (u.load, u.turd, u.crap, u.poop, u.dood, u.loaf);
COMMIT;
END;
Sometimes I feel sorry for us computer nerds that have to deal with frustrating little things like a damned ORA-01008 that works perfectly in SQL when you are testing but as soon as you try to automate it with a variable you get your geek pee-pee whacked with a ruler. Bad geek, no work. Thankfully the clicker-web is littered with other suffering nerd-souls that have run into these problems on the smallest of free systems to multi-million dollar goliaths. Unlike other nagging problems this one didn't cause much suffering on my part. Hopefully by posting this here it will add to the Internet litter and maybe help others faster.

6 comments:

M.Moore said...

Got the note you left on my blog. Thanks.

Anonymous said...

Thank you VERY-VERY much! You saved our day! I was going to move 10.2.0.4 or 11G, as someone else has suggested.

Anonymous said...

Thanks, you saved my day too !

oggo said...

works perfect! thanks a lot!

Anonymous said...

Thank you much! Very helpful.

Anonymous said...

Bless your nerd soul! Your post worked and made me laugh. Thanks!