Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Unanswered: Reading MS SQL Server Tables In Oracle...

    Hi,

    I want to update or insert a record into oracle from MS SQL Server database. I need to read the record from SQL Server and apply few transformation and insert or update the oracle table.

    I plan to implement this in the oracle SP. Is it possible? If yes how do i connect to the SQL Server from oracle SP.

    Appericiate your help.

    T & R,
    Amit
    A friend in need is a friend in deed

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    This is definitely possible. Have done it myself before. You are looking for a feature called Heterogenous connections.

    Oracle allows you to connect to various other db's using Oracle dblink's. Your dba will setup a heterogenous connection which your dblink will use. Your PL/SQL will communicate with the remote db using the dblink. Most of the time you're code will not know that it is actually communicating with an MS Sql database on the other end.

    Some resources:
    http://www.ooug.org/2002slides/oracl...usservices.pdf
    http://www.csee.umbc.edu/help/oracle...784/hs_ch6.htm

  3. #3
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    Hi Dayneo,

    Thank you so much for that.
    A friend in need is a friend in deed

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Here is one that I have used as a step-by-step setup guide.
    Attached Files Attached Files

  5. #5
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    Thank you my friend, you have made my day. Thanks again
    A friend in need is a friend in deed

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    @dayneo, PDF document you have attached is only partially readable - right margin text is missing (4-5 characters, my guess). Although it is better to have any kind of help than no help at all, could you, please, find / make the complete document and attach it again?

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    I was under the impression that using this Transparent Gateway feature, you're looking at some hefty licensing fees. Look under "Licensing Gateways":

    http://www.oracle.com/technology/pro...ays/index.html

    I can't remember how it was spec'ed out for us, but the price tag was something like $25K. It was over 2 years ago when we looked into it.

    Are Heterogeneous Services different, or just the former title for "Transparent Gateways"

    ---=Chuck
    Last edited by chuck_forbes; 05-01-07 at 19:10.

  8. #8
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    I am not sure about licensing. That is not my department. That's my DBA's problem. But I am sure that spending on licensing will be cheaper than your overall equivalent development attempts at the same. Software is usually like that.

    The PDF.... sorry, thats the only way I have it. Maybe try taking phrases from the doc and do a google search. Should find it that way.

    I think "Transparent Gateways" are a subset of Heterogeneous services or vica versa.

    The only gateway I have had trouble with is from Oracle 10.1.02 to MySQL 5.0 where date conversions never seemed to work properly. All attempts to set the date format seemed to fail, but perhaps that was just me.

    Oracle provides excellent compatibility with MSSqlServer though. There is a gateway service specifically for it. I beleive they can even do some sort of replication with the db. I think I saw that in passing (so unconfirmed).

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    @dayneo: Oh, I'm sorry - I thought you are the author of the document.

  10. #10
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    The contents of the PDF can be found @ http://www.orafusion.com/art_orahs.htm
    A friend in need is a friend in deed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •