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: Mandal, Ashoke <ashoke.k.mandal_at_medtronic.com>
Date: Mon, 22 Apr 2002 11:19:55 -0800
Message-ID: <F001.0044B615.20020422111955@fatcity.com>


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).
Received on Mon Apr 22 2002 - 14:19:55 CDT

Original text of this message

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