Re: Pro*C and Indicator Variables Question
Date: 1996/12/30
Message-ID: <32c81437.17912096_at_dcsun4>#1/1
On Mon, 30 Dec 1996 10:47:30 -0800, rcarey <rcarey_at_gte.net> wrote:
>I've used Pro*C for a while now, however the use of indicator
>variables are somewhat new for me. Any help or comments would
>be appreciated.
>
>I have a question about using Pro*C, PL/SQL and indicator
>variables. In a simple select statement like shown below, would
>the route.len value be zero if route_cd was a NULL value?
>
>
> EXEC SQL SELECT route_cd into :route
> FROM waste
> WHERE pkg_id = :pkg;
>
> route.arr[route.len] = NULL;
both the entire route data structure would be left alone if route_cd was NULL (eg: it would be unchanged, whatever garbage happened to be in the structure route before the select would be there after. Therefore route.len may have zero, or it may not... you can't tell)
>
>
>I was told by Oracle that even when using the NVL function I
>needed to use indicator variables to set float variables to
>zero if there was a NULL returned from the database
>(ie: if (id_value == -1) value = 0.0; placed in my C code). I tried
>it and it works great. Seems strange that the NVL function
>wouldn't take care of it for me.
>
No, you've been led astray.... NVL( column, 0 ) will return 0 and never, never null. You only need to use an indicator variable with NVL if the NVL returns NULL itself... Consider the following code (which always prints 0)
static void process()
{
float x;
x = 124;
exec sql whenever sqlerror do sqlerror_hard();
exec sql create table foo ( x int );
exec sql insert into foo values ( null );
exec sql select nvl( x, 0 ) into :x from foo;
exec sql drop table foo;
printf( "x = %g\n", x );
}
If you use nvl, AND your NVL functions don't themselves return NULL, you don't need to use an indicator variable.
>Does anyone know if NVL would be required for varchar data or would
>I need to use indicator variables for that as well?
>
>Thanks,
>
>Rob Carey
>rcarey_at_gte.net
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Mon Dec 30 1996 - 00:00:00 CET