Re: Oracle 9i PL/SQL Cursors and Joins

From: Ben Stafford <bstafford_at_sqlinsight.com>
Date: Thu, 16 Jan 2003 02:00:18 GMT
Message-ID: <SmoV9.155$05.31375546@newssvr30.news.prodigy.com>

I was able to duplicate the error in my own query, and truly the only problem is the use of "LEFT JOIN". If you replace the from and where clauses of the first cursor with the following text you should have no problems and the same expected results. I'm still investigating to find out why 'left join' is causing this.

...
FROM

  loc_location l2,
  map_locationType mlt2,
  loc_type lt2

WHERE
  l2.locationID = mlt2.locationID(+)
AND mlt2.objectTypeID = lt2.objectTypeID(+) AND l2.locationID = l.locationID
...

Ben Stafford
www.sqlinsight.com

"Steve" <artigat1_at_hotmail.com> wrote in message news:d2dd7454.0301140907.62ec0c4a_at_posting.google.com...
> Hi,
>
> I'm writing a procedure to get back results from a SQL statement as
> XML. The structure of the XML means I need to have a cursor in the
> select statement. This normally works fine (the second cursor is ok
> and the code runs fine if I cut the first cursor), but this time I
> need a join in there too and I just get an internal error. The code
> below is what I'm running in SQLPlus worksheet and it also gives an
> internal error. Are you not able to join tables within cursors?
>
> Any help would be greatly appreciated.
>
> Steve
>
> The select statement is:
>
> SELECT l.locationID, l.locName,
> CURSOR(
> SELECT DISTINCT( lt2.displayName )
> FROM loc_location l2
> LEFT JOIN map_locationType mlt2 ON mlt2.locationID =
> l2.locationID
> LEFT JOIN loc_type lt2 ON lt2.objectTypeID =
> mlt2.objectTypeID
> WHERE l2.locationID = l.locationID
> ) AS TYPEDISPLAY,
> CURSOR(
> SELECT mla1.osapr, mla1.addressLine, mla1.posttown_nm,
> mla1.postcode, mla1.street1, mla1.street2,
> mla1.siteID, mla1.latitude, mla1.longitude
> FROM map_locationAddress mla1
> WHERE mla1.locationID = l.locationID
> ) AS ADDRESS
> FROM loc_location l
> WHERE l.logicallyDeleted = 'N'
> AND l.locAccepted = 1
> AND l.locationID = 59858405;
>
> The error is:
>
> SELECT l.locationID, l.locName, dc.phoneNumber,
> *
> ERROR at line 1:
> ORA-00600: internal error code, arguments: [kokbnp2], [904], [], [],
> [], [],
> [], []
Received on Wed Jan 15 2003 - 20:00:18 CST