committed row insert doesn't show first time...

From: Purcell, Sandra <SPurcell_at_temgweb.com>
Date: Thu, 23 Jan 2003 12:49:08 -0800
Message-ID: <F001.00538EB1.20030123124908@fatcity.com>

 two oracle 8174 databases on one aix 4.3.3 server  

database1 = CMS
database2 = HAT      

In database2 (HAT)  

insert a row into table card_status_log on hat  

--The insert is happening directly in database2 from a
--direct sqlplus connect to the hat database.
--no link involved with insert
 

commit;  

successful
no errors

---

 

from database1 (CMS)
--Distributed query run via sqlplus session on CMS:
SELECT
card_id,

       card_status_id,
       cardtype_cd,
       cardstatus_cd,
       card_status_reason_cd,
       status_date,
       user_id

FROM hat_card_status_log (synonym over a link to card_status_log table in database2)
where card_id = 302
UNION
SELECT

       card_id,
       card_status_id,
       cardtype_cd,
       cardstatus_cd,
       card_status_reason_cd,
       status_date,
       user_id

FROM card_outline_status_log (table in database1) where card_id = 302;  

open a sqlplus session from database1 (CMS), query for newly inserted row from
initial insert above  

new row does not show  

rerun query for new row from same sqlplus session in database1  

newly inserted row shows  

--Remote query

running first part of the query (select to database2 without union) shows newly created row on first attempt  

---

work around... add hint, qualify object names:  

SELECT
/*+ DRIVING_SITE(hcsl) */
hcsl.card_id,

       hcsl.card_status_id,
       hcsl.cardtype_cd,
       hcsl.cardstatus_cd,
       hcsl.card_status_reason_cd,
       hcsl.status_date,
       hcsl.user_id

FROM hat_card_status_log hcsl
where hcsl.card_id = 302
UNION
SELECT

       csl.card_id,
       csl.card_status_id,
       csl.cardtype_cd,
       csl.cardstatus_cd,
       csl.card_status_reason_cd,
       csl.status_date,
       csl.user_id

FROM card_outline_status_log csl
where csl.card_id = 302;  

open sqlplus session  

query for newly inserted row from database1 (CMS), shows with initial query      

why doesn't the first distributed query work on initial execution?  

thanks  

sandy, long time lurker

Content-Type: text/plain; name="ReadMe.txt"; charset="us-ascii" Content-Transfer-Encoding: 7bit

The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster_at_fatcity.com for clarification.

------_=_NextPart_000_01C2C320.E258B7B0--
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Purcell, Sandra
  INET: SPurcell_at_temgweb.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Jan 23 2003 - 14:49:08 CST