Connect to Oracle

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • Connect to Oracle

      I have some problems if I want to connect to oracle with a service name.

      When I put in the connection fields for oracle
      Host: s0048
      Port: 1521
      Database: B1.s0048.world

      there exists an error. I know that the Database B1.s0048.world isn't an SID. But how can I put a servicename in front of the sid?

      I tried also:
      jdbc:oracle:thin:@(description=(address=(protocol=tcp)(host=s0048)(port=1521))(connect_data=(service_name=B1.s0048.WORLD)))

      because that works with talend open studio but in etl server doesn't work.

      Has anyone an idea to solve?

      Thanks

    • try some other Java-Tool and maybe then you "get" it

      I recommend SquirreL

      BTW my SQL-Server connection looks like this:

      Source Code

      1. <connection name="Navision" type="Sqlserver">
      2. <host>${sqlserver}</host>
      3. <port>1433</port>
      4. <database>141.146.9.91</database>
      5. <user>orcale</user>
      6. <password>guess!</password>
      7. </connection>


      Your Oracle connection should be very similar - other type and port and of course the correct address and credentials.

      then firewall ... ;)

    • My connection in ETL Server looks like

      - <connection name="Oracle_Database" type="Oracle">
      <host>s0048</host>
      <port>1521</port>
      <database>B1.s0048.world</database>
      <user>Oracle</user>
      <password>tl34mq</password>
      </connection>

      The firewall is okay. I can connect with Talend Open Studio and iSQLplus from Oracle to that datase. And when I try to connect to an other oracle database with a SID from Palo ETL Server it works well, too.

      But I have only the problem, that I don't know to connect with a SERVICE NAME instead of the SID

    • All right... puhhh... I solved it!

      I think that the problem was current the dots in the Service Name "B1.s0048.world"
      so I went to the directory for Oracle and I changed the tnsnames.ora for the Service Name in "B1" -> in ETL Server I put for the database also "B1".
      So the connection looks like

      - <connection name="Oracle_Database" type="Oracle">
      <host>s0048</host>
      <port>1521</port>
      <database>B1</database>
      <user>Oracle</user>
      <password>tl34mq</password>
      </connection>

      Now it works well... I think that was a campatibility-problem of Palo

      But Thanks a lot axi for your help

    • Hey guys

      you can also connect to clustered oracle dbs by using <url> tag!

      Source Code

      1. <connection name="A_Connection" type="oracle:thin">
      2. <user>user</user>
      3. <password>password</password>
      4. <url>jdbc:oracle:thin:@(DESCRIPTION =(LOAD_BALANCE =on)
      6. (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = ${database_ip})(PORT = 1522))
      7. )
      8. (CONNECT_DATA = (SERVICE_NAME = servicename))
      9. )
      10. </url>
      11. </connection>
      Display All
      sivgin

    • RE: Connect to Oracle

      Unfortunately, setting up a linked server to Oracle is not as easy as it should be. In fact, you’ll have to install some software on your server to make things work. I’ve put together a short guide on connecting to Oracle from SQL Server. The process doesn’t take very long but there are several hoops to jump through.

      1. Install Oracle Database 10g Client Release 2
      1. Install using the InstantClient option
      2. Install Oracle 10g Release 2 ODAC (64-bit, 32-bit)
      1. Select the Oracle Data Access Components option (not .NET!)
      3. Edit TNSNAMES.ora
      1. <install directory>Product10.2.0ClientnetworkADMINTNSNAMES.ora (i.e. C:OracleProduct10.2.0ClientnetworkADMINTNSNAMES.ora)
      2. There is a very specific format to the network configuration file. Here is an example to get you started (just copy & paste multiple entries if necessary):

      DMDEV =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = FRIENDLYNAME)
      )
      )

      There are a couple of things you need to change:
      * HOST = SERVERNAME. The value SERVERNAME should be changed to reflect the actual address or hostname of the target system.
      * SERVICE_NAME = FRIENDLYNAME. FRIENDLYNAME is the name by which you refer to this actual connection.
      4. REBOOT!
      5. Configure provider in SQL Server
      OraOLEDB.Oracle Provider Menu Item

      OraOLEDB.Oracle Provider Menu Item
      1. Databases→DBName→Server Objects→Linked Servers→Providers→OraOLEDB.Oracle→Properties→Enable “Allow inprocess”

      Enable "Allow inprocess"
      6. Create a linked server to the Oracle Database
      1. General
      1. Linked Server: A name of your choosing which you will use when querying using four-part naming conventions.
      2. Provider: Oracle Provider for OLE DB
      3. Product Name: “Oracle” is fine here
      4. Data Source: This should match the HOST you defined in TNSNAMES.ora

      Create a Linked Server

      Create a Linked Server
      2. Security
      1. Select Be made using this security context and supply the remote login and password
      7. Query the linked server:

      SELECT TOP 10 * FROM LINKEDSERVERNAME..SYS.HELP
      i would also suggest you to visit oracle toolkit where u can get more details related to this.
      Derek Randall