Re: PL/SQL Error PLEASE HELP

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

Usually ora-1858 error message means that you compare character string to a number, and character string cannot be converted to number using default conversion. Check if ID columns in both tables have the same datatype. Also if ID is number, don't use quotes when comparing it to a constant. I don't think this error has anything to do with links.

> 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 - 15:16:19 CDT