Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Confusing ORA-1001 Error

Confusing ORA-1001 Error

From: Gerald Winslow <gerald.winslow_at_titan.com>
Date: 13 Sep 2002 07:41:51 -0700
Message-ID: <7bc6b913.0209130641.6f174abb@posting.google.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US