Al Willis wrote:
> Paul,
>
> Thanks for your patient answer.
>
> I'm only using the version of Oracle downloaded from their website. I'm
> just a poor developer trying to get Oracle to work with my application.
>
> When I try the query from SQL*Plus I get the following errors:
>
> SP2-0642: SQL*Plus internal error state 2133, context 0:0:0
> Unsafe to proceed
> SP2-0642: SQL*Plus internal error state 2221, context 4294967295:4:0
> Unsafe to proceed
>
> I tried using the following and it worked:
>
> SELECT A.* FROM IM.LOOPDAT A
> WHERE LOOPNO IN (
> SELECT DISTINCT XA.LOOPNO FROM IM.LOOPDAT XA
> LEFT OUTER JOIN IM.LOOPCON XB ON XB.LOOPNO = XA.LOOPNO
> WHERE (XA.LOOPNO LIKE '24%') )
>
> Unfortunately this doesn't work when I have more than one search field.
>
> As you've hinted I may have a problem with the number of fields in the
> table. I believe that I have a valid reason for the table structure and it
> works OK under Interbase, and SQL Server. The end-users really don't have
> to manually input into the fields. My application populates most, (but not
> all) of the fields. However for searches and updates all of the fields have
> to be available whether they are in one table or spread among several.
>
> I had reduced the problem down to the minimum for problem reproduction. My
> application is really joining 8 tables to LOOPDAT, not just the 1 that I
> showed. I just hated to have to split up LOOPDAT because of the additional
> work that it entails. My application is being written to handle 6 different
> database backends, so I may have to bite the bullet one more time and make a
> change to accommodate a database that responds differently to some
> particular input or setup. From reading the newsgroup postings on
> ORA-03113, it seems to be somewhat difficult to solve, so I going to
> surrender now and take a different path.
>
> Many thanks for your help.
>
> Al
>
>
>
You're running into Oracle's poor implementation of ansi joins.
Your specific case is solved in 9.2.0.4 - others are not.
See if you can rewrite, using the 8i outer join standard; you
have a better chance then.
BTDT, hth
--
Regards,
Frank van Bortel
Received on Sat Jan 10 2004 - 06:46:13 CST
