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<count er;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]

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@rec-all.comwrote in message
news:607d10e1.0 402150723.5f7c8 61a@posting.goo gle.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<count er;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]

Re: Pls save me

Thanks ron, you r my saviour man,

this is the version i'm using.

SQLselect * 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" <support@dbainf opower.comwrote in message news:<2eednZMMs _EWPrLdRVn-ug@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_advice_disclaimer.html

>
>
>
"Dexter" <dexter@rec-all.comwrote in message
news:607d10e1.0 402150723.5f7c8 61a@posting.goo gle.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<count er;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]

Re: Pls save me

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_advice_disclaimer.html

"Dexter" <dexter@rec-all.comwrote in message
news:607d10e1.0 402160619.655a0 ea3@posting.goo gle.com...

Thanks ron, you r my saviour man,
>
this is the version i'm using.
>
SQLselect * 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" <support@dbainf opower.comwrote in message

news:<2eednZMMs _EWPrLdRVn-ug@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_advice_disclaimer.html

"Dexter" <dexter@rec-all.comwrote in message
news:607d10e1.0 402150723.5f7c8 61a@posting.goo gle.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<count er;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]

Re: Pls save me

tnx ron will do tht asap.

Regards,
Dexter.

"Ron" <support@dbainf opower.comwrote in message news:<QoGdne03b bIMOq_dRVn-ug@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_advice_disclaimer.html

>
>
>
>
"Dexter" <dexter@rec-all.comwrote in message
news:607d10e1.0 402160619.655a0 ea3@posting.goo gle.com...

Thanks ron, you r my saviour man,

this is the version i'm using.

SQLselect * 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" <support@dbainf opower.comwrote in message

news:<2eednZMMs _EWPrLdRVn-ug@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_advice_disclaimer.html

>
>
>
"Dexter" <dexter@rec-all.comwrote in message
news:607d10e1.0 402150723.5f7c8 61a@posting.goo gle.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<count er;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