none
Can a Procedure have more than one cursor? RRS feed

  • Question

  • Hello:

     

    When I declare two cursors, I get the following error:

     

    320

    A cursor with the name 'crsrDrug' already exists.

    16

    16915

     

    wven though the two cursors have different names, crsrDemo and crsrDrug.

     

    Is there a restiction that a procedure can have only one cursor declared at a time?

     

     

    Wednesday, June 18, 2008 1:36 PM

All replies

  • You can have more than one cursor open at a time.  Check your declarations and make sure you did not copy and paste something.  Can you post the code?

     

     

    Wednesday, June 18, 2008 1:42 PM
  • Ken:

     

    Here's the code snippet:

     

    DECLARE

    crsrDemo CURSOR

    STATIC READ_ONLY

    FOR

    SELECT

    Record_No_N, Year_C, Quarter_C, ISR_No_C, Case_No_C, I_F_CD_C, FU_Seq_No_C,

    Image_No_C, Event_Date_C, Manufacturer_Date_C,

    Received_by_FDA_Date_C, Report_Type_CD_C, Manufacturer_No_C,

    Manufacturer_Name_C, Patient_Age_at_Event_Time_C, Age_Unit_CD_C,

    Gender_CD_C, Electr_Submitted_YN_C, Patient_Wt_C, Weight_Unit_CD_C,

    Report_Date_C, Occupation_CD_C, Patient_Death_Date_C,

    Sent_To_Manufacturer_YN_C, Confidential_YN_C

    FROM

    stgDEMOGRAPHIC

    DECLARE

    crsrDrug CURSOR

    STATIC READ_ONLY

    FOR

    SELECT

    Record_No_N, Year_C, Quarter_C, ISR_No_C, Drug_Seq_No_C, Drug_Role_CD_C, Drug_Name_C,

    Drug_Name_Type_CD_C, Route_Of_Admin_C, Dosage_Verbatim_C,

    Dechallenge_CD_C, Rechallenge_CD_C, Lot_No_C, Expiry_DT_C, NDA_No_Verbatim_C

    FROM

    stgDRUG

     

    Wednesday, June 18, 2008 1:46 PM
  • It sounds like you might have forgotten to DEALLOCATE a cursor after using it.  Cursor handles can be either GLOBAL or LOCAL, depending on the database setting "default to local cursor".  See DECLARE CURSOR in Books Onlin for more information.

     

    If your cursors are GLOBAL, and you forget to deallocate the cursor, then it will exist until you disconnect.  You will need to run DEALLOCATE crsrDemo first.  You need to make sure that your error handling closes and deallocates the cursors if there is an error.  Other options would be to check to see if the cursors exists before declaring it (but this could break other parts of your app if you're expecting a long-life cursor), or to set the cursor to be local (either by changing the database setting, or by declaring your cursors with the LOCAL keyword: DECLARE crsrDemo LOCAL).

    Wednesday, June 18, 2008 2:05 PM
    Answerer
  •  

    Make sure you execute this before you try to reopen the cursor.

     

     

    CLOSE crsrDemo

    Deallocate crsrDemo

    CLOSE crsrDrug

    Deallocate crsrDrug

    Wednesday, June 18, 2008 2:10 PM
  •  

    The structure of your query looks good, the error is because you did not close and deallocate the cursor.

    If you are calling a opening same cursor you will get the error message.

     

    I hope this help

    Wednesday, June 18, 2008 2:19 PM
  • Not true. I have the DECLARE. I then open crsrDemo, process the rows, close it and deallocate it. I have:

     

    OPEN crsrDemo

    -- Process the rows

    CLOSE crsrDemo

    DEALLOCATE crsrDemo

     

    OPEN crsrDrug

    -- Process the rows

    CLOSE crsrDrug

    DEALLOCATE crsrDrug

     

    When I create the SP with the codes that declares, opens, closes and deallocates crsrDRUG, it runs.

     

    But when I uncomment those lines, I get this error.

     

    I did a little bit of digging around and it surfaced something fishy:

     

    Cursor 1 Name    Cursor 2 Name    Works?

    crsrDEMO           crsrDRUG           No

    crsrDEMO           crsrDaUG           Yes

    crsrDEMO           crsrERUG           Yes

    crsrDEMO           crsrDeUG           Yes

    crsrDEMO           crsrdrug              No

     

    Can somebody lend credance to this observation?

     

    Venki

     

     

    Wednesday, June 18, 2008 3:20 PM
  • Cursor by default is global to a connection, not limited to a particular SP. I suspect you have this crsrDrug cursor open in that connection already. Try to run the close and deallocate statement alone and see what happens:

     

    Or you can always try to open a new connection.

    Wednesday, June 18, 2008 8:45 PM