Re: ORA-01405: fetched column value is NULL

msallwas_at_world.nad.northrop.com (Michael Sallwasser) writes:
: In article <1994Jul28.181547.15433_at_rossinc.com> davidmo_at_rossinc.com (David Moore) writes:
: >I have a program that returns the above error. Assuming I don't have the
: >the source, what would be the easiest way to determine which table/row/column
: >is NULL??
:
: I am not positive I understand the comment, "I don't have the source" although
: I guess you mean the SQL statement. But here goes.
:
: SELECT ROWID
: FROM one_of_your_tables
: WHERE a_column IS NULL.
:
: Repeat the above for every column in every table. This will give you the
: rowid and you could use that to delete the offending rows, if desired.
:
: You could also try the following:
:
: DELETE FROM one-of-your-tables
: WHERE column_1 IS NULL
: OR column_2 IS NULL
: OR column_3 IS NULL ...
:
: Since you have RTFM, I can only assume I do not understand the original
: question, or that you are a novice and RTFM is not a guarantee to
: UTFM (U = Understanding).
:
: Hope this helps :-)
: --
: ============================================================================
: Michael Sallwasser | Down one path is utter dispair and hopelessness. Down
: Northrop Grumman | the other is total destruction. Let us choose wisely.

When you say, " I don't have the source", does that mean you are using one of the Oracle Pre-Compilers, and the program in question is a PRO*C etc program? If so, the following may be of interest. If it is not what you are looking for, apologies, but the questions was somewhat vague.

    In Versions 1.3/1.4 of precompilers, it was not mandatory to use an indicator host variable while fetching a null value. However, Version 1.5+ of the precompilers will return an ORA 1405 (fetched column value is null) when no indicator variable is used. Hence, with Version 1.5+ precompilers it is mandatory to use indicator variables. This change was introduced to ensure full compliance to the SQL89 and SQL92 standards.

    Migration of applications from 1.4 to 1.5 precompilers requires usage of indicator variables in order to avoid the ORA 1405 error. But this implies a lot of changes to the existing code, which is not a viable solution for any large existing application. In order to preserve this V6 behavior for O7 applications the precompiler option DBMS=V6|V7 is provided. With DBMS=V6, the ORA 1405 error is not generated.

1.5 is shipped with the ORACLE RDBMS 7.

--
regards,
Carl
+-----------------------------------------------------------------------------+
Carl Gohringer, 
Oracle UK, The Oracle Centre, The Ring, Bracknell, Berkshire, England, RG121BW
Internet   : cgohring_at_uk.oracle.com    
+-----------------------------------------------------------------------------+