473,320 Members | 1,883 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
Jul 19 '05 #1
4 3382
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

Jul 19 '05 #2
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

Jul 19 '05 #3
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

Jul 19 '05 #4
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

Jul 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: F. Hall | last post by:
If I read a bitmap image from one file and save it to another the save operation is slow unless I draw on the image. In other words, Image inputImage = Image.FromFile( @"c:\temp\source.bmp" );...
4
by: WJA | last post by:
I'm probably missing something here but I can't understand the following. When 2 users try to save a record with the same primary key (a number field), the first record saves as expected, but the...
5
by: kevin | last post by:
Hi, Any help with this would be really appreciated! I'm trying to download a file from a remote server. The access permissions is okay but the problem I'm facing is that the file is getting...
2
by: Alvo von Cossel I | last post by:
hi, you can create a file with the savefiledialog. once you have done that and edit the file in the text editior that i wrote you cannot save the file, you can only create a new file or replace...
10
by: GJP | last post by:
Hello. Ive been asked to make my own notepade for college assignment. All ig going well, but i cant get the save to work. I can get Save a (shows dialog box), i can get it to just save too,...
5
by: C Watson | last post by:
Hi, I'm wondering if anyone can help me with AJAX in ASP.NET 1.1. I have a very specific feature that I would like to use it for. I have a rather long form that the users use to enter data...
3
by: =?Utf-8?B?YXNkZg==?= | last post by:
Hello. I am making a web application with c# and am using this code: Response.ContentType = "application/x-excel"; Response.AddHeader("Content-Disposition", "attachment;filename=" +...
0
by: amrhi | last post by:
Hy Guys , Can anybody help me ? I try to make small web database in my unit. Some of fields have on change behaviour to get other data that automatically filled other text field. But when i try to...
12
by: =?Utf-8?B?RnJlZU5FYXN5?= | last post by:
Hello, the scenario: There's an ASPX page which shows some text and has three buttons at the bottom: Save, Print and Close. Print and close is done by javascript. But how can I save the page...
0
by: ppardi | last post by:
I'm developing an addin for Word 2007 and I need to determine whether a user saves a Word 2007 document in an older format (97-2003) after a save as is done. The scenario is that the user starts...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.