embedded implicit cursors in SQL [message #679441] |
Fri, 28 February 2020 17:20 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/5bff9f04ff3780a0cf870f90089b9c9f?s=64&d=mm&r=g) |
Calvin_007
Messages: 1 Registered: February 2020
|
Junior Member |
|
|
Hi there
I have never dealt with sql cursors so I need some help trying to remove the sql cursors and keep the hierarchy structure of the SQL statement:
SELECT sol.code,
TO_CHAR(swo.observation_datetime,'YYYY-MM-DD/HH24:MI:SS'),
swo.atmospheric_pressure, swo.ATMOS_PRESSURE_CONF_LEVEL,
CURSOR ( SELECT
SUBSTR(splo.pavement_lane_type_code, -1),
splo.surface_temperature, splo.SURFACE_TEMP_CONFIDENCE_LEVEL
FROM saw_pavement_observation spo, saw_pavement_lane_observation splo
WHERE splo.pavement_observation_id = spo.pavement_observation_id
AND spo.weather_observation_id (+) = swo.weather_observation_id),
CURSOR (
SELECT SUBSTR(splo2.pavement_lane_type_code, -1), splo2.sub_asphalt_temperature
FROM saw_pavement_observation spo2, saw_pavement_lane_observation splo2
WHERE splo2.pavement_observation_id = spo2.pavement_observation_id AND spo2.weather_observation_id = swo.weather_observation_id
)
FROM saw_observation_location sol, saw_weather_observation swo, saw_snowpack_observation sso
WHERE rownum <= 100 and sso.weather_observation_id (+) = swo.weather_observation_id AND sol.observation_location_id IN
(SELECT rol.observation_location_id
FROM saw_party_report_obs_location rol,saw_party_report spr,saw_party sp
WHERE rol.party_report_id = spr.party_report_id
AND spr.party_id = sp.party_id AND spr.report_type_code = '7110')
AND sol.observation_location_id = swo.observation_location_id
ORDER BY sol.code, swo.observation_datetime DESC
This obviously gives the -maximum open cursors exceeded- error.
DB max cursor=300
I guess the original developer used cursors to traverse each line for export into the resultant XML file: Report.xml
I still need the sql results to go into the xml report.
I am researching this but falling short at the moment.
Any help is greatly appreciated
Thank you!
|
|
|
Re: embedded implicit cursors in SQL [message #679443 is a reply to message #679441] |
Sat, 29 February 2020 00:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68650 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 100 characters when you format.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Quote:This obviously gives the -maximum open cursors exceeded- error.
This is not obvious for me unless the application does not close the outer cursor.
Quote:I still need the sql results to go into the xml report.
If you want a XML report directly from the query then write the query to directly generate the XML.
[Updated on: Sat, 29 February 2020 00:56] Report message to a moderator
|
|
|