Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    3

    Unanswered: connecting SQL Server with oracle forms 9i/10g

    Could anyone please guide me in detail to connect Oracle Forms 9i/10g with MS SQL Server database?
    To connect with Oracle Database, we need to copy and paste the tnsnames.ora file from the Oracle Database folder to the Oracle Forms folder.
    But how do i connect with other databases like SQL Server, MS Access, and so on?
    Thanks in advance.

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Wink SQL server

    From SQL server To Oracle
    You have the security folder, see :
    Console ROOT -> MS SQL SERVER -> servergroup -> (local) - > security - > linked server(right click here to add new linked server)
    here you can connect to Oracle

    if you want to query the oracle trough Sql.serv you have to use and openquery

    so say you have an person table in Oracle and in the linked server you've called the linked server OrcConn your sql will look like this

    An SELECT example:
    SELECT * FROM OPENQUERY (OrcConn,
    'SELECT Id, Name , age
    From Person
    where Name like ''Richard'' and id = 12')

    An update Update example:

    Update OPENQUERY (OrcConn,
    'SELECT Id, Name , age
    From Person
    where Name like ''Richard'' and id = 12')
    SET age =34



    For a JOIN from SQL.Person to ORACLE.Person you do:

    SELECT *
    FROM OPENQUERY (OrcConn,
    'SELECT Id, Name , age
    From Person
    where id = 12') as ORCPERSON
    LEFT JOIN Person ON ORCPERSON.id = Person.Id
    WHERE Person.Deleted = false

    This will be "a bit slow" because you'll first query the oracle to fetch person then get it to your local level and requery the result to inner join
    Last edited by Marvels; 12-07-07 at 05:00.

  3. #3
    Join Date
    Dec 2007
    Posts
    3

    Re:

    Thanks for the reply.

    I'm afraid you've replied not for my question.

    Inorder to connect the Oracle Forms Builder we need to connect to a database. So if we want to connect to a MS SQL Server database, what's the procedure to do it?

    I think now my question is bit more specific.

    Thanks.


    Quote Originally Posted by Marvels
    From SQL server To Oracle
    You have the security folder, see :
    Console ROOT -> MS SQL SERVER -> servergroup -> (local) - > security - > linked server(right click here to add new linked server)
    here you can connect to Oracle

    if you want to query the oracle trough Sql.serv you have to use and openquery

    so say you have an person table in Oracle and in the linked server you've called the linked server OrcConn your sql will look like this

    An SELECT example:
    SELECT * FROM OPENQUERY (OrcConn,
    'SELECT Id, Name , age
    From Person
    where Name like ''Richard'' and id = 12')

    An update Update example:

    Update OPENQUERY (OrcConn,
    'SELECT Id, Name , age
    From Person
    where Name like ''Richard'' and id = 12')
    SET age =34



    For a JOIN from SQL.Person to ORACLE.Person you do:

    SELECT *
    FROM OPENQUERY (OrcConn,
    'SELECT Id, Name , age
    From Person
    where id = 12') as ORCPERSON
    LEFT JOIN Person ON ORCPERSON.id = Person.Id
    WHERE Person.Deleted = false

    This will be "a bit slow" because you'll first query the oracle to fetch person then get it to your local level and requery the result to inner join

  4. #4
    Join Date
    Dec 2006
    Posts
    30
    Perhaps this is better asked in the Oracle forum.

Posting Permissions

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