ORA-01405: fetched column value is NULL

I got ” ORA-01405: fetched column value is NULL ” error in Oracle database.

 

ORA-01405: fetched column value is NULL

 

Details of error are as follows.

ORA-01405 fetched column value is NULL

Cause: The INTO clause of a FETCH operation contained a NULL value, and no indicator was used.
 The column buffer in the program remained unchanged, and the cursor return code was +2.
 This is an error unless you are running Oracle with DBMS=6, emulating version 6, in which
 case it is only a warning.

Action: You may do any of the following:
Use the NVL function to convert the retrieved NULL to another value, such as zero or blank. 
This is the simplest solution.
Use an indicator to record the presence of the NULL. You probably should use this option when
 you want a specific action to be taken when a NULL arises.
Revise the cursor definition so that no columns possibly containing NULL values are retrieved.

*** ACTION NAME:(QMON Slave) 2010-05-14 13:20:23.983

kwqdlScanDeqLog during stmt fetch: retval -1, errnum 1405, errbuf ORA-01405: fetched column value is NULL

kwqitmmit during kwqitmmit:deq log proc: Error 1405 in Queue Table SYS.SYS$SERVICE_METRICS_TAB

*** 2010-05-14 13:20:24.501
kwqicaclcur: Error 1405
Cursor Session Number : 8
Cursor Session Serial : 1426
Cursor Pin Number : 24585
kwqitmmit during kwqitmmit:end proc: Error 1405 in Queue Table SYS.SYS$SERVICE_METRICS_TAB
kwqitptm: error 1405 encountered during serving 12861
KSV 1405 error in slave process

*** 2010-05-14 13:20:24.514
ORA-01405: fetched column value is NULL
ORA-01405: fetched column value is NULL
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-01405: fetched column value is NULL
ORA-01405: fetched column value is NULL


    

 

 

fetched column value is NULL

This ORA-01405 errors are related with the INTO clause of a FETCH operation contained a NULL value, and no indicator was used. The column buffer in the program remained unchanged, and the cursor return code was +2. This is an error unless you are running Oracle with DBMS=6, emulating version 6, in which case it is only a warning.

 

Use the NVL function to convert the retrieved NULL to another value, such as zero or blank.
This is the simplest solution.

Use an indicator to record the presence of the NULL. You probably should use this option when you want a specific action to be taken when a NULL arises.

Revise the cursor definition so that no columns possibly containing NULL values are retrieved.

 

You can use the NVL function to substitutes a value when a null value is encountered.

 

Second case’s solution is as follows.

To fix current corrupted messages, delete all entries in the dequeue table log (AQ$_<QT>_L) which have message id of ‘00000000000000000000000000000000’ as follows:

Important:

-- For environments running with millions of processed messages, these steps may take some time to complete, so please plan accordingly.
-- It may also fail if there are very high enqueue/dequeue concurrence operations for the affected queue, therefore we recommend stopping
-- the queue for enq/deq operations.

1. Stop all the queues in the relevant queue table:

$ sqlplus /nolog

connect / as sysdba

set line 200

select
owner, name, queue_type, enqueue_enabled, dequeue_enabled
from
dba_queues
where
owner='SYS'  and
queue_table='SYS$SERVICE_METRICS_TAB' and
queue_type='NORMAL_QUEUE';

--Note1: Replace '<owner>.<name>' appropriately with your affected queue table:
exec dbms_aqadm.stop_queue(queue_name => 'SYS.SYS$SERVICE_METRICS');

--Note2: The event 10852 should be disabled to complete the remaining steps:
select inst_id, value from gv$parameter where name like 'event';

--Execute this if previous query returned row for event 10852, otherwise proceed to step 2:
alter system set events  '10852 trace name context off';

2. Delete the unnecessary/corrupted entries:

delete from sys.aq$_SYS$SERVICE_METRICS_TAB_L where msgid ='00000000000000000000000000000000';
commit;

3. Purge the PROCESSED messages for all consumers as reported in the AQ$ view.

DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := TRUE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table     => 'SYS.SYS$SERVICE_METRICS_TAB',
purge_condition => 'qtview.queue =  ''SYS.SYS$SERVICE_METRICS''
and qtview.msg_state = ''PROCESSED''',
purge_options   => po);
END;
/

commit;

4. Run below query to make sure rows were deleted:

select
count(*)
from
SYS.
aq$_SYS$SERVICE_METRICS_TAB_L
where
msgid ='00000000000000000000000000000000';

5. Once the above query returns 0 row, enable (or re-enable if using already) event 10852 executing below commands which will disable the new 11.2 dequeue functionality and revert back to pre 11.2 where the dequeue log table is not used:

alter system set events '10852 trace name context forever, level 16384';
alter system set event='10852 trace name context forever, level 16384' scope=spfile;

6. Re-start the queue again:

exec dbms_aqadm.start_queue(queue_name => 'SYS.SYS$SERVICE_METRICS')

Note: The above steps were executed presuming the offended queue was SYS$SERVICE_METRICS, so the information on above SQLs were used as follows:

   <owner> = SYS
   <queue> = SYS$SERVICE_METRICS
   <queue_table> = SYS$SERVICE_METRICS_TAB

When implementing at your end, make sure to replace the values appropriately with your affected queue/queue table.

 

II. To prevent new log messages to get corrupted, you may:

 

a) Upgrade/apply PSU for one of the fixed RDBMS versions as per Doc ID 12899768.8, or apply one-off for bug 12899768 on top of your current RDBMS version if available.

or

b) In 11.2 without back-port for 12899768, use workaround of enabling event 10852 as provided on step 5 previously:

conn / as sysdba
alter system set events ‘10852 trace name context forever, level 16384′;
alter system set event=’10852 trace name context forever, level 16384’ scope=spfile;

Note: In 12.1 or if using patch 12899768, the event 10852 can be disabled.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 1 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

IT Tutorial