-
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.
Best wishes!
Dmitri
-
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.
sonofsita
http://www.ordba.net
-
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.
Best wishes!
Dmitri
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|