Okay, I am stumped. Currently, i am a 734 system that talks to another 734 system constantly via a set of synonyms. My other 734 system is now upgradeing to 9i Rel2. With this new release, oracle dropped support for connectivity between 734 and 9i. So, as a workaround, we were told to create a temp instance in 8i, create a series of synonyms in the 8i system pointing to the new 9i system. Then, I recreate my syns and link in 734 an all should be well.
Well, we decided to test this with two 9i systems talking using the 8i as a the go between. Created my links and syns. Then, ran a select count(*) from in sqlplus on my 9i system. I got the correct count, via the 8i system, of the table in the tgt 9i system. All seemed well. The, i did a simple script that ran this same query and output the count to my screen. Big Blowup. Immediately got an ora-04052 error saying there was a problem finding the remote object.
If, in my 9i system, I create a synonym to a table on the 8i system and try to do a select via the synonym, I get an error message saying the synonym translation is no longer valid.
Can someone enlighten me? I know that 8.1.6 is desupported, but if it works fine at the sqlplus level, why would it not work from within the pl/sql block?