Can't test nulls in Oracle Pro*C

Question

I am beginner about Proc Oracle and C. When I practice example base on Oracle Proc document, I can't test Nulls which use indicator, reference Oracle Pro*c guide link(I am not sure my code is correct).

Oracle document write:

6.2.4 Test for NULLs You can use indicator variables in the WHERE clause to test for NULLs, as the following example shows:

EXEC SQL SELECT ename, sal INTO :emp_name, :salary FROM emp WHERE :commission INDICATOR :ind_comm IS NULL ...

My code as follows:

Locations table:

+-------------+---------+---------+
| Location_Id | Address | Country |
+-------------+---------+---------+
|           1 | London  |  London |
|           2 | US      |    null |
+-------------+---------+---------+


void connect()
{
    /* Connect to ORACLE. */
    strcpy(username, "hr");
    strcpy(password, "hr");

    EXEC SQL DECLARE DB_NAME DATABASE;
    EXEC SQL CONNECT : username IDENTIFIED BY : password;
    printf("\nConnected to ORACLE as user: %s\n", username);
}

void testNull()
{
    short country_ind = -1;
    char address[20];
    char *country = "country";
    EXEC SQL SELECT ADDRESS INTO :address FROM LOCATIONS
    WHERE :country INDICATOR :country_ind is null;
    /*
Current, it always output address is London instead of US
 */
        printf("address :%d\n", address);
    }

void main()
{
    connect();
    testNull();
}

Answer 1

The use of indicator is not "indicated" here (unintended pun)

I would have written it this way

void testNull()
{
    short country_ind = -1;
    char address[20];
    char *country = "country";
    EXEC SQL SELECT ADDRESS INTO :address FROM LOCATIONS
    WHERE country  is null;
    /*
Current, it always output address is London instead of US
 */
        printf("address :%d\n", address);
    }

And you can use an indicator for the addess should you need to check if it is null afterwards

If you want the "country" to be a variable then your code should be something like :

void testNull()
{
    short country_ind = 0;   // the word "country" is not null, but the value of the field can be depending on the query/data
     char address[20];
   char *country = "country";
EXEC SQL SELECT ADDRESS INTO :address FROM LOCATIONS
WHERE :country INDICATOR :country_ind is null;
/*
Current, it always output address is London instead of US
 */
        printf("address :%d\n", address);
    }