Re: PL/SQL Error PLEASE HELP

You need at least to create a public synonym of "EMPL_RECORD_VW" on dev1, so that

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.

will not occur.

Hope this helps.

Guang

In article <8pt1p2$fkl$1_at_nnrp1.deja.com>,   Chris Taylor <ctaylor_at_asapsc.com> wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Sep 15 2000 - 14:11:43 CDT