How to Insert / Update to Oracle via Linked Server from SQL2005 - with commit?

  • Hello,

    I have an Oracle table, where I have to insert / update records. It all works using the Oracle client on the IIS server.

    Now, we want to remove this client and the necessity of installing it and created a linked server to Oracle on SQL 2005.

    Select is achieved like this:

    SELECT * from OPENQUERY(linkedservername,'select fields from table where field = ''value'' order by field') - the compare values must be put between double apostrophs.

    This is working - and faster then the direct Oracle select.

    Now, I would like to check for a record. If the record exists, I have to update it - else it has to be inserted. The problem in Oracle (for me) is that you have to use COMMIT; at the end - which I do not know how to put into my query.

    Here is the principle structure (using a direct Oracle connection):

    DECLARE l_num_count NUMBER := 0;

    BEGIN

    SELECT COUNT(*) into l_num_count FROM table WHERE field = ''value'';

    IF l_num_count = 0 THEN

    INSERT ...;

    ELSE

    UPDATE...;

    END IF;

    COMMIT;

    END;

    In principle, I know the syntax for INSERT and UPDATE - but how to add the rest and the COMMIT;?

    INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles') VALUES ('NewTitle');

    UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101') SET name = 'ADifferentName';

  • I would rely in distributed transactions, here is how.

    On the SQL Server side initiate a distributed transaction by issuing BEGIN DISTRIBUTED TRAN

    Do something commitable to a SQL Server table -even if your business specs do not ask for it.

    Do whaterver you have to do against Oracle tables

    Commit the distributed transaction by issuing COMMIT TRAN statement.

    😉

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • The Oracle Commit; is necessary to write data permanently to the tables - it has nothing to do with a transaction in SQL ;). So in Oracle you need it for any INSERT or UPDATE, even for one record.

    So I still need the syntax for what I have to do: INSERT + COMMIT or UPDATE + COMMIT. Or - even better - a replacement for my mentioned structure.

  • birgit.schelloeh (1/29/2009)


    The Oracle Commit; is necessary to write data permanently to the tables - it has nothing to do with a transaction in SQL ;). So in Oracle you need it for any INSERT or UPDATE, even for one record.

    So I still need the syntax for what I have to do: INSERT + COMMIT or UPDATE + COMMIT. Or - even better - a replacement for my mentioned structure.

    :w00t: Did you miss the part that says "DISTRIBUTED TRANSACTION" on my post?

    When you set a distributed transaction and issue a commit, commit happens on all RDBMS involved in the distro e.g.: SQL Server and Oracle in your case 😉

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • I do not know about distributed transactions, how to set them up, etc. I simply call the whole thing in .NET via direct SQL. What I could do is call a SP instead. Would that help together with the linked server?

  • You code should look like...

    BEGIN DISTRIBUTED TRAN

    Do your Oracle stuff

    Do any commitable SQL stuff -even if your business specs do not ask for it.

    COMMIT TRAN

    You can read about distributed transactions on Books Online, it's free!

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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

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