Confusing ORA-1001 Error

I have some Pro*C code that loops through a cursor (created using dynamic SQL) and INSERTs the FETCHed values into a table. There are 2 rows returned in the cursor. The first row gets inserted OK. However, the cursor apparently gets invalidated somehow, because the second time through the loop, the fetch fails (ORA-1001), but it attempts to do the insert anyway. Since the host variables haven't changed from the first fetch, the insert fails with a ORA-0001 (duplicate val on key).

What's weird about this is that this code has worked correctly for months. Yesterday it worked fine.

So, what I want to know is how is the cursor getting invalidated? Here's a snippet of the Pro*C:

      EXEC SQL OPEN c_create_bcl_pmf_codes USING :v1, :v2, :v3, :v4;
      if (sqlca.sqlcode != 0)


      {

        /* ORACLE error issued */
      }
      else


      {

        for (;;)
        {
          EXEC SQL WHENEVER NOT FOUND DO break;
          EXEC SQL FETCH  c_create_bcl_pmf_codes
                   INTO   :t_pmf_step_seq_num,
                          :t_pmf_proc_prefix,
                          :t_pmf_code; 
	  /* 2nd time thru - ORA-1001 */                        
          EXEC SQL WHENEVER NOT FOUND CONTINUE;

          EXEC SQL INSERT INTO cmdb_bcl_pmf_codes
                           (...)                                  
                    VALUES (...);
          if (sqlca.sqlcode != 0)
          {
            /* here's where our ORA-00001 comes out on the 2nd time
thru */
          }
          else
          {
            t_update_count++;

            if (t_update_count >= 100)                 
            {
              t_update_count = 0;
              /* COMMIT goes here */
            } /* if t_update_count */
          } /* if INSERT sqlcode */
        } /* for loop */

        EXEC SQL CLOSE c_create_bcl_pmf_codes;
      } /* if OPEN sqlcode */

Anybody got any ideas?

Thanks!
Gerald Winslow
Titan Systems Corp. Received on Fri Sep 13 2002 - 09:41:51 CDT