Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: How to connect to an Oracle Service NOT a SID using Pentaho Spoon

  1. #1
    Join Date
    Jan 2012
    Posts
    4

    Default How to connect to an Oracle Service NOT a SID using Pentaho Spoon

    I had a need to connect to an Oracle Service NOT a SID recently. The underlying reason was that the Database was configured as a Cluster and exposed as a Service not a SID. It turns out that Pentaho 4.2.1 does not offer an option to configure this (yet), when making a straight JDBC connection to an Oracle DB, but I did find a work around.There's a subtle difference in the JDBC URL to connect to SID vs. Service:

    SID URL:


    jdbc:oracle:thin:@MY-CLUSTER.myLAN.com:1521:myDB

    Service URL:


    jdbc:oracle:thin:@MY-CLUSTER.myLAN.com:1521/myDBPlease

    note that to connect to an Oracle Service requires a forward slash to replace the colon (shown in red).
    To work around this, when creating a DB connection, in the JDBC settings I simply added a forward slash to the DB name and 'voila' Pentaho Spoon allowed me to connect to the Oracle Service.

    Host Name: MY-CLUSTER.myLAN.com
    Database Name: /myDB

    If you're using JNDI, then you should not have a problem, as your JDBC URL is contained within your JNDI property file and you can configure for SID or Service directly in the URL.

    I hope this helps!
    Last edited by MattCasters; 01-06-2012 at 03:50 PM.

  2. #2
    Join Date
    Jan 2012
    Posts
    4

    Default

    To display the URLs without the HTML editor converting to smilies, here they are using zero for the letter O

    jdbc:0racle:thin:@MY-CLUSTER.myLAN.com:1521:myDB
    jdbc:0racle:thin:@MY-CLUSTER.myLAN.com:1521/myDB

  3. #3
    Join Date
    Sep 2011
    Posts
    12

    Default

    I have a similar issue here, we need to setup a connection able to use both methods, then I can't use the / as hardcode, but if I use the / in my parameter the connection not understand as a service and mount a url like hostort:/service_name. Do you have any idea for help me?

    thanks
    Gibon

  4. #4
    Join Date
    Nov 2014
    Posts
    1

    Default

    Forward slash in front of the database name within the connection prompt works for this type of connection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.