|
Error in cursor when adding another cursor
Sorry the title could not be more informative. I have a weird error where a code fragment appears to work fine, but when another (unrelated) cursor is added, an exception is raised with the first one.
Here's the SQL outside of the cursor
sql Code:
 Original
- sql Code |
|
|
|
SQL> SELECT cd.company_id, lc.dest_id, cd.data_scale, MIN(fy.financial_year) AS FinYear 2 FROM company_details cd, merged_company mc, linked_company lc, financial_year fy, company_details cd2 3 WHERE cd.group_id = 303 4 AND cd.company_id = 133787 5 AND fy.company_id = cd.company_id 6 AND fy.latest_flag = 1 7 AND mc.a_id = cd.company_id 8 AND lc.dest_id = mc.b_id 9 AND cd2.company_id = lc.dest_id 10 GROUP BY cd.company_id, lc.dest_id, cd.data_scale; COMPANY_ID DEST_ID DATA_SCALE FINYEAR ---------- ---------- -------------------------------------------------------------------------------------------------------------------------------- --------- 133787 154326 m 01-JAN-00 Elapsed: 00:00:00.04
Here's the cursor running correctly:
sql Code:
| Original
- sql Code |
|
|
|
SQL> SQL> DECLARE 2 CURSOR cur IS 3 SELECT cd.company_id, lc.dest_id, cd.data_scale, MIN(fy.financial_year) AS FinYear 4 FROM company_details cd, merged_company mc, linked_company lc, financial_year fy, company_details cd2 5 WHERE cd.group_id = 303 6 AND cd.company_id = 133787 7 AND fy.company_id = cd.company_id 8 AND fy.latest_flag = 1 9 AND mc.a_id = cd.company_id 10 AND lc.dest_id = mc.b_id 11 AND cd2.company_id = lc.dest_id 12 GROUP BY cd.company_id, lc.dest_id, cd.data_scale; 13 14 15 ScaleFactor NUMBER; 16 17 BEGIN 18 FOR rec IN cur loop 19 NULL; 20 END loop; 21 END; 22 / PL/SQL PROCEDURE successfully completed. Elapsed: 00:00:00.00
And here's the exception when a redundant additional cursor has been added.
sql Code:
| Original
- sql Code |
|
|
|
SQL> SQL> DECLARE 2 CURSOR cur IS 3 SELECT cd.company_id, lc.dest_id, cd.data_scale, MIN(fy.financial_year) AS FinYear 4 FROM company_details cd, merged_company mc, linked_company lc, financial_year fy, company_details cd2 5 WHERE cd.group_id = 303 6 AND cd.company_id = 133787 7 AND fy.company_id = cd.company_id 8 AND fy.latest_flag = 1 9 AND mc.a_id = cd.company_id 10 AND lc.dest_id = mc.b_id 11 AND cd2.company_id = lc.dest_id 12 GROUP BY cd.company_id, lc.dest_id, cd.data_scale; 13 14 CURSOR fy_cur(CID NUMBER, FYEAR DATE) IS 15 SELECT 16 FROM financial_year fy 17 WHERE company_id = CID 18 AND latest_flag = 1 19 AND FINANCIAL_YEAR < FYEAR 20 ORDER BY financial_year ASC; 21 22 ScaleFactor NUMBER; 23 24 BEGIN 25 FOR rec IN cur loop 26 NULL; 27 END loop; 28 END; 29 / FROM company_details cd, merged_company mc, linked_company lc, financial_year fy, company_details cd2 * ERROR at line 4: ORA-06550: line 17, COLUMN 3: PL/SQL: ORA-00936: missing expression ORA-06550: line 16, COLUMN 65530: PL/SQL: SQL Statement ignored
Here's the db info.
sql Code:
| Original
- sql Code |
|
|
|
SQL> SELECT * 2 FROM v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production CORE 9.2.0.1.0 Production TNS FOR 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production
Thanks in advance.
|
Dev Shed Forums Sponsor:
April 18th, 2006, 06:43 PM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
