Hello,
I am trying to connect a Oracle DB to FME but even if I did everything from this link it is not working.
Does anybody have any idea what could be the problem?
Question
Oracle db connection to FME
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+2What version of FME and is it 32 or 64 bit? You need to match the Oracle Client as 32 or 64 bit. It is possible to have both.
One thing I like to do is to use SQLPlus to check the connection to Oracle - If SQLPlus can't connect FME probably can't.
Wondering also if the Service name allows an IP - Is you TNS Names configured correctly - the suggestion above will prove that.
Also when you click the Test button it should return an error - what does it say as the reason for failure? You can also Show Log which you can paste here.
What version of FME and is it 32 or 64 bit? You need to match the Oracle Client as 32 or 64 bit. It is possible to have both.
One thing I like to do is to use SQLPlus to check the connection to Oracle - If SQLPlus can't connect FME probably can't.
Wondering also if the Service name allows an IP - Is you TNS Names configured correctly - the suggestion above will prove that.
Also when you click the Test button it should return an error - what does it say as the reason for failure? You can also Show Log which you can paste here.
As it can be seen FME is x64 and also Instant Client is. I can access this data base from SQL developer and it is not a problem but I am trying to find a way how to write some results from FME to this base that already exists. I don't know what could be a problem in FME so it doesn't want to let me to connect db and FME.
+2
As it can be seen FME is x64 and also Instant Client is. I can access this data base from SQL developer and it is not a problem but I am trying to find a way how to write some results from FME to this base that already exists. I don't know what could be a problem in FME so it doesn't want to let me to connect db and FME.
Can you post the SQLDeveloper connection details? Is it using TNS or Basic connection?
+2OK I think the problem is in the Environment Variables.
The PATH variable needs the directory where the Oracle Client is installed. You do not need the InstantClient variable and certainly not pointing to oci.dll
Is my setting - yours will be in a different directory.
Can you post the SQLDeveloper connection details? Is it using TNS or Basic connection?
+2
OK I think the problem is in the Environment Variables.
The PATH variable needs the directory where the Oracle Client is installed. You do not need the InstantClient variable and certainly not pointing to oci.dll
Is my setting - yours will be in a different directory.
Failed to read schema features from dataset '147.91.175.146' using the 'ORACLE_NONSPATIAL' reader
Now I get this error. I changed value as in Your picture.
+2Try setting up the connection like this image (EasyConnect) where the conenction details are all on one line:
See this link:
https://knowledge.safe.com/articles/827/connect-to-an-oracle-service-name-not-oracle-sid.html
This is because it seems TNS Names is not configured or readable.
Another good resource:
https://knowledge.safe.com/articles/28996/fme-and-oracle-troubleshooting-guide.html
+2UniversalReader -- readSchema resulted in 0 schema features being returned
Error connecting to Oracle database: message was `ORA-12514: TNS:listener does not currently know of service requested in connect descriptor'. (serverType=`ORACLE_NONSPATIAL', serverName=`147.91.175.146', userName=`DRAGICFME', password=`***', dbname=`')
Failed to read schema features from dataset '147.91.175.146' using the 'ORACLE_NONSPATIAL' reader
Now I get this error. I changed value as in Your picture.
See this link:
https://knowledge.safe.com/articles/827/connect-to-an-oracle-service-name-not-oracle-sid.html
This is because it seems TNS Names is not configured or readable.
Another good resource:
https://knowledge.safe.com/articles/28996/fme-and-oracle-troubleshooting-guide.html
It is not working again. Damn.
Here is log file but I have not idea what is problem and why it doesn't want to connect but this is really important for me.
@mark_1spatial
+2Try setting up the connection like this image (EasyConnect) where the conenction details are all on one line:
See this link:
https://knowledge.safe.com/articles/827/connect-to-an-oracle-service-name-not-oracle-sid.html
This is because it seems TNS Names is not configured or readable.
Another good resource:
https://knowledge.safe.com/articles/28996/fme-and-oracle-troubleshooting-guide.html
The problem looks like Oracle TNS Names configuration - which is not FME, but you can work around TNS using EasyConnect.
An explanation of TNS Names
http://www.orafaq.com/wiki/Tnsnames.ora
+2- Contributor
You're Oracle connection is missing an sid or servicename. This identifies the database instance that's is running on you're Oracle server. Out of the box Oracle uses orcl as the instance name. DBA's change this name to an name that's identifies the use of the database. On Oracle server can host multiple database connecting through different port numbers. Standard portnumber is 1521.
You're connection to oracle has only an IP address. That's not an valid connection.
When using TNSNames you give an Oracle connection an name (Alias). This cannot be an IP address. So I assume you are trying to connect to Oracle via an Easy Connect.
The richt syntax for Easy Connect is.
<servername or IP Address>:<portnr>/<service_name>
You can test if the Easy Connect is valid inside an command box (cmd.exe).
For example:
IP: 147.91.175.146
port: 1521
service_name: orcl
tnsping 147.91.176.146:1521/orcl
I think the connection will be woring in FME when you are using the bold text as mentioned above after TNSPING.
P.S.
You should consider not to mention the server name of Ip address of your Oracle connection on any (web)site. I could reach your Oracle server (ping and tnsping) so I guess everyone in the world can reach you're server. This could be an security risk to you're data/organisation.
You're Oracle connection is missing an sid or servicename. This identifies the database instance that's is running on you're Oracle server. Out of the box Oracle uses orcl as the instance name. DBA's change this name to an name that's identifies the use of the database. On Oracle server can host multiple database connecting through different port numbers. Standard portnumber is 1521.
You're connection to oracle has only an IP address. That's not an valid connection.
When using TNSNames you give an Oracle connection an name (Alias). This cannot be an IP address. So I assume you are trying to connect to Oracle via an Easy Connect.
The richt syntax for Easy Connect is.
<servername or IP Address>:<portnr>/<service_name>
You can test if the Easy Connect is valid inside an command box (cmd.exe).
For example:
IP: 147.91.175.146
port: 1521
service_name: orcl
tnsping 147.91.176.146:1521/orcl
I think the connection will be woring in FME when you are using the bold text as mentioned above after TNSPING.
P.S.
You should consider not to mention the server name of Ip address of your Oracle connection on any (web)site. I could reach your Oracle server (ping and tnsping) so I guess everyone in the world can reach you're server. This could be an security risk to you're data/organisation.
log2.txt
+2- Contributor
Can I get any help please?
Have you tried connecting to SQL*Plus?
Can you verify the Oracle server is still running?
E.g. ping 147.91.175.146
You get an timeout on the connection to Oracle. This looks like the server self is not reachable.
Hi @maliodpalube,
Have you tried connecting to SQL*Plus?
Can you verify the Oracle server is still running?
E.g. ping 147.91.175.146
You get an timeout on the connection to Oracle. This looks like the server self is not reachable.
+2Where can I find SQL Plus? I have SQL Developer and it works in there all the time. I don't know why it doesn't work in FME???
SQLPlus is command line utility. SQLDeveloper doesn't need an Oracle Client to connect to a database but FME does. Do you have an Oracle DBA to help you configure your environment because you must have everything configured correctly to allow it to work.