subprogram or cursor reference is out of scope

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

what is your code

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.