Forum Stats

  • 3,840,015 Users
  • 2,262,558 Discussions
  • 7,901,123 Comments

Discussions

Is this a bug? What do I do?

305235
305235 Member Posts: 112
edited Oct 19, 2009 1:45PM in General Database Discussions
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
Tagged:

Answers

  • 367117
    367117 Member Posts: 668
    Second column has data ?

    If you post the update statement, it might help.
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    What else can I try?
    using CUT & PASTE so we can see what you do & how Oracle responds.
  • 728534
    728534 Member Posts: 1,386
    Also,
    Check for any triggers on that table.
    If it is a view check for instead of update trigger.

    Regards,
    Bhushan
  • 305235
    305235 Member Posts: 112
    edited Oct 19, 2009 1:44PM
    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
    Centinul Member Posts: 6,871 Bronze Crown
    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.
    Centinul
This discussion has been closed.