Open Query to Oracle

Question

Post reply

Open Query to Oracle

thbaig

Hall of Fame

Points: 3006
More actions
August 5, 2014 at 6:15 am

#145210

Hi,
I have used select,insert and delete in open query(from mssql to oracle) , but I don't how can I transform following oracle sql to my openquery call.
SELECT * FROM OPENQUERY (linkOra, 'insert into Old_item
(
PRODID,
SIZECODE
)
select
PRODID,
SIZECODE
from new_ITEM')

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

If both tables (Old_item and new_ITEM) are located at Oracle you could use

Execute ('insert into Old_item

(

PRODID,

SIZECODE

)

select

PRODID,

SIZECODE

from new_ITEM') AT linkOra

If Old_item is located at Oracle (similar for new_ITEM):

insert into linkOra..Old_item

(

PRODID,

SIZECODE

)

select

PRODID,

SIZECODE

from new_ITEM

Note: you might noeed to provide the fully qualified name for the Oracle table...

If there are any error messages, please post back.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

thbaig Hall of Fame Points: 3006 More actions · Answer 2

Thank you Lutz.

RPC with Execute worked.

second option you have mentioned is use to insert records in Oracle from local MSSQL object ?

LutzM SSC Guru Points: 107049 More actions · Answer 3

Yes, the 2nd option is used when there's a need to send data from a SQL source to an Oracle target.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

thbaig Hall of Fame Points: 3006 More actions · Answer 4

LutzM (8/6/2014)
Yes, the 2nd option is used when there's a need to send data from a SQL source to an Oracle target.

I have used 2nd option but having following error. I am not using that column "SCOLOR" to insert ,also this column allowed to insert null.

OLE DB provider "MSDAORA" for linked server "linkOra" returned message "Multiple-step operation generated errors. Check each status value.".

Msg 7344, Level 16, State 1, Line 1

The OLE DB provider "MSDAORA" for linked server "linkOra" could not INSERT INTO table "[linkOra]..[OraUser].[NEW_ITEM]" because of column "SCOLOR". The column used the default value.

LutzM SSC Guru Points: 107049 More actions · Answer 5

No I'ver reached the point where my very limited Oracle knowledge ends...

Maybe Oracle requires values for each column. Don't know.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

thbaig Hall of Fame Points: 3006 More actions · Answer 6

LutzM (8/6/2014)
No I'ver reached the point where my very limited Oracle knowledge ends...
Maybe Oracle requires values for each column. Don't know.

Thank you Lutz.

I will take care of oracle stuff, just want to know if there is anything left on MSSOL side 🙂

Thanks again for wonderful help