Is this a bug? What do I do?

305235

Hi all,
I am running an update of a table with 2 columns from 9.2.0.8 to 8.1.7.4 over database link.
First column (number) is updated, but second appears to be NULL. ?!
I've tried to do it from 8.1.7 to 9.2, but with the same result.
Then I've tried to create a table on 8.1.7 side and insert data from 9.2 into it first, but .... same: NULL values for char colums.
Is it a bug?
What else can I try?

Thanks,
Eugene

367117

Second column has data ?

If you post the update statement, it might help.

sb92075

What else can I try?

using CUT & PASTE so we can see what you do & how Oracle responds.

728534

Also,
Check for any triggers on that table.
If it is a view check for instead of update trigger.

Regards,
Bhushan

305235

Thanks all for replies.
This is the oddest thing and that is why I was asking you if it's a bug. It worked this time and you will laugh at me.

Here is what I've done:

============
9.2 DB
============

select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

desc macys_store_names;
Name Null? Type
---------------------------------------------------------------------------------------------------
PH00_PKT_CTL_NBR NOT NULL NUMBER(6)
STORE_NAME CHAR(25)

truncate table macys_store_names;

Table truncated.

select count(*) macys_rec_num
2 from [email protected]
3 where ph00_soldto like 'MACYS%'
4 and ph00_pkt_stat_flg = ' ';

MACYS_REC_NUM
-------------
1292

insert into macys_store_names
2 SELECT ph00_pkt_ctl_nbr,
3 substr(defa_cust_store_name,1,25) store_name
4 FROM sales_order A,
5 customer_store b,
6 (
7 select ph00_pkt_ctl_nbr,
8 '81' business_unit_id,
9 SUBSTR(ph00_order,1,1) sales_order_prefix,
10 LTRIM(SUBSTR(REPLACE((ph00_order||ph00_order_sfx),' ',''),2),0) sales_order_numb
er
11 from [email protected]
12 where ph00_soldto like 'MACYS%'
13 and ph00_pkt_stat_flg = ' '
14 ) c
15 WHERE A.business_unit_id = b.business_unit_id AND
16 A.customer_id = b.customer_id AND
17 A.customer_store_id = b.customer_store_id AND
18 a.business_unit_id = c.business_unit_id
19 and a.sales_order_prefix = c.sales_order_prefix
20 and a.sales_order_number = c.sales_order_number;

1292 rows created.

commit;

Commit complete.

select * from macys_store_names where rownum < 10;

PH00_PKT_CTL_NBR STORE_NAME
---------------- -------------------------
567384 Fort Steuben
567385 Fort Steuben
567386 Chesterfield MO
567387 Chesterfield MO
567388 Mid Rivers
567389 Mid Rivers
567390 White Oaks
567391 White Oaks
567392 Market Place IL

9 rows selected.

update [email protected] a
2 set ph00_mark_for = (select store_name
3 from macys_store_names b
4 where a.ph00_pkt_ctl_nbr = b.ph00_pkt_ctl_nbr)
5 ,ph00_lm_user_id = 'MACYS_STOR'
6 ,ph00_dlm = sysdate
7 where ph00_pkt_ctl_nbr in
8 (select ph00_pkt_ctl_nbr from macys_store_names);

1292 rows updated.

commit;


============
8.1.7 DB
============

select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.2.1 Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production


select ph00_pkt_ctl_nbr, ph00_mark_for
2 from phpick00
3 where ph00_pkt_ctl_nbr in
4 (select ph00_pkt_ctl_nbr from [email protected])
5 and rownum < 10
6 ;

PH00_PKT_CTL_NBR PH00_MARK_FOR
---------------- -------------------------
566781 Queens
566782 Queens
566783 Brooklyn
566784 Brooklyn
566785 Herald Square
566786 Herald Square
566787 Westfarms Mall
566788 Westfarms Mall
566789 Manhasset

9 rows selected.


Thanks,
Eugene

Edited by: epipko on Oct 19, 2009 10:40 AM

Edited by: epipko on Oct 19, 2009 10:44 AM

Centinul

Just to let you know you may want to edit your post and remove things like user names, fully qualified server names and other sensitive data.

305235

thanks