Re: Pls save me

Hello Dexter,

1. Please check and post oracle version - there are some related bugs that were fixed in Patch Sets -

    ( on metalink look for articles like this one : 9.2.0.3 Patch Set - List of Bug Fixes by Problem Type)

2. Can you please check if this is a local table or remote table

 Example:

    select DB_LINK from all_synonyms where synonym_name = '<table_name>';

 There are some bugs related to bulk inserts across db_links.

Please, let us know.

Regards,

  Ron
  DBA Infopower
  http://www.dbainfopower.com
  Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html

"Dexter" <dexter_at_rec-all.com> wrote in message news:607d10e1.0402150723.5f7c861a_at_posting.google.com...
> 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 - 17:57:47 CET