Re: How to handle error ORA-01405?

In article <2vjo9j$8uh_at_arcadia.informatik.uni-muenchen.de>, Richard Spitz <spitz_at_ana.med.uni-muenchen.de> wrote:
>Dear Oracle experts,
>
>this is probably a newbie question, but I have only limited access to
>manuals, so this is my only hope.
>
...

>Almost all of the rows have NULL values in one or more columns, so
>I have to find a solution. I tried with "WHENEVER ERROR CONTINUE".
>That works, but now there is only one column returned per fetch
>instead of 200 (size of host array). This seriously degrades per-
>formance over the network.
>
 

Along with the array of values you want returned, you also need an array of indicator values (short, I believe). This must be declared in the declare selection along with the attribute array. When you issue the select, use the following format:

EXEC SQL BEGIN DECLARE;
  short indicator[100];
...
EXEC SQL END DECLARE; SELECT attribute:indicator FROM TABLE;

The returned indicator can have one of the following values:  -2 ==> The column is longer than the max that can be returned. The

         returned value is truncated.

-1 ==> The column is null. The value of the host variable is unchanged.

0 ==> The host variable has been populated with a column value

>0 ==> The column length is longer than allocated for the host variable.

         The data will be truncated and the indicator variable will contain
         the length of the data before truncation.

Once you execute the fetch, you'll need to test the indicator to determine the "nullness" of the data.

I hope this helps.

Mike Marolda
Bristol Database Resources Inc. Received on Fri Jul 08 1994 - 23:14:43 CEST