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

[carlinodba]

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

BEGIN xajtdb.HISR_RETEN.p_hisr_retention_all; 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;
/

[burleson]

Hi Carlino,

This is a DBA forum, DBA's are not always PL/SQL proficient!

BTW, here are notes on PL/SQL debugging. Read this carefuly and follow the steps:

http://www.dba-oracle.com/t_pl_sql_debugger.htm

*********************************************

>> The most curious is that the problem is not production.

Not really. Your error is "no data found", a common issue!!


*********************************************
>> I consult for a mistake . . . I do not have much experience in PL-SQL

Do they know that you are inexperienced?

I would not assign PL/SQL to a beginner, especially if they charge by the hour!

If you want to share the money and hire me to help you, here are my rates:

http://www.dba-oracle.com/consulting_prices.htm#burleson

[carlinodba]

Thanks!

Hi Carlino,

This is a DBA forum, DBA's are not always PL/SQL proficient!

BTW, here are notes on PL/SQL debugging. Read this carefuly and follow the steps:

http://www.dba-oracle.com/t_pl_sql_debugger.htm

*********************************************

>> The most curious is that the problem is not production.

Not really. Your error is "no data found", a common issue!!


*********************************************
>> I consult for a mistake . . . I do not have much experience in PL-SQL

Do they know that you are inexperienced?

I would not assign PL/SQL to a beginner, especially if they charge by the hour!

If you want to share the money and hire me to help you, here are my rates:

http://www.dba-oracle.com/consulting_prices.htm#burleson