Question

Oracle db connection to FME


Badge

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?


21 replies

Badge +2

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.

Badge

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.

 

Badge +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?
Badge +2
It can't find your Oracle Client - did you use the Oracle Installer or a manual configuration?

 

 

Badge +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.

Badge

 

Can you post the SQLDeveloper connection details? Is it using TNS or Basic connection?

It is basic with SID but I don't get it why FME is not accepting it.

 

 

 

Badge +2

It is basic with SID but I don't get it why FME is not accepting it.

 

 

 

As posted above - from your original screen captures - it looks like the client isn't installed correctly.

 

 

Badge

@mark_1spatial Where can You see that? Which client is not installed correctly?

Badge

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.

UniversalReader -- 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.

 

Badge +2
Try 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
Badge +2
UniversalReader -- 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.

 

Try 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
Badge

It is not working again. Damn.

Badge

It is not working again. Damn.

log.txt

 

 

 

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

 

Badge +2
Try 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
@maliodpalube did you try the EasyConnect suggested above?

 

 

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

 

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.

Badge

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.

This the error that I receive after I use Your tips.

 

 

 

log2.txt

 

Badge

Can I get any help please?

Can I get any help please?

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.

 

 

Badge
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.

 

 

Where 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???

 

Badge +2
Where 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.

 

 

Badge
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.

 

 

Is there any way that I can contact You outside the forum to ask all the problems that I am facing in connection between FME and Oracle DB?

 

Reply