Pls save me

Hi,

I have a pro*c program that inserts records into a table from another table.This was working fine until i recently upgrade from 8i to 9i.

Now the problem is it errors out frequently with ora-1400 (cannot insert NULL...) or 0ra-1458 (length inside Variable character...) But when i rerun it it goes fine, for the same records!!! I'm not able to predict its behavior.

When i change the code to do row by row insert instead of a bulk insert it goes fine without any problem.

Can anybody explain way this is happening? You could be my saviour...

Code snippet:
This errors out unpredictably..

EXEC SQL FOR :counter

              INSERT INTO TMP1 (
                      a,
		      b,
		      c,
		      d,
		      e,
		      f,
		      g,
		      h,
		      j,
		      k,
		      l,
               VALUES (
                      TO_NUMBER(Id)


                     ,:Key

                     ,c1

                     ,d1

                     ,e1

                     ,SYSDATE

                     ,g1

                     ,SYSDATE

                     ,i1

                     ,SYSDATE


                     ,h1

When i change it to th following it works fine...

for(i=0;i<counter;i++)
{
EXEC SQL INSERT INTO TMP1 (

                      a,
		      b,
		      c,
		      d,
		      e,
		      f,
		      g,
		      h,
		      j,
		      k,
		      l,
               VALUES (
                      TO_NUMBER(Id)


                     ,:Key

                     ,c1

                     ,d1

                     ,e1

                     ,SYSDATE

                     ,g1

                     ,SYSDATE

                     ,i1

                     ,SYSDATE


                     ,h1

}

Thanks,
Dexter Received on Sun Feb 15 2004 - 16:23:05 CET