Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL giving problem over the database link

RE: SQL giving problem over the database link

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 22 Apr 2002 13:13:38 -0800
Message-ID: <F001.0044B909.20020422131338@fatcity.com> 





Ashoke,



sorry, try this:



INSERT INTO FWVERID.fwlot_pn2m_at_TFWVERID.PACING.MEDTRONIC.COM
SELECT FW.FWLOT_PN2M.* FROM FW.FWLOT_PN2M,(

select fw.fwlot.sysid sysid from fw.fwlot where fw.fwlot.appid =
'2000005956')


 WHERE FW.FWLOT_PN2M.fromid  = sysid;




Tom Mercadante


Oracle Certified Professional




-----Original Message-----


Sent: Monday, April 22, 2002 3:20 PM


To: Multiple recipients of list ORACLE-L




Tom,



With this I get the following errors




INSERT INTO FWVERID.fwlot_pn2m_at_TFWVERID.PACING.MEDTRONIC.COM

                               *




ERROR at line 1:


ORA-00913: too many values



Any more suggestions.



Thanks,


Ashoke



-----Original Message-----


Sent: Monday, April 22, 2002 12:31 PM


To: Multiple recipients of list ORACLE-L




Ashoke,



Can you try using an in-line view like this:



INSERT INTO FWVERID.fwlot_pn2m_at_TFWVERID.PACING.MEDTRONIC.COM
SELECT * FROM FW.FWLOT_PN2M,(



select fw.fwlot.sysid sysid from fw.fwlot where fw.fwlot.appid =
'2000005956')


 WHERE FW.FWLOT_PN2M.fromid  = sysid;



Havn't tried this, but it is worth a shot.



good luck!



Tom Mercadante


Oracle Certified Professional




-----Original Message-----


Sent: Monday, April 22, 2002 1:00 PM


To: Multiple recipients of list ORACLE-L




Ashoke - Sympathy, but no firm answers here. I have seen this type of
behavior before. Someone mentioned that the underlying problem is that the
Oracle optimizer doesn't have enough information to make an intelligent
decision in some database link situations. Sometimes I've given up in
frustration and just pulled the entire table to the other side of the link.
I haven't tried this, but I wonder if a hint might help its behavior. 
Dennis Williams


DBA



Lifetouch, Inc.


dwilliams_at_lifetouch.com




-----Original Message-----


Sent: Monday, April 22, 2002 10:48 AM


To: Multiple recipients of list ORACLE-L




Greetings,



Here is the scenario.



We are trying to insert records into a remote table via database link after
selecting the data locally.



This query hangs for ever:




INSERT INTO FWVERID.fwlot_pn2m_at_TFWVERID.PACING.MEDTRONIC.COM
SELECT * FROM FW.FWLOT_PN2M WHERE  FW.FWLOT_PN2M.fromid  =


   (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid =
'2000005956');



select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '2000005956' :
This query returns only one row.



SELECT * FROM FW.FWLOT_PN2M WHERE  FW.FWLOT_PN2M.fromid  = (.....) : This
select returns 15 rows.



If I replace the subquery as follows then it works great :




INSERT INTO FWVERID.fwlot_pn2m_at_TFWVERID.PACING.MEDTRONIC.COM
SELECT * FROM FWLOT_PN2M WHERE fromid =


'00000e31.900fb406.37bc1803.00003074.2026';




I tried to do the same insert locally and it works great :




INSERT INTO fwlot_pn2m_gene


SELECT * FROM FW.FWLOT_PN2M WHERE  FW.FWLOT_PN2M.fromid  =


(select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '2000005956');





For some reason the combination of database link and the subquery in the
select clause is going for full table scan for each occurence of formid. I
am wondering if this is a bug or something against the rule.
WHERE  FW.FWLOT_PN2M.fromid  =


   (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid =
'2000005956');




Any explanation or help is appreciated.



Thanks,


Ashoke

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mandal, Ashoke
  INET: ashoke.k.mandal_at_medtronic.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mandal, Ashoke
  INET: ashoke.k.mandal_at_medtronic.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Mon Apr 22 2002 - 16:13:38 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US