I'm trying to connect to an Oracle databse with PDI. I've successfully connect to the database with Oracle SQL Developer and sqlplus using TNS. The connection string I used in sqlplus is just something like username/password@tnsname.
I followed the steps here to connect to the same database in PDI, but got the error "ORA-12504:TNS:listener was not given the SERVICE_NAME in CONNECT_DATA".
The item in my tnsnames.ora looks something like this:
sample_TNS.world=
(DESCRIPTION=
(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=off)(FAILOVER=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=host1.com)(PORT=38000))
(ADDRESS=(PROTOCOL=TCP)(HOST=host2.com)(PORT=38000))
(ADDRESS=(PROTOCOL=TCP)(HOST=host3.com)(PORT=38000)))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TEST.COM)))
Unlike most examples, it has got multiple ADDRESSes, so I just picked one of the HOSTS to fill into the "Host Name" field in PDI and copied the entire thing following the equals sign into the "Database Name" field. I also tried to not include every (ADDRESS=xxx) in the "Database Name" field, but only the one I used in the "Host Name" field (everything else was kept intact).
I've googled the error and some suggests it could be that the (Description=xxxx) string not correctly formed. However, I've check it multiple times that I'm pasting it correctly, no typoes and the brackets do match (and I was able to make the connection with SQL developer and sqlplus so the TNS string should be correctly formed).
I've also made a simple Oracle database on the local machine and successfully connected to it with PDI (not using TNS names of course), so I don't think it's the problem with the drivers.
Some other methods I've tried including using "Generic database" as connection type instead of "Oracle" as mentioned here (but I failed to get my head around the 2nd and 3rd method mentioned by shassan2) as well as leaving host and port blank and filling the DatabaseName with the service description as mentioned here. I tried to adjust things here and there but just kept getting all kinds of error, including the same ORA-12504,TNS:listener does not currently know of service requested in connect descriptor, No suitable driver found, IO Error: The Network Adapter could not establish the connection, etc (too many that I've lost my count of the errors). The links in those "aged" forum posts are all down at the moment, so it will be extremely helpful if anyone can show me the right way to make the connection. I'm using 64-bit Windows, Pentaho Community Edition 9.3.0.0-428
openjdk version "1.8.0_352"
OpenJDK Runtime Environment (build 1.8.0_352-b08)
OpenJDK 64-Bit Server VM (build 25.352-b08, mixed mode)
and put the driver ojdbc8.jar under
data-integration/lib/
Any help would be much appreciated. Thank you!
UPDATE-I tried Wireshark and that's what I got:
Attempt 1: leave host and port empty, and put that DESCRIPTION bulk in the Database Name field, as suggested by Ana GH
"3DU<zE2Í%@M
¬Gô
Rípÿ[TNrÞPì
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.xx.xxx.xxx)(PORT=38000)))(CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=myusername))(SERVER=DEDICATED)(SERVICE_NAME=TEST.COM)(CONNECTION_ID=xxxxxxxxxxxxx)))
Attempt 2: Picked one of the HOSTS to fill into the "Host Name" field and copied the entire thing following the equals sign into the "Database Name" field as suggested in the first link in this question body.
"3DU<zEöÍ·@K»
¬Gô
Ró»pùO0çÜP…@Î(DESCRIPTION=(CONNECT_DATA=(SID=(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)(ADDRESS_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP)(HOST=host1.com)(PORT=38000)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TEST.COM))))(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=myusername))(CONNECTION_ID=xxxxxxxxxxxxxxx))(ADDRESS=(PROTOCOL=TCP)(HOST=10.xx.xxx.xxx)(PORT=38000)))
I've checked that the HOST (both domain and ip form) is correct and the HOST do match with what I got from tnsnames.ora.
Can you let me know the Oracle JDBC driver version that you are using? Based on the JDBC version, you can pass the TNS Alias as part of the connection string. Check out the blog for more details.