PL/SQL Error PLEASE HELP

Ok I have an odd situation.

We have 2 databases (one development and one test). They both have a link to a respective development and test database:

TEST1 -> LINK -> TEST2
DEV1 -> LINK -> DEV2 I have a select that is failing in Test1 but does not fail in Dev1.

here is an example of the select:

SELECT E.AAA_DATE, E.FIRST_NAME
FROM EMPL_RECORD E, EMPL_NAME I,
WHERE E.ID = 'XXXXXX'
AND I.ID(+) = E.ID EMPL_RECORD is a SYNONYM that points across the ABCLNK to EMPL_RECORD_VW on the TEST2 DB. (or the DEV2 database depending on which environment you are in).

So when running this select it hits the EMPL_RECORD synonym goes across the link to the EMPL_RECORD_VW which is built against the EMPL table.

Does this make sense so far? I hope so... :)

Anyway, here is the error I get when running this in TEST1:

Error:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-02063: preceding line from ABCLNK

When I Run From DEV1 it runs fine. IT goes across the ABCLNK to EMPL_RECORD_VW and then accesses the EMPL Table and returns the expected result.

I have looked at the NLS_DATE formats and they are the same. I believe the problem lies with the TEST2 database (referenced by the ABCLNK).

Here is some other info that might help:

If I Run This Select:

TEST1 db:
SELECT * FROM EMPL_RECORD_VW_at_ABCLNK where rownum < 50;

ERROR: ORA-00942 Table/view does not exist ORA-02063 preceding line from ABCLNK.

To get this select to work I have to specify the owner:

SELECT * FROM OWNER.EMPL_RECORD_VW_at_ABCLNK where rownum < 50 and it runs successfully.

Now, I do not have to specify the owner when running the above query in DEV1. It goes across the link and finds the EMPL_RECORD_VW successfully.

In DEV2 there is a public synonym named EMPL_RECORD_VW which is why I believe I do not have to specify the owner when selecting from DEV1 across the link to DEV2.

Does there need to be a PUBLIC synonym in TEST2? I believe that would fix the problem with having to specify the owner when going across the link but would that fix the NON NUMERIC problem mentioned above. I dont think it will.

Also, If I remove the OUTER JOIN from the first select statement it will run in both TEST1 and DEV1. Why is that? What exactly is this outer join doing?

Thanks for any help. If you need more info, I can provide it.

--
Chris Taylor


Sent via Deja.com http://www.deja.com/
Before you buy.