none
How to insert data to Oracle table from SQL using linked server?

    Question

  • I want to insert data to Oracle 11g table from SQL Server 2008 R2. I have installed Oracle 11g client in server machine.

    I have created a linked server and the connection was successful. I have also enabled Allow in process for OraOLeDb.Oracle in the providers.

    If I try to retrieve the data using select query as:

    SELECT * FROM TEST_AMT..PS_I2L_FAR_AMT_DET

    Here TEST_AMT - linked server name, PS_I2L_FAR_AMT_DET - Oracle table name

    it returns an error.

    But if I use OPENQUERY it is fine. SELECT * FROM OPENQUERY("TEST_AMT", SELECT * FROM PS_I2L_FAR_AMT_DET)

    But now if I'm facing a problem while inserting data to oracle table.

    INSERT OPENQUERY (TEST_AMT, 'SELECT ITEMID FROM PS_I2L_FAR_AMT_DET') VALUES('1002');

    Here: ITEMID- column name of oracle table

    OR

    INSERT  OPENQUERY(TEST_AMT,'SELECT ITEMID FROM PS_I2L_FAR_AMT_DET') SELECT ItemID FROM Item;

    Here: ItemID - SQL column name, Item - SQL table name


    The error is:

    OLE DB provider "OraOLEDB.Oracle" for linked server "TEST_AMT" returned message "".
    OLE DB provider "OraOLEDB.Oracle" for linked server "TEST_AMT" returned message "ROW-00054: Cannot load the library
    O/S-Error: (OS 126) The specified module could not be found.".
    Msg 7320, Level 16, State 2, Line 1
    Cannot execute the query "SELECT ITEMID FROM PS_I2L_FAR_AMT_DET" against OLE DB provider "OraOLEDB.Oracle" for linked server "TEST_AMT".

    Is the error due to some permission rights from Oracle side or some permissions from SQL side? So is there any other way to insert data to oracle table? Please help.

    Monday, December 03, 2012 6:19 AM

All replies

  • Hello Sanketh ,

       Not exactly sure about this error. But could be one of the below reason.

       1. If there are more columns in the PS_I2L_FAR_AMT_DET table and if they have some contraint on them , error may occur.

       2. You shoud have neccesary permissions to do this operation. Meaning your login or your login configured in the linked server should have proper permissions.

       3. May be you can consider enabling RPC in and Out option in the linked server.

       4. Make sure to have Microsoft Distributor Coorodinator service up and running.

    Hope this helps.


    Best Regards Sorna

    Monday, December 03, 2012 6:35 AM
  • Hi,

    First know link server creation between two database and then use openquery() to fetch data from Oracle database.

    http://msdn.microsoft.com/en-us/library/ms188279(v=sql.105).aspx

     


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Monday, December 03, 2012 7:27 AM
  • Can you try with the fully qualified name, that could be a problem 

    SELECT * FROM TEST_AMT.<<Schema>>.PS_I2L_FAR_AMT_DET

    Regards
    Satheesh

    Monday, December 03, 2012 7:32 AM
  • Hi,

    You can do this(oracle to SQL server or SQL to oracle) easily in integration Services (SSIS).


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, December 03, 2012 11:12 AM
  • Hi Sanketh,

    You should be better of using SSIS for this task as Loading data using LinkServer is slower as compaired to SSIS.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by AniqaSQL Monday, December 03, 2012 12:24 PM
    Monday, December 03, 2012 12:23 PM
  • Thanks for the reply Sorna.

    I have considered all the points you mentioned and still I'm facing the same error. There were constraints like all the fields in the oracle table cannot be left blank, values need to be provided. And i have all the permissions for the Oracle table. I've set RPC option to true. There are 6 columns in oracle table.

    I tried another query by which i was successful in inserting the data to oracle table.

    EXEC('INSERT INTO PS_I2L_FAR_AMT_DET(OraColName1, OraColName2, OraColName3, OraColName4, OraColName5, OraColName6)
            VALUES (?,?,?,?,?,?)','0123','123','123','123','0123','12345') AT TEST_AMT

    But if i modify the above query and use a select statement as below, then it returns an error.


    EXEC('INSERT INTO PS_I2L_FAR_AMT_DET(OraColName1, OraColName2, OraColName3, OraColName4, OraColName5, OraColName6)
    (SELECT SqlColName1, SqlColName2, SqlColName3, SqlColName4, SqlColName5, SqlColName6 FROM SqlTable)') AT TEST_AMT

    Error:

    OLE DB provider "OraOLEDB.Oracle" for linked server "TEST_AMT" returned message "ORA-00942: table or view does not exist".
    Msg 7215, Level 17, State 1, Line 1
    Could not execute statement on remote server 'TEST_AMT'.


    So what may be the problem for this error??

    Regards,

    Sanketh


    Tuesday, December 04, 2012 5:00 AM
  • I assume Test_AMT is the Oracle Server. in that case when you run the statement on Oracle server, it wont find the SqlTable, I believe.

    Can you try running this directly on the sql server?

    INSERT INTO <<LinkedServer.Database.Schema.>>PS_I2L_FAR_AMT_DET
      (OraColName1, OraColName2, OraColName3, OraColName4, OraColName5, OraColName6)
    (SELECT 
       SqlColName1, SqlColName2, SqlColName3, SqlColName4, SqlColName5, SqlColName6 FROM SqlTable) 



    Regards
    Satheesh



    Tuesday, December 04, 2012 5:24 AM
  • Do you have this "sqlTable" table on your oracle server?
    Tuesday, December 04, 2012 7:46 AM
  • Hi Satheesh,

    The query want you have given works in SQL Server 2005, but not in 2008 R2. Because in SQL Server 2005, there is MSDAORA providers, in the list of providers. But in SQL Server 2008, this provider will not appear.

    I also tried to export data to oracle using Export Wizard.

    If i try to export data using Export wizard in 2005, and click Test Connection, connection is fine, since i choose my destination as Oracle provider for OLEDB. But when i try to do this in SQL Server 2008 R2, i don't have this provider in my list. Hence i cannot create the connection

    In SQL Server 2005 direct Select query will work. There is no need for Openquery.

    So what may be the solution to my problem?

    I'm running the query from SQL server.


    Wednesday, December 05, 2012 5:04 AM