IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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 )
