CODEWITHSUNDEEP
coracle-databaseerror-handlingcursororacle11g
iPadDevloperJr
iPadDevloperJr

Reputation: 1004

Oracle Pro*C : Handling end of fetch cursor

I can't figure out the problem on my code :

/* Declare a cursor for the FETCH statement. */
EXEC SQL DECLARE customer_cursor CURSOR FOR
SELECT CUSTOMER_ID, CUSTOMER_NAME
FROM CUSTOMER_TABLE 
WHERE CUSTOMER_CARD_NUM = :argv[1];

if ( sqlca.sqlcode != 0 )
{
        printf("Declare cursor failed\n");
        return( sqlca.sqlcode );
}
EXEC SQL OPEN customer_cursor;
if ( sqlca.sqlcode != 0 )
{
        printf("Open cursor failed\n");
        return( sqlca.sqlcode );
}
EXEC SQL WHENEVER NOT FOUND GOTO no_match;

for ( ;; )
{
    /* Fetching data */
    EXEC SQL FETCH customer_cursor 
        INTO :var_customer_id, :var_customer_name;
    if ( sqlca.sqlcode != 0 )
    {
            EXEC SQL CLOSE cust_data_cursor;
            return ( sqlca.sqlcode );
    }

    /* Doing some stuff here */
    processing_customer();

}
EXEC SQL CLOSE customer_cursor;

/* Handling the no data found here */    
no_match:

      printf("NO CUSTOMER MATCHING THIS CARD_NUM\n");
      /* Some stuff */
      ......
      return 1;

My query is supposed to return just one row or nothing, when nothing is returned all is ok, but when ther's a matching, the function processing_customer is executed and the strange is that the no_match is executed too.

Thanks for helping me to fix that.

Upvotes: 0

Views: 5581

Answers (3)

0zkr PM
0zkr PM

Reputation: 863

Change your label "no_match" to "no_more_records" and you'll realize why it runs 2 times:

a) when there are no records, the FETCH raises the NOT FOUND condition inmediatly and therefore will go to the label "no_more_records"

b) when there is one (or more) record(s), the FETCH is executed returning the first record.

Then the

if ( sqlca.sqlcode != 0 )

evaluates to false (in fact, only useful to trap other problems) and then

processing_customer();

After it, the FETCH run again (by the infinity for) and behave as in (a): no_more_records condition arrives.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191435

As @Roger Cornejo suggests, you need a way to not execute the 'no match' section if there is a match. no_match: is just a label so there's nothing to tell your code not to execute that section. You either need to return before that label, or goto something after it. You also need to close the cursor in the no-match scenario.

But this seems unnecessarily messy and as @Arion hinted you don't need an explicit cursor for this - just do a select into and catch the exception.

EXEC SQL SELECT CUSTOMER_ID, CUSTOMER_NAME
    INTO :var_customer_id, :var_customer_name
    FROM CUSTOMER_TABLE 
    WHERE CUSTOMER_CARD_NUM = :argv[1];

if (sqlca.sqlcode == 1403)
{
    printf("NO CUSTOMER MATCHING THIS CARD_NUM\n");
    /* Some stuff */
    ......
    return 1;
}
if (sqlca.sqlcode != 0)
{
    return ( sqlca.sqlcode );
}

/* Doing some stuff here */
processing_customer();

You've said there will be zero or one rows; if there are more than one you'll get a too-many-rows error (ORA-02112).

Upvotes: 1

Roger Cornejo
Roger Cornejo

Reputation: 1547

add GOTO after "EXEC SQL CLOSE customer_cursor;"

Upvotes: 0

Related Questions