1 Reply Latest reply: May 3, 2012 9:56 AM by David Last-Oracle RSS

    ORA-01001: invalid cursor + ORA-01403: no data found

    user355017
      Hi, I consult for a mistake in the execution of a procedure / package:

      HISR_RETEN.p_hisr_retention_all BEGIN, END;

      Error when executing the same is:

      ORA-01001: invalid cursor
      ORA-06512: at "XAJTDB.HISR_RETEN", line 155
      ORA-01403: no data found
      ORA-06512: at line 1

      Schema: xajtdb
      Package: HISR_RETEN
      Procedure: p_hisr_retention_all
      cursor: hisr_freq_cur

      I do not have much experience in PL-SQL, but the error tells of a cursor that is not data, and points to the line of the exception.

      They may help me solve this?, The most curious is that the problem is not production.

      I copy the code in the package:

      CREATE OR REPLACE PACKAGE BODY IS XAJTDB.HISR_RETEN

      / *
      | | Current package defines functions
      | |
      * /

      / *
      ************************************************** ****************************
      ************************************************** ****************************
      RETENTION **: process all tables for truncation
      ************************************************** ****************************
      ************************************************** ****************************
      * /

      PROCEDURE p_hisr_retention_all
      IS

      return_status INTEGER: = 0;
      hisr_freq_rec HISRFrequency% ROWTYPE;
      currentdate DATE;
      past_date DATE;
      sql_string VARCHAR (1000);
      Table_Count BINARY_INTEGER;

      / *
      Each row of select ** Table HISRFrequency
      * /
      CURSOR IS hisr_freq_cur
      SELECT *
      FROM HISRFrequency
      WHERE activeFlag <> 'INACTIVE';

      BEGIN
      / *
      ** Get current date / time
      * /
      currentdate: = HISR_UTILITY.f_hisr_sysdate_plus_offset;


      / *
      Debuggery ENABLE **
      * /
      HISR_DEBUG.p_hisr_debug_out ('RETENTION', 'Entering p_hisr_retention_all', 'NO');
      HISR_DEBUG.p_hisr_debug_out ('RETENTION', '-', 'NO');
      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , 'Table Names' | | TO_CHAR (currentdate, 'YYYY / MM / DD HH24: MI: SS'), 'NO');
      HISR_DEBUG.p_hisr_debug_out ('RETENTION', '------------', 'NO');

      / *
      Loop-through rows **
      * /
      OPEN hisr_freq_cur;
      LOOP
      FETCH INTO hisr_freq_cur hisr_freq_rec;
      EXIT WHEN NOTFOUND hisr_freq_cur%;

      / *
      Debug ** For Each table name
      * /
      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , Hisr_freq_rec.TableName, 'NO');
      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , '|' | | Hisr_freq_rec.DateTimeColumnName, 'NO');

      / *
      Retention ** Check for Presence on record
      * /
      past_date: = currentdate;
      IF (hisr_freq_rec.Retention IS NOT NULL)
      THEN / * {Retention present * /


            past_date: =
              HISR_UTILITY.f_hisr_add_units_2_date
                (Hisr_freq_rec.RetentionUnits
                ,-Hisr_freq_rec.RetentionCount
                , Currentdate
                );

            HISR_DEBUG.p_hisr_debug_out ('RETENTION'
              , '|' | | TO_CHAR (past_date, 'YYYY / MM / DD HH24: MI: SS'), 'NO');

              / *
            If ** Specified in the HISRFrequency table,
            Select rows ** target archive table and Them.
            **
            * /
             return_status: =
             HISR_ARCHIVE.f_hisr_archive_view (hisr_freq_rec, past_date);

            / *
            Setup ** delete oldest records of routine
            Got rows ** if output file to archive
            * /
            IF (1 = return_status)
            THEN
              HISR_RETEN.p_hisr_truncate_old_rows
                (Hisr_freq_rec
                , Past_date
                );
            END IF;

          END IF / *} Retention present * /

      / *
      Presence ** check for RetentionNumber on record
      * /
      IF (hisr_freq_rec.RetentionNumber IS NOT NULL)
      THEN / * {RetentionNumber present * /

            / *
            Select count ** format of DateTimeColumnName
            * /
            Table_Count: = HISR_RETEN.f_hisr_row_counter (hisr_freq_rec);
            HISR_DEBUG.p_hisr_debug_out ('RETENTION'
              , '| RetentionNumber =' | | to_char (hisr_freq_rec.RetentionNumber) | |
               '| Table_Count =' | | to_char (Table_Count), 'NO');

            / *
            Delete ** set-up of routine
            * /
            HISR_RETEN.p_hisr_truncate_excess
              (Hisr_freq_rec
              , Table_Count
              );

          END IF / *} RetentionNumber present * /


      END LOOP / * Each HISRFrequency Row * /

      CLOSE hisr_freq_cur;


      / *
      ** Get current date / time
      * /
      currentdate: = HISR_UTILITY.f_hisr_sysdate_plus_offset;
      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , TO_CHAR (currentdate, 'YYYY / MM / DD HH24: MI: SS'), 'NO');

      / *
      DISABLE the debugger **
      * /
      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , TO_CHAR (currentdate, 'YYYY / MM / DD HH24: MI: SS') | | ': Leaving p_hisr_reten_all', 'NO');

      COMMIT;

      EXCEPTION

      WHEN OTHERS
      THEN
      CLOSE hisr_freq_cur;


      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , ': HISR: Could not HISRFrequency ACCESS table'
      'YES');

      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , ': ERROR:' | | to_char (SQLCODE) | | ':' | | SQLERRM (SQLCODE) | | ''
      'YES');

      ext_comm_pkg.p_insert_elog
      ('HISR: Could not HISRFrequency ACCESS table, ERROR:' | | to_char (SQLCODE)
      'Yes');

      END;


      / *
      ************************************************** ****************************
      RETENTION **: process by table for truncation
      ************************************************** ****************************
      ************************************************** ****************************
      * /
      PROCEDURE p_hisr_retention_table (table_name IN VARCHAR2)
      IS

      return_status INTEGER: = 0;
      hisr_freq_rec HISRFrequency% ROWTYPE;
      currentdate DATE;
      past_date DATE;
      sql_string VARCHAR (1000);
      Table_Count BINARY_INTEGER;

      / *
      Table check ** HISRFrequency activeFlag in Table
      * /
      CURSOR IS hisr_freq_cur
      SELECT *
      FROM HISRFrequency
      WHERE activeFlag <> 'INACTIVE'
      and tablename = table_name;

      BEGIN
      / *
      ** Get current date / time
      * /
      currentdate: = HISR_UTILITY.f_hisr_sysdate_plus_offset;

      / *
      Debuggery ENABLE **
      * /
      HISR_DEBUG.p_hisr_debug_out ('RETENTION', 'Entering p_hisr_retention_table', 'NO');
      HISR_DEBUG.p_hisr_debug_out ('RETENTION', '-', 'NO');
      HISR_DEBUG.p_hisr_debug_out ('RETENTION', 'Table Name' | | table_name | | TO_CHAR (currentdate, 'YYYY / MM / DD HH24: MI: SS'), 'NO');
      HISR_DEBUG.p_hisr_debug_out ('RETENTION', '------------', 'NO');

      / *
      Loop-through rows **
      * /

      OPEN hisr_freq_cur;
      LOOP
      FETCH INTO hisr_freq_cur hisr_freq_rec;
      EXIT WHEN NOTFOUND hisr_freq_cur%;

      / *
      Debug name for table **
      * /
      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , Hisr_freq_rec.TableName, 'NO');
      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , '|' | | Hisr_freq_rec.DateTimeColumnName, 'NO');

      / *
      Retention ** Check for Presence on record
      * /
      past_date: = currentdate;
      IF (hisr_freq_rec.Retention IS NOT NULL)
      THEN / * {Retention present * /



            past_date: =
              HISR_UTILITY.f_hisr_add_units_2_date
                (Hisr_freq_rec.RetentionUnits
                ,-Hisr_freq_rec.RetentionCount
                , Currentdate
                );

            HISR_DEBUG.p_hisr_debug_out ('RETENTION'
              , '|' | | TO_CHAR (past_date, 'YYYY / MM / DD HH24: MI: SS'), 'NO');

            / *
            If ** Specified in the HISRFrequency table,
            Select rows ** target archive table and Them.
            Do not ** Attempt to archive an archive table.
            * /

             return_status: =
             HISR_ARCHIVE.f_hisr_archive_view (hisr_freq_rec, past_date);


            / *
            Setup ** delete oldest records of routine
            Got rows ** if output file to archive
            * /
            IF (1 = return_status)
            THEN
              HISR_RETEN.p_hisr_truncate_old_rows
                (Hisr_freq_rec
                , Past_date
                );
            END IF;

          END IF / *} Retention present * /

      / *
      Presence ** check for RetentionNumber on record
      * /
      IF (hisr_freq_rec.RetentionNumber IS NOT NULL)
      THEN / * {RetentionNumber present * /

            / *
            Select count ** format of DateTimeColumnName
            * /
            Table_Count: = HISR_RETEN.f_hisr_row_counter (hisr_freq_rec);
            HISR_DEBUG.p_hisr_debug_out ('RETENTION'
              , '| RetentionNumber =' | | to_char (hisr_freq_rec.RetentionNumber) | |
               '| Table_Count =' | | to_char (Table_Count), 'YES');

            / *
            Delete ** set-up of routine
            * /
            HISR_RETEN.p_hisr_truncate_excess
              (Hisr_freq_rec
              , Table_Count
              );

          END IF / *} RetentionNumber present * /


      END LOOP / * Each HISRFrequency Row * /

      CLOSE hisr_freq_cur;

      / *
      ** Get current date / time
      * /
      currentdate: = HISR_UTILITY.f_hisr_sysdate_plus_offset;
      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , TO_CHAR (currentdate, 'YYYY / MM / DD HH24: MI: SS'), 'NO');

      / *
      DISABLE the debugger **
      * /
      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , TO_CHAR (currentdate, 'YYYY / MM / DD HH24: MI: SS') | | ': Leaving p_hisr_reten_all', 'NO');

      COMMIT;

      EXCEPTION

      WHEN OTHERS
      THEN

      CLOSE hisr_freq_cur;

      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , ': HISR: Could not HISRFrequency ACCESS table'
      'YES');

      HISR_DEBUG.p_hisr_debug_out ('RETENTION'
      , ': ERROR:' | | to_char (SQLCODE) | | ':' | | SQLERRM (SQLCODE) | | ''
      'YES');

      ext_comm_pkg.p_insert_elog
      ('HISR: Could not HISRFrequency ACCESS table, ERROR:' | | to_char (SQLCODE)
      'Yes');

      END;


      / *
      ************************************************** ****************************
      ************************************************** ****************************
      SEAL **: Truncate old rows by date
      ************************************************** ****************************
      ************************************************** ****************************
      * /
      PROCEDURE p_hisr_truncate_old_rows
      (Hisr_freq_rec IN HISRFrequency% ROWTYPE
      , Past_date IN DATE
      )
      IS

      feedback BINARY_INTEGER;
      sql_string VARCHAR2 (200);
      DATE UTCTime;
      / *
      Setup routine of delete **
      * /

      delete_cur BINARY_INTEGER: = DBMS_SQL.OPEN_CURSOR;

      BEGIN
      / *
      WHERE DateTimeColumnName delete ** <past_date
      * /

      IF hisr_freq_rec.TableName like'PTC_% '
      THEN

      sql_string: =
      'DELETE FROM' | | hisr_freq_rec.TableName | |
      'WHERE: past_date>' | | hisr_freq_rec.DateTimeColumnName | |
      'AND UTCDAY <= TO_CHAR (: past_date,'' DD'') AND rownum <500000';

      else
      sql_string: =
      'DELETE FROM' | | hisr_freq_rec.TableName | |
      'WHERE: past_date>' | | hisr_freq_rec.DateTimeColumnName | |
      'AND rownum <300000';
      END IF;

      DBMS_SQL.PARSE (delete_cur, sql_string, DBMS_SQL.NATIVE);

      DBMS_SQL.BIND_VARIABLE (delete_cur 'past_date', past_date);

      LOOP
      feedback: = DBMS_SQL.EXECUTE (delete_cur);
      COMMIT;
      EXIT WHEN feedback <1, / * leave loop, last not delete rows * /
      END LOOP;

      DBMS_SQL.CLOSE_CURSOR (delete_cur);

      EXCEPTION
      WHEN OTHERS
      THEN

      ERROR_MESSAGE: =
      to_char (HISR_UTILITY.f_hisr_sysdate_plus_offset, 'YYYY / MM / DD HH24: MI: SS') | |
      'RETENTION: Could not truncate old rows, ERROR:' | | to_char (SQLCODE) | | ':' | | SQLERRM (SQLCODE);
      HISR_UTILITY.p_hisr_update_frequency
      (ERROR_MESSAGE
      , Hisr_freq_rec.FrequencyEntryNumber
      );

      IF (DBMS_SQL.IS_OPEN (delete_cur))
      THEN
      DBMS_SQL.CLOSE_CURSOR (delete_cur);
      END IF;

      END;



      / *
      ************************************************** ****************************
      ************************************************** ****************************
      SEAL **: Excess oldest truncate rows by count
      ************************************************** ****************************
      ************************************************** ****************************
      * /
      PROCEDURE p_hisr_truncate_excess
      (Hisr_freq_rec IN HISRFrequency% ROWTYPE
      , Table_Count IN BINARY_INTEGER
      )

      IS

      return_status INTEGER: = 0;
      feedback BINARY_INTEGER;
      Table_Incr BINARY_INTEGER;
      delete_cur BINARY_INTEGER: = DBMS_SQL.OPEN_CURSOR;
      sql_string VARCHAR2 (200): =
      'DELETE FROM' | | hisr_freq_rec.TableName | |
      'WHERE' | | hisr_freq_rec.DateTimeColumnName | |
      '= (SELECT MIN (' | | hisr_freq_rec.DateTimeColumnName | | ')' | |
      'FROM' | | hisr_freq_rec.TableName | | ')';

      BEGIN

      HISR_DEBUG.p_hisr_debug_out ('RETENTION', 'Entering p_hisr_truncate_excess', 'NO');

      / *
      DateTimeColumnName delete routine **
      * /
      DBMS_SQL.PARSE (delete_cur, sql_string, DBMS_SQL.NATIVE);

      Table_Incr: = hisr_freq_rec.RetentionNumber;

      WHILE Table_Incr <Table_Count
      LOOP
      / *
      ** Try archiving the first rows
      If ** Specified in the HISRFrequency table,
      Select rows ** target archive table and Them.
      **
      * /

      return_status: =
      HISR_ARCHIVE.f_hisr_archive_oldest (hisr_freq_rec);


      if (1 = return_status)
      THEN
      feedback: = DBMS_SQL.EXECUTE (delete_cur);
      COMMIT;
      ELSE
      Table_Incr: = Table_Count / * JIGGER EXIT CONDITION * /
      END IF;
      Table_Incr: = Table_Incr + feedback;
      HISR_DEBUG.p_hisr_debug_out ('RETENTION' hisr_freq_rec.tablename | | 'Table_Incr:' | | Table_Incr | | 'Table_Count:' | | Table_Count, 'NO');
      END LOOP;

      DBMS_SQL.CLOSE_CURSOR (delete_cur);

      HISR_DEBUG.p_hisr_debug_out ('RETENTION', 'Leaving p_hisr_truncate_excess', 'NO');

      EXCEPTION
      WHEN OTHERS
      THEN

      ERROR_MESSAGE: =
      to_char (HISR_UTILITY.f_hisr_sysdate_plus_offset, 'YYYY / MM / DD HH24: MI: SS') | |
      'RETENTION: Could determine count of rows, ERROR:' | | to_char (SQLCODE) | | ':' | | SQLERRM (SQLCODE);
      HISR_UTILITY.p_hisr_update_frequency
      (ERROR_MESSAGE
      , Hisr_freq_rec.FrequencyEntryNumber
      );

      IF (DBMS_SQL.IS_OPEN (delete_cur))
      THEN
      DBMS_SQL.CLOSE_CURSOR (delete_cur);
      END IF;

      END;

      FUNCTION f_hisr_row_counter
      (Hisr_freq_rec IN HISRFrequency% ROWTYPE)
      RETURN BINARY_INTEGER
      IS

      Rows_In_Table BINARY_INTEGER: = 0;
      feedback BINARY_INTEGER;

      select_cur BINARY_INTEGER: = DBMS_SQL.OPEN_CURSOR;
      sql_string VARCHAR2 (200): =
      'SELECT COUNT (' | | hisr_freq_rec.DateTimeColumnName
      | | ')' | |
      'FROM' | | hisr_freq_rec.TableName;

      BEGIN

      / *
      Select count ** of DateTimeColumnName routine
      * /
      DBMS_SQL.PARSE (select_cur, sql_string, DBMS_SQL.NATIVE);

      DBMS_SQL.DEFINE_COLUMN (select_cur, 1, Rows_In_Table);

      feedback: = DBMS_SQL.EXECUTE (select_cur);

      LOOP

      EXIT WHEN DBMS_SQL.FETCH_ROWS (select_cur) = 0;
      DBMS_SQL.COLUMN_VALUE (select_cur, 1, Rows_In_Table);

      END LOOP;

      DBMS_SQL.CLOSE_CURSOR (select_cur);

      RETURN (Rows_In_Table);

      EXCEPTION
      WHEN OTHERS
      THEN

      ERROR_MESSAGE: =
      to_char (HISR_UTILITY.f_hisr_sysdate_plus_offset, 'YYYY / MM / DD HH24: MI: SS') | |
      'RETENTION: Could not determine count of rows, ERROR:' | | to_char (SQLCODE) | | ':' | | SQLERRM (SQLCODE);
      HISR_UTILITY.p_hisr_update_frequency
      (ERROR_MESSAGE
      , Hisr_freq_rec.FrequencyEntryNumber
      );

      IF (DBMS_SQL.IS_OPEN (select_cur))
      THEN
      DBMS_SQL.CLOSE_CURSOR (select_cur);
      END IF;

      RETURN (0);

      END;


      HISR_RETEN END;
      /