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?

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

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.