DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: subprogram or cursor reference is out of scope

  1. #1
    Join Date
    Dec 2000
    Posts
    40
    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    what is your code

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Show me your PL/SQL block.
    Best wishes!
    Dmitri

  4. #4
    Join Date
    Dec 2000
    Posts
    40
    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




  5. #5
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    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

  6. #6
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    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
  •  


Click Here to Expand Forum to Full Width