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.

Tuesday, August 26, 2008

Oracle instant client, Ubuntu, revisited

For those that want quick installation instructions assuming you have the installation files on the root of a mounted CDROM:

sudo -s
mkdir /opt/oracle
cd /opt/oracle
unzip /media/cdrom/instanclient-basic- (etc.version.blah.blah)
unzip /media/cdrom/instanclient-sdk- (etc.version.blah.blah)
unzip /media/cdrom/instanclient-sqlplus- (etc.version.blah.blah) optional
ln -s instantclient_11_1 client
cd client
ln -s libclntsh.so.11.1 libclntsh.so
ln -s libocci.so.11.1 libocci.so
ln -s libsqora.so.11.1 libsqora.so
tr -d '\r' < /media/cdrom/tnsnames.ora > /etc/tnsnames.ora
tr -d '\r' < /media/cdrom/sqlnet.ora > /etc/sqlnet.ora

To use SQLplus from a shell you need to add the ORACLE_HOME=/opt/oracle variable and add /opt/oracle/client to variable LD_LIBRARY_PATH to the /etc/bash.bashrc (for example).