Post reply

Open Query to Oracle

  • 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')

  • August 6, 2014 at 2:34 am

    #1735525

    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]

  • August 6, 2014 at 5:46 am

    #1735582

    Thank you Lutz.

    RPC with Execute worked.

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

  • August 6, 2014 at 5:48 am

    #1735583

    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]

  • August 6, 2014 at 6:06 am

    #1735600

    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.

  • August 6, 2014 at 6:11 am

    #1735604

    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]

  • August 6, 2014 at 6:24 am

    #1735610

    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

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

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