Pls save me

[Dexter]

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

[Ron]

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_ad...isclaimer.html

"Dexter" <de****@rec-all.com> wrote in message
news:60**************************@posting.google.c om...

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

[Dexter]

Thanks ron, you r my saviour man,

this is the version i'm using.

SQL> select * from v$version
2 ;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
PL/SQL Release 9.2.0.2.0 - Production
CORE 9.2.0.2.0 Production
TNS for Solaris: Version 9.2.0.2.0 - Production
NLSRTL Version 9.2.0.2.0 - Production

Bullseye i'm using DBlinks those tables are all remote table in some other server.

Please get back to me with wat i need to do to resolve this problems.

Thanks,
Dexter.

"Ron" <su*****@dbainfopower.com> wrote in message news:<2e********************@comcast.com>...

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_ad...isclaimer.html

"Dexter" <de****@rec-all.com> wrote in message
news:60**************************@posting.google.c om...

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

[Ron]

Hello Dexter,

If you can, please, open Oracle TAR (or ask DBA to open it) on this
issue.

Get DBAs help to research metalink as well (search for Bug 2765286).

If this impacting production - open it as sev 1. to get fast response.

From what I can see, you may need DBAs help to apply recommended patch
set.

Also, for bulk operations across db_links - please, check if both
databases are at the same Oracle version (9i-9i) - check with Oracle on this
as well (metalink Note:198842.1).

Regards,

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


"Dexter" <de****@rec-all.com> wrote in message
news:60**************************@posting.google.c om...

Thanks ron, you r my saviour man,

this is the version i'm using.

SQL> select * from v$version
2 ;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
PL/SQL Release 9.2.0.2.0 - Production
CORE 9.2.0.2.0 Production
TNS for Solaris: Version 9.2.0.2.0 - Production
NLSRTL Version 9.2.0.2.0 - Production

Bullseye i'm using DBlinks those tables are all remote table in some other server.
Please get back to me with wat i need to do to resolve this problems.

Thanks,
Dexter.

"Ron" <su*****@dbainfopower.com> wrote in message

news:<2e********************@comcast.com>...

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_ad...isclaimer.html

"Dexter" <de****@rec-all.com> wrote in message
news:60**************************@posting.google.c om...

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

[Dexter]

tnx ron will do tht asap.

Regards,
Dexter.

"Ron" <su*****@dbainfopower.com> wrote in message news:<Qo********************@comcast.com>...

Hello Dexter,

If you can, please, open Oracle TAR (or ask DBA to open it) on this
issue.

Get DBAs help to research metalink as well (search for Bug 2765286).

If this impacting production - open it as sev 1. to get fast response.

From what I can see, you may need DBAs help to apply recommended patch
set.

Also, for bulk operations across db_links - please, check if both
databases are at the same Oracle version (9i-9i) - check with Oracle on this
as well (metalink Note:198842.1).

Regards,

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


"Dexter" <de****@rec-all.com> wrote in message
news:60**************************@posting.google.c om...

Thanks ron, you r my saviour man,

this is the version i'm using.

SQL> select * from v$version
2 ;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
PL/SQL Release 9.2.0.2.0 - Production
CORE 9.2.0.2.0 Production
TNS for Solaris: Version 9.2.0.2.0 - Production
NLSRTL Version 9.2.0.2.0 - Production

Bullseye i'm using DBlinks those tables are all remote table in some other

server.

Please get back to me with wat i need to do to resolve this problems.

Thanks,
Dexter.

"Ron" <su*****@dbainfopower.com> wrote in message

news:<2e********************@comcast.com>...

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_ad...isclaimer.html

"Dexter" <de****@rec-all.com> wrote in message
news:60**************************@posting.google.c om...
> 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