1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Procedure Execution Error

Discussion in 'SQL PL/SQL' started by sheetal, Jun 3, 2014.

  1. sheetal

    sheetal Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I'm facing the error while executing a procedure. Kinldy help.

    Throwing an error, saying invalid cursor Error Code:ORA 06512

    Below is the code

    DECLARE
    CURSOR C1 IS
    SELECT HDR.REQUISITION_HEADER_ID FROM STG_XXPF_REQ_HEADERS HDR,STG_XXPF_PR_HISTORY PRHIST
    WHERE HDR.REQUISITION_HEADER_ID=PRHIST.REQUISITION_HEADER_ID AND ACTION_TRACKED='ENTERED'
    ORDER BY HDR.REQUISITION_HEADER_ID;
    MY_REQUISITION_HEADER_ID NUMBER;
    MY_PR_BLOCKED_DAYS NUMBER;

    BEGIN
    IF C1%ISOPEN THEN
    CLOSE C1;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Cursor opened by for');
    FOR HDR_ID IN C1 LOOP
    FETCH C1 INTO MY_REQUISITION_HEADER_ID;
    EXIT WHEN C1%NOTFOUND;

    SELECT (SELECT COUNT(THALES_NON_WRKG_DAY_FLG) AS NO_DAYS FROM STG_DAY
    WHERE THALES_NON_WRKG_DAY_FLG='N' AND
    CALENDAR_DATE BETWEEN (SELECT PRHIST.CREATION_DATE FROM STG_XXPF_PR_HISTORY PRHIST
    WHERE PRHIST.REQUISITION_HEADER_ID=MY_REQUISITION_HEADER_ID AND PRHIST.ACTION_TRACKED ='BLOCKED')
    AND
    (SELECT PRHIST.CREATION_DATE FROM STG_XXPF_PR_HISTORY PRHIST
    WHERE PRHIST.REQUISITION_HEADER_ID=MY_REQUISITION_HEADER_ID AND PRHIST.ACTION_TRACKED ='NOT BLOCKED' )
    ) AS PR_BLOCKED_DAYS INTO MY_PR_BLOCKED_DAYS

    FROM STG_XXPF_PR_HISTORY PRHIST
    WHERE PRHIST.REQUISITION_HEADER_ID=MY_REQUISITION_HEADER_ID;

    END LOOP;
    CLOSE C1;

    END;
     
  2. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi,

    There is a space in the following variables. Please remove the space and try again.

    REQUISITION_HEADE R_ID -----> REQUISITION_HEADER_ID
    MY_REQUISITION_HEADER _ID -----> MY_REQUISITION_HEADER_ID

    Note: Replace at all occurrences.

    Thanks.
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Lot of mistakes are there...

    First understand what is for loop.

    Also your select statement doesn't have INTO statement
     
  5. sheetal

    sheetal Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    Tried the below query by remving Fetch.but still the same

    DECLARE
    CURSOR C1 IS
    SELECT HDR.requisition_header_id FROM STG_XXPF_REQ_HEADERS HDR,STG_XXPF_PR_HISTORY PRHIST
    WHERE HDR.requisition_header_id=PRHIST.requisition_header_id AND ACTION_TRACKED='ENTERED'
    ORDER BY HDR.REQUISITION_header_id;
    MY_REQUISITION_HEADER_ID NUMBER;
    MY_PR_BLOCKED_DAYS NUMBER;

    BEGIN
    IF C1%ISOPEN THEN
    CLOSE C1;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Cursor opened by for');
    FOR HDR_ID IN C1 LOOP

    SELECT (SELECT COUNT(THALES_NON_WRKG_DAY_FLG) AS NO_DAYS FROM STG_DAY
    WHERE THALES_NON_WRKG_DAY_FLG='N' AND
    CALENDAR_DATE BETWEEN (SELECT PRHIST.CREATION_DATE FROM STG_XXPF_PR_HISTORY PRHIST
    WHERE PRHIST.REQUISITION_HEADER_ID=MY_REQUISITION_HEADER_ID AND PRHIST.ACTION_TRACKED ='BLOCKED')
    AND
    (SELECT PRHIST.CREATION_DATE FROM STG_XXPF_PR_HISTORY PRHIST
    WHERE PRHIST.REQUISITION_HEADER_ID=MY_REQUISITION_HEADER_ID AND PRHIST.ACTION_TRACKED ='NOT BLOCKED' )
    ) AS PR_BLOCKED_DAYS INTO MY_PR_BLOCKED_DAYS

    FROM STG_XXPF_PR_HISTORY PRHIST
    WHERE PRHIST.REQUISITION_HEADER_ID=MY_REQUISITION_HEADER_ID;

    END LOOP;
    CLOSE C1;

    END;
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    y u need IF C1%ISOPEN THEN
    CLOSE C1;
    END IF;

    Also remove CLOSE C1;
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    1) You don't need the :

    and

    2) How is "MY_REQUISITION_HEADER_ID" populated ? It is always null.

    3) You don't use the values returned by the cursor (no reference to HDR_ID anywhere in the code)
     
  8. sheetal

    sheetal Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    If I dont put

    IF C1%ISOPEN THEN
    CLOSE C1;
    END IF;

    It throws error saying cursor already open. So I've put this statements.
    And if remove all open and close cursors , it says invalid cursor.

    DECLARE
    CURSOR C1 IS
    SELECT HDR.REQUISITION_HEADER_ID FROM STG_XXPF_REQ_HEADERS HDR,STG_XXPF_PR_HISTORY PRHIST
    WHERE HDR.REQUISITION_HEADER_ID=PRHIST.REQUISITION_HEADER_ID AND ACTION_TRACKED='ENTERED'
    ORDER BY HDR.REQUISITION_HEADER_ID;
    MY_REQUISITION_HEADER_ID NUMBER;
    MY_PR_BLOCKED_DAYS NUMBER;

    BEGIN

    FOR HDR_ID IN C1 LOOP
    FETCH C1 INTO MY_REQUISITION_HEADER_ID;
    EXIT WHEN C1%NOTFOUND;

    SELECT (SELECT COUNT(THALES_NON_WRKG_DAY_FLG) AS NO_DAYS FROM STG_DAY
    WHERE THALES_NON_WRKG_DAY_FLG='N' AND
    CALENDAR_DATE BETWEEN (SELECT PRHIST.CREATION_DATE FROM STG_XXPF_PR_HISTORY PRHIST
    WHERE PRHIST.REQUISITION_HEADER_ID=MY_REQUISITION_HEADER_ID AND PRHIST.ACTION_TRACKED ='BLOCKED')
    AND
    (SELECT PRHIST.CREATION_DATE FROM STG_XXPF_PR_HISTORY PRHIST
    WHERE PRHIST.REQUISITION_HEADER_ID=MY_REQUISITION_HEADER_ID AND PRHIST.ACTION_TRACKED ='NOT BLOCKED' )
    ) AS PR_BLOCKED_DAYS INTO MY_PR_BLOCKED_DAYS

    FROM STG_XXPF_PR_HISTORY PRHIST
    WHERE PRHIST.REQUISITION_HEADER_ID=MY_REQUISITION_HEADER_ID;

    END LOOP;



    END;
     
  9. sheetal

    sheetal Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Rajen,


    Cursor C1 is defined as below.

    CURSOR C1 IS
    SELECT HDR.REQUISITION_HEADER_ID FROM STG_XXPF_REQ_HEADERS HDR,STG_XXPF_PR_HISTORY PRHIST
    WHERE HDR.REQUISITION_HEADER_ID=PRHIST.REQUISITION_HEADER_ID AND ACTION_TRACKED='ENTERED'
    ORDER BY HDR.REQUISITION_HEADER_ID;
    And
    Cursor C1 is been fetched into My Requisition Header Id using the below statement
    FETCH C1 INTO MY_REQUISITION_HEADER_ID;
     
  10. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Again You used

    FETCH C1 INTO MY_REQUISITION_HEADER_ID;
    EXIT WHEN C1%NOTFOUND;



    no need fetch , the value is in hdr_id.REQUISITION_HEADER_ID
     
  11. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Sheetal,

    1) As mentioned previously - no need for:

    and

    AND

    2) For MY_REQUISITION_HEADER_ID use the following instead (replace WHERE PRHIST.REQUISITION_HEADER_ID=MY_REQUISITION_HEADER _ID by
    PRHIST.REQUISITION_HEADER_ID = HRD_ID.REQUISITION_HEADER_ID), i.e.

    Code (SQL):
    FOR HDR_ID IN C1
    LOOP
      SELECT
        ....
          WHERE PRHIST.REQUISITION_HEADER_ID = HRD.REQUISITION_HEADER_ID
        ....
    END LOOP;
     
  12. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Sheetal,

    It appears you have a basic misunderstanding of Cursor For-Loops. Your code also suffers from lack of whitespace which makes it really difficult to read...

    Code (SQL):
    DECLARE
         cursor c1_csr IS
              SELECT hdr.requisition_header_id
              FROM  stg_xxpf_req_headers hdr
                   ,stg_xxpf_pr_history prhist
              WHERE hdr.requisition_header_id = prhist.requisition_header_id
              AND     action_tracked          = 'ENTERED'
              ORDER BY hdr.requisition_header_id;

         my_requisition_header_id   NUMBER;
         my_pr_blocked_days         NUMBER;

    BEGIN
         FOR hdr_id_rec IN c1_csr loop

              SELECT (SELECT COUNT(thales_non_wrkg_day_flg) AS no_days
                         FROM stg_day
                         WHERE thales_non_wrkg_day_flg = 'N'
                         AND calendar_date BETWEEN
                              (SELECT prhist.creation_date
                               FROM stg_xxpf_pr_history prhist
                               WHERE prhist.requisition_header_id = my_requisition_header_id
                               AND   prhist.action_tracked        = 'BLOCKED')
                         --
                              AND
                         --
                               (SELECT prhist.creation_date
                                FROM stg_xxpf_pr_history prhist
                                WHERE prhist.requisition_header_id = my_requisition_header_id
                                AND   prhist.action_tracked        = 'NOT BLOCKED' )) AS pr_blocked_days
                          --
              INTO my_pr_blocked_days
              FROM stg_xxpf_pr_history prhist
              WHERE prhist.requisition_header_id = my_requisition_header_id;

         END loop;
    END;
    You'll note I took the liberty of cleaning this up and removing the Open/Fetch/Close. You don't need the latter as a Cursor For-Loop construct takes care of that for you (as noted before). There is a small alteration to your CURSOR RECORD by adding _rec to the inline declaration in the loop statement. I also fixed the extra spaces you had littered about which would crash the block's code anyway (that was also noted before). I took the liberty altering the use of upper/lower case to make it easier to read. All this, just so I could read it and figure out what you're trying to do. Hopefully I didn't introduce a type! :cool:

    And look what I found. Two declared variables that you don't need.

    my_requisition_header_id NUMBER;
    my_pr_blocked_days NUMBER;​

    The following assignments can be altered from :
    = my_requisition_header_id​

    to:
    = hde_id_rec.requisition_header_id​

    wherever that line occurs in order to use the CURSOR record as intended.

    The second variable gets a value...but you do nothing w/ it...ergo, it's not needed...unless this is a work in progress and you've not provided everything?

    Now, as for the error where you report an 'invalid cursor'... have you tried running the following select statement by itself?

    Code (SQL):
    SELECT hdr.requisition_header_id
    FROM  stg_xxpf_req_headers hdr
            ,stg_xxpf_pr_history prhist
    WHERE hdr.requisition_header_id = prhist.requisition_header_id
    AND   action_tracked            = 'ENTERED'
    ORDER BY hdr.requisition_header_id;
    If that statement fails, then the cursor will never work. I also question the use of the AS qualifier in the SELECT statements since they don't appear to serve a purpose for what you're doing.

    HTH

    CJ
     
    venu57 likes this.