5 Replies Latest reply: Aug 27, 2013 4:38 AM by 996599 RSS

    maximum open cursors exceeded

    996599

      --when i have run the package,this error will come.

      ORA-01000: maximum open cursors exceeded

                    Please help me.

        • 1. Re: maximum open cursors exceeded
          Soofi

          You can change the maximum number of cursor by

          ALTER SYSTEM SET OPEN_CURSORS=1000;

           

          But before doing this ensure that you have closed the cursors that is already been opened by you.

           

          Otherwise the cursors will flow from beginning till to the end of your Procedure.

           

          Hope this helps,

          • 2. Re: maximum open cursors exceeded
            Karthick_Arp

            OPEN_CURSORS defines the maximum number of open cursor that can be there for a session. Basically your package is trying to open cursors more than the number specified in OPEN_CURSORS. This generally happen due to bad coding, where people open cursor and forget to close them. Or another reason could be that the value for OPEN_CURSORS is set to a very low number. So please check these cases and fix the issue.

            • 3. Re: maximum open cursors exceeded
              996599

              But in this code they are using implicit cursor(only select statements)then how to close.

              Please see the below code.

               

               

              CREATE OR REPLACE

              PACKAGE BODY          Pkg_Mdas_Meter_Reading IS

              ------------------------------------------------------------------------------------------------------------

              PROCEDURE Pr_Set_Mdas_Meter_Reading(

                  i_substation_code VARCHAR2,

                  i_feeder_code VARCHAR2,

                  i_dtc_code VARCHAR2,

                  i_meter_ref VARCHAR2,

                  i_meter_reading_dt DATE,

                  i_energy_kwh NUMBER

              ) IS

               

              v_meter_ref_c VARCHAR2(50);

              v_meter_type_cd VARCHAR2(20);

              v_substation_id NUMBER;

              v_feeder_id NUMBER;

              v_dtc_id NUMBER;

              v_prev_meter_reading_dt DATE;

              v_reading_count NUMBER;

              v_created_updated_by VARCHAR2(10) := 'MDAS';

              v_prev_energy_kwh NUMBER;

              v_meter_consumption NUMBER;

              v_ea_month NUMBER;

              v_ea_year NUMBER;

              v_meter_status NUMBER;

              v_count NUMBER;

              --e_invalid_meter_ref EXCEPTION;

              --KKS 30/05//2013

              v_from_bu_c varchar2(4);

              v_to_bu_c varchar2(4);

              v_from_town_id_n number;

              v_to_town_id_n number;

              v_from_office_id_n number;

              v_to_office_id_n number;

              v_from_office_cd_c varchar2(10);

              v_to_office_cd_c varchar2(10);

              --KKS 30/05//2013

               

              BEGIN

                   dbms_output.put_line('-------- Input received by Pr_Set_Mdas_Meter_Reading -----------------------');

                   dbms_output.put_line('Substation Code: ' || i_substation_code);

                   dbms_output.put_line('Feeder Code: ' || i_feeder_code);

                   dbms_output.put_line('DTC Code: ' || i_dtc_code);

                   dbms_output.put_line('Meter Ref: ' || i_meter_ref);

                   dbms_output.put_line('Meter Reading Date: ' || TO_CHAR(i_meter_reading_dt,'dd-Mon-yyyy hh24:mi:ss'));

                   dbms_output.put_line('Energy KWH: ' || i_energy_kwh);

                   dbms_output.put_line('----------------------------------------------------------------------------');

               

                  -- Find Meter and Asset Info

                  BEGIN

                      --SELECT METER_REF_C, METER_TYPE_CD_C, FEEDER_ID_N, TRANSFORMER_ID_N INTO v_meter_ref_c, v_meter_type_cd, v_feeder_id, v_dtc_id

                      --FROM NDM_METER_ON_ASSET, NDM_METER_TYPE

                      --WHERE METER_REF_C = i_meter_ref

                      --AND NDM_METER_ON_ASSET.METER_TYPE_ID_N = NDM_METER_TYPE.METER_TYPE_ID_N;

                      SELECT METER_TYPE_CD_C INTO v_meter_type_cd

                      FROM NDM_METER_ON_ASSET, NDM_METER_TYPE

                      WHERE METER_REF_C = i_meter_ref

                      AND NDM_METER_ON_ASSET.METER_TYPE_ID_N = NDM_METER_TYPE.METER_TYPE_ID_N;

                  EXCEPTION

                         WHEN OTHERS THEN

                      RAISE_APPLICATION_ERROR (-20000, 'Meter ' || i_meter_ref || ' does not exist in NDM');

                     END;

               

                   dbms_output.put_line('-------- Output received from NDM_METER_ON_ASSET -----------------------');

                   dbms_output.put_line('Meter Ref: ' || v_meter_ref_c);

                   dbms_output.put_line('Feeder Id: ' || v_feeder_id);

                   dbms_output.put_line('DTC Id: ' || v_dtc_id);

                   dbms_output.put_line('Meter Type: ' || v_meter_type_cd);

                   dbms_output.put_line('----------------------------------------------------------------------------');

               

                  -- Get Substation Id from Substation Code

                  IF (i_substation_code IS NOT NULL AND i_feeder_code IS NOT NULL) THEN

                     BEGIN

                         SELECT SUBSTATION_ID_N INTO v_substation_id

                         FROM NDM_SUBSTATION

                         WHERE SUBSTATION_CD_C = i_substation_code;

                         dbms_output.put_line('Substation Id: ' || v_substation_id);

                     EXCEPTION

                             WHEN OTHERS THEN

                          RAISE_APPLICATION_ERROR (-20001, 'Substation Code ' || i_substation_code || ' does not exist in NDM');

                        END;

               

                      -- Get Feeder Id from Feeder Code

                      BEGIN

                         SELECT FEEDER_ID_N INTO v_feeder_id

                         FROM NDM_FEEDER

                         WHERE SRC_SUBSTATION_ID_N = v_substation_id

                         AND FEEDER_CD_C = i_feeder_code;

                         dbms_output.put_line('Feeder Id: ' || v_feeder_id);

                      EXCEPTION

                             WHEN OTHERS THEN

                          RAISE_APPLICATION_ERROR (-20002, 'Feeder Code ' || i_feeder_code || ' and Substation Code ' || i_substation_code || ' does not exist in NDM');

                         END;

                  END IF;

               

                  -- Get DTC Id from DTC Code

                  IF (i_dtc_code IS NOT NULL) THEN

                     BEGIN

                         SELECT TRANSFORMER_ID_N INTO v_dtc_id

                         FROM NDM_TRANSFORMER

                         WHERE TRANSFORMER_CD_C = i_dtc_code;

                         dbms_output.put_line('DTC Id: ' || v_dtc_id);

                     EXCEPTION

                             WHEN OTHERS THEN

                          RAISE_APPLICATION_ERROR (-20003, 'DTC Code ' || i_dtc_code || ' does not exist in NDM');

                        END;

                  END IF;

               

                   dbms_output.put_line('-------- Getting Asset Info based on input -----------------------');

                   dbms_output.put_line('Substation Id: ' || v_substation_id);

                   dbms_output.put_line('Feeder Id: ' || v_feeder_id);

                   dbms_output.put_line('DTC Id: ' || v_dtc_id);

                   dbms_output.put_line('----------------------------------------------------------------------------');

               

                  IF(v_feeder_id IS NULL AND v_dtc_id IS NULL) THEN

                      RAISE_APPLICATION_ERROR (-20004, 'No DTC / Feeder found for input DTC Code ' || i_dtc_code || ' / input Feeder Code ' || i_feeder_code || ' in NDM');

                  END IF;

               

                  -- Validate Asset info

                  v_count := Fn_Validate_Meter_On_Asset(i_meter_ref, v_feeder_id, v_dtc_id);

                  dbms_output.put_line('Validate Asset Info ' || v_count);

               

               

                  IF(v_meter_type_cd IS NOT NULL AND (v_meter_type_cd = '002' OR v_meter_type_cd = '003')) THEN

                       dbms_output.put_line('Meter REF ' || i_meter_ref || ' IS Boundary Meter');

               

                      -- Find out if Meter Readings exist for Meter Reading Date supplied and Meter Reading Dates after the supplied date

                      SELECT COUNT(1) INTO v_reading_count

                      FROM NDM_BOUNDARY_METER_READING

                      WHERE METER_REF_C = i_meter_ref

                      AND METER_READING_DT >= i_meter_reading_dt;

               

                      dbms_output.put_line('Reading COUNT: ' || v_reading_count);

               

                      IF (v_reading_count <= 0) THEN

                         -- Find out latest Meter Reading Date for the Meter. This will be set as Previous Meter Reading Date in new record

                         BEGIN

                              SELECT METER_READING_DT, ENERGY_N INTO v_prev_meter_reading_dt, v_prev_energy_kwh

                              FROM NDM_BOUNDARY_METER_READING

                              WHERE METER_REF_C = i_meter_ref

                              AND ROWNUM < 2

                              ORDER BY METER_READING_DT DESC;

                         EXCEPTION

                                 WHEN NO_DATA_FOUND THEN

                              v_prev_meter_reading_dt := NULL;

                              v_prev_energy_kwh := 0;

                         END;

               

                         dbms_output.put_line('Previous Energy: ' || v_prev_energy_kwh);

               

                         IF(v_prev_meter_reading_dt IS NOT NULL) THEN

                                 dbms_output.put_line('Previous Meter Reading DATE: ' || TO_CHAR(v_prev_meter_reading_dt,'dd-Mon-yyyy hh24:mi:ss'));

                         ELSE

                                dbms_output.put_line('Previous Meter Reading DATE IS NULL');

                         END IF;

               

                         -- Calculate EA Month and Year

                         v_ea_month := TO_NUMBER(TO_CHAR(i_meter_reading_dt, 'mm'));

                         v_ea_year := TO_NUMBER(TO_CHAR(i_meter_reading_dt, 'yyyy'));

                         dbms_output.put_line('EA Month AND Year: ' || v_ea_month || '-' || v_ea_year);

               

                         -- Calculate Meter Status (Normal = 1/ Overflow = 3)

                         IF(v_prev_energy_kwh > i_energy_kwh) THEN

                                 v_meter_status := 3;

                         ELSE

                                 v_meter_status := 1;

                         END IF;

                         dbms_output.put_line('Meter Status: ' || v_meter_status);

               

                         -- Calculate Consumption

                         v_meter_consumption := Fn_Calculate_Meter_Consumption(i_meter_ref, v_meter_status, i_energy_kwh, v_prev_energy_kwh);

                         dbms_output.put_line('Consumption: ' || v_meter_consumption);

               

                         -- Create Boundary Meter Reading record

                         -- How to compute DISCOM_OWNED_YN_C, FROM_OFFICE_TYPE_ID_N, FROM_OFFICE_CD_REF_C,

                         -- TO_OFFICE_TYPE_ID_N, TO_OFFICE_CD_REF_C, FROM_TOWN_ID_N, TO_TOWN_ID_N?

                         --KKS 30/05/2013

                         Select from_bu_c, to_bu_c, from_town_id_n, to_town_id_n

                         into v_from_bu_c, v_to_bu_c, v_from_town_id_n, v_to_town_id_n

                         from ndm_meter_on_asset

                         where meter_ref_c = i_meter_ref;

                        

                         begin

                              SELECT location_cd_c into v_from_office_cd_c from MBC_OFFICE_LOCATION where LOCATION_ID_N IN

                              (select PARENT_LOCATION_ID_N from MBC_OFFICE_LOCATION where LOCATION_ID_N In

                              (select LOCATION_ID_N from MBC_BU_OFFICE_LOCATION where BU_CODE = v_from_bu_c));

                         exception

                              when others then

                                   v_from_office_cd_c := null;

                         end;

                        

                         begin

                              SELECT location_cd_c into v_to_office_cd_c from MBC_OFFICE_LOCATION where LOCATION_ID_N IN

                              (select PARENT_LOCATION_ID_N from MBC_OFFICE_LOCATION where LOCATION_ID_N In

                              (select LOCATION_ID_N from MBC_BU_OFFICE_LOCATION where BU_CODE = v_to_bu_c));

                         exception

                              when others then

                                  v_to_office_cd_c := null;

                         end;

                        

                         v_from_office_id_n := 3;

                         v_to_office_id_n := 3;

               

               

                         INSERT INTO NDM_BOUNDARY_METER_READING (

                         METER_REF_C, METER_READING_DT, FEEDER_ID_N, DISCOM_OWNED_YN_C,

                         MSEDCL_SUBSTATION_ID_N, EA_MONTH_N, EA_YEAR_N, FROM_OFFICE_TYPE_ID_N,

                         FROM_OFFICE_CD_REF_C, TO_OFFICE_TYPE_ID_N, TO_OFFICE_CD_REF_C, ENERGY_N,

                         CONSUMPTION_N, PREV_METER_READING_DT, FROM_TOWN_ID_N, TO_TOWN_ID_N,

                         STATUS_CD_C, CREATED_BY_C, CREATED_DT, UPDATED_BY_C, UPDATED_DT,

                         METER_STATUS_ID_N

                         ) VALUES (

                         i_meter_ref, i_meter_reading_dt, v_feeder_id, NULL,

                         v_substation_id, v_ea_month, v_ea_year, v_from_office_id_n,

                         v_from_office_cd_c, v_to_office_id_n, v_to_office_cd_c, i_energy_kwh,

                         v_meter_consumption, v_prev_meter_reading_dt, v_from_town_id_n, v_to_town_id_n,

                         'A', v_created_updated_by, SYSDATE, v_created_updated_by, SYSDATE,

                         v_meter_status);

                         --KKS 30/05/2013

                         dbms_output.put_line('NEW reading inserted FOR Meter REF ' || i_meter_ref || ' dated ' || TO_CHAR(i_meter_reading_dt,'dd-Mon-yyyy hh24:mi:ss'));

                      ELSE

                          dbms_output.put_line('Reading EXISTS FOR Meter REF ' || i_meter_ref || ' dated ' || TO_CHAR(i_meter_reading_dt,'dd-Mon-yyyy hh24:mi:ss'));

                      END IF;

                  END IF;

               

                  IF(v_meter_type_cd IS NOT NULL AND (v_meter_type_cd = '001' OR v_meter_type_cd = '003')) THEN

                      dbms_output.put_line('Meter REF ' || i_meter_ref || ' IS Main Meter');

               

                      -- Find out if Meter Readings exist for Meter Reading Date supplied and Meter Reading Dates after the supplied date

                      SELECT COUNT(*) INTO v_reading_count

                      FROM NDM_METER_READING_BY_RECEIVER

                      WHERE METER_REF_C = i_meter_ref

                      AND METER_READING_DT >= i_meter_reading_dt;

               

                      dbms_output.put_line('Reading COUNT: ' || v_reading_count);

               

                      IF (v_reading_count <= 0) THEN

                         -- Find out latest Meter Reading Date for the Meter. This will be set as Previous Meter Reading Date in new record

                         BEGIN

                             SELECT METER_READING_DT, ENERGY_N INTO v_prev_meter_reading_dt, v_prev_energy_kwh

                             FROM NDM_METER_READING_BY_RECEIVER

                             WHERE METER_REF_C = i_meter_ref

                             AND ROWNUM < 2

                             ORDER BY METER_READING_DT DESC;

                         EXCEPTION

                                 WHEN NO_DATA_FOUND THEN

                              v_prev_meter_reading_dt := NULL;

                              v_prev_energy_kwh := 0;

                         END;

               

                         IF(v_prev_meter_reading_dt IS NOT NULL) THEN

                                 dbms_output.put_line('Previous Meter Reading DATE: ' || TO_CHAR(v_prev_meter_reading_dt,'dd-Mon-yyyy hh24:mi:ss'));

                         ELSE

                                dbms_output.put_line('Previous Meter Reading DATE IS NULL');

                         END IF;

               

                         -- Calculate Meter Status (Normal = 1/ Overflow = 3)

                         IF(v_prev_energy_kwh > i_energy_kwh) THEN

                                 v_meter_status := 3;

                         ELSE

                                 v_meter_status := 1;

                         END IF;

                         dbms_output.put_line('Meter Status: ' || v_meter_status);

               

                         -- Calculate Consumption

                         v_meter_consumption := Fn_Calculate_Meter_Consumption(i_meter_ref, v_meter_status, i_energy_kwh, v_prev_energy_kwh);

                         dbms_output.put_line('Consumption FOR Meter REF ' || i_meter_ref || ': ' || v_meter_consumption);

               

                         -- Create Meter Reading record

                         -- How to compute OFFICE_TYPE_ID_N, OFFICE_CODE_CD_C, AVG_LOAD_FACTOR_N, TOWN_ID_N?

                         --KKS 29/05/2013

                         if (v_dtc_id is not null) then

                              v_feeder_id := null;

                         end if;

                         --KKS 29/05/2013

                         INSERT INTO NDM_METER_READING_BY_RECEIVER (

                         METER_REF_C, METER_READING_DT, OFFICE_TYPE_ID_N, OFFICE_CODE_CD_C,

                         AVG_LOAD_FACTOR_N, ENERGY_N, CONSUMPTION_N, PREV_METER_READING_DT,

                         METER_STATUS_ID_N, READ_BY_REF_C, REMARKS_C, TOWN_ID_N,

                         STATUS_CD_C, CREATED_BY_C, CREATED_DT, UPDATED_BY_C, UPDATED_DT,

                         TRANSFORMER_ID_N, FEEDER_ID_N, BUSBAR_ID_N

                         ) VALUES (

                         i_meter_ref, i_meter_reading_dt, NULL , NULL,

                         NULL, i_energy_kwh, v_meter_consumption, v_prev_meter_reading_dt,

                         v_meter_status, v_created_updated_by, 'Reading from MDAS', NULL,

                         'A', v_created_updated_by, SYSDATE, v_created_updated_by, SYSDATE,

                         v_dtc_id, v_feeder_id, NULL);

               

                         dbms_output.put_line('NEW reading inserted FOR Meter REF ' || i_meter_ref || ' dated ' || TO_CHAR(i_meter_reading_dt,'dd-Mon-yyyy hh24:mi:ss'));

                       ELSE

                           dbms_output.put_line('Reading EXISTS FOR Meter REF ' || i_meter_ref || ' dated ' || TO_CHAR(i_meter_reading_dt,'dd-Mon-yyyy hh24:mi:ss'));

                      END IF;

               

                  END IF;

                  dbms_output.put_line('-------- End Pr_Set_Mdas_Meter_Reading -------------------------------------');

              END Pr_Set_Mdas_Meter_Reading;

               

              ------------------------------------------------------------------------------------------------------------

               

              FUNCTION Fn_Calculate_Meter_Consumption(i_meter_ref VARCHAR2, i_meter_status NUMBER, i_energy_kwh NUMBER, i_prev_energy_kwh NUMBER) RETURN NUMBER IS

              v_meter_consumption NUMBER;

              v_meter_mf NUMBER;

              v_meter_digits NUMBER;

              v_max_energy_kwh NUMBER;

              BEGIN

                   v_meter_consumption := 0;

                   dbms_output.put_line('-------- Input received by Fn_Calculate_Meter_Consumption -------------------');

                   dbms_output.put_line('Meter Ref: ' || i_meter_ref);

                   dbms_output.put_line('Meter Status: ' || i_meter_status);

                   dbms_output.put_line('Current Energy KWH: ' || i_energy_kwh);

                   dbms_output.put_line('Previous Energy KWH: ' || i_prev_energy_kwh);

                   dbms_output.put_line('----------------------------------------------------------------------------');

               

                   BEGIN

                      --commented on 06-dec

                      --SELECT MF_N INTO v_meter_mf

                      -- FROM NDM_METER_CTPT

                      -- WHERE METER_REF_C = i_meter_ref;

               

                       --added on 06-dec

                       SELECT MF_N INTO v_meter_mf

                       FROM NDM_METER_CTPT

                       WHERE METER_REF_C = i_meter_ref

                               AND STATUS_CD_C = 'A';

                   EXCEPTION

                        WHEN OTHERS THEN

                            v_meter_mf := 0;

                   END;

               

                       dbms_output.put_line('MF : ' || v_meter_mf);

               

                   IF (i_meter_status = 1) THEN

                   -- If Meter Status is Normal, use the formula below

                       v_meter_consumption := (i_energy_kwh - i_prev_energy_kwh) * v_meter_mf;

                   ELSE

                   -- If Meter Status Is Overflow, get No. of Digits, get the max number using those digits and calculate

                   -- If No. of Digits is 5, max number would be 100000

                        BEGIN

                             SELECT DIGITS_N INTO v_meter_digits

                            FROM NDM_METER_ON_ASSET

                            WHERE METER_REF_C = i_meter_ref;

                       EXCEPTION

                            WHEN OTHERS THEN

                                v_meter_digits := LENGTH(TO_CHAR(ROUND(i_prev_energy_kwh)));

                       END;

               

                       dbms_output.put_line('Meter Digits: ' || v_meter_digits);

               

                       SELECT POWER(10, v_meter_digits) INTO v_max_energy_kwh FROM dual;

                       v_meter_consumption := ((v_max_energy_kwh - i_prev_energy_kwh) +  i_energy_kwh) * v_meter_mf;

                   END IF;

              dbms_output.put_line('-------- End Fn_Calculate_Meter_Consumption ---------------------------------');

              RETURN v_meter_consumption;

              END Fn_Calculate_Meter_Consumption;

              ------------------------------------------------------------------------------------------------------------

               

              FUNCTION Fn_Validate_Meter_On_Asset(i_meter_ref VARCHAR2, v_feeder_id NUMBER, v_dtc_id NUMBER) RETURN NUMBER IS

              v_count NUMBER;

               

              BEGIN

               

                   dbms_output.put_line('-------- Input received by Fn_Validate_Meter_On_Asset -------------------');

                   dbms_output.put_line('Meter Ref: ' || i_meter_ref);

                   dbms_output.put_line('Feeder Id: ' || v_feeder_id);

                   dbms_output.put_line('DTC Id: ' || v_dtc_id);

                   dbms_output.put_line('----------------------------------------------------------------------------');

               

               

                   IF (v_dtc_id IS NOT NULL) THEN

                       select count(1) into v_count from NDM_METER_ON_ASSET where METER_REF_C = i_meter_ref and TRANSFORMER_ID_N = v_dtc_id;

                      dbms_output.put_line('iffff');

                       IF (v_count < 1 ) THEN

                         RAISE_APPLICATION_ERROR (-20010, 'DTC & Meter combination supplied does not exist in NDM');

                      END IF;

                      --kks 13-Mar-2013

                      RETURN v_count;

                      --kks 13-Mar-2013

                   END IF;

               

                   IF (v_feeder_id IS NOT NULL) THEN

                       select count(1) into v_count from NDM_METER_ON_ASSET where METER_REF_C = i_meter_ref and FEEDER_ID_N = v_feeder_id;

                      dbms_output.put_line('ELSE iffff');

                       IF (v_count < 1 ) THEN

                         RAISE_APPLICATION_ERROR (-20011, 'Feeder & Meter combination supplied does not exist in NDM');

                      END IF;

                   END IF;

               

              dbms_output.put_line('-------- End Fn_Validate_Meter_On_Asset ---------------------------------');

              RETURN v_count;

              END Fn_Validate_Meter_On_Asset;

              ------------------------------------------------------------------------------------------------------------

               

              END Pkg_Mdas_Meter_Reading;

              • 4. Re: maximum open cursors exceeded
                Karthick_Arp

                This package may not be opening any cursors, but there are other references to procedures and functions in this package which could be doing it. So you need to check them all.

                • 5. Re: maximum open cursors exceeded
                  996599

                  Thanks  for your advice....