Oracle 9i PL/SQL Cursors and Joins

From: Steve <artigat1_at_hotmail.com>
Date: 14 Jan 2003 09:07:50 -0800
Message-ID: <d2dd7454.0301140907.62ec0c4a@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 Tue Jan 14 2003 - 11:07:50 CST