-
Hi,
In one of my PL/SQL Blocks I have a cursor declaration as follows:
CURSOR CUR_MS_SITE IS
SELECT *
FROM CSS_DBA.CSS_MS_SITE
WHERE MS_SITE_ID = v_MS_SITE_ID
FOR UPDATE;
Where MS_SITE_ID is the PK on this table and v_MS_SITE_ID is a predefined variable.
When I tried to execute this, I am getting an error :
ERROR at line 135:
ORA-06550: line 135, column 24:
PLS-00225: subprogram or cursor 'CUR_MS_SITE' reference is out of scope
Any ideas to resolve this pl..
MS Reddy
-
-
Show me your PL/SQL block.
-
Resolved it. I was using cursor name instead of the variable.
It is some thing like this.
OPEN CUR_MS_SITE;
FETCH CUR_MS_SITE INTO v_CSS_MS_SITE;
IF (CUR_MS_SITE%NOTFOUND) THEN
INSERT INTO CSS_DBA.CSS_MS_SITE (MS_SITE_ID,MS_SITE_DESC)
VALUES (v_MS_SITE_ID,v_CSS_SHIPMENT_TEMP.MS_SITE_DESC);
insert_MS_SITE := insert_MS_SITE + 1;
ELSE
IF cur_ms_site.MS_SITE_DESC <> v_CSS_SHIPMENT_TEMP.MS_SITE_DESC THEN
-- Here instead of using the cursor record reference, i used the cursor name(CUR_MS_SITE.MS_SITE_DESC instead of v_CSS_SHIPMEMT_TEMP.MS_SITE_DESC).
UPDATE CQCS_DBA.CSS_MS_SITE
SET MS_SITE_DESC = v_CSS_SHIPMENT_TEMP.MS_SITE_DESC
WHERE CURRENT OF CUR_MS_SITE;
update_MS_SITE := update_MS_SITE +1;
END IF;
END IF;
CLOSE CUR_MS_SITE;
Thanks
ms reddy
-
be sure that the owner of the proc and the refered to objects are one and the same. else there should be grants to the owner of the proc. try this.
-
Try to use that construction:
OPEN CUR_MS_SITE;
LOOP
FETCH CUR_MS_SITE INTO v_CSS_MS_SITE;
EXIT WHEN CUR_MS_SITE%NOTFOUND;
..... your code ....
END LOOP;
CLOSE CUR_MS_SITE;
You can use CUR_MS_SITE%ROWCOUNT to get number of rows returned.