Solved

SHP to Oracle Database

M
Badge

Hello,

 

I have result from FME, one shape file with many attributes that need to be written in one DB in Oracle that I have access to. I don't know how to connect that DB in FME and how to export attributes from this shp to that data base. Is there any way to do it?
icon

Best answer by maliodpalube 1 February 2018, 09:58

View original

21 replies

oscard
Rank
Userlevel 2
Badge +19

Hi.

Add a SHAPE Reader.

Then add the Oracle Spatial Object Writer:

Choose the option: "Copy from Reader"

In the "Dataset" parameter create a New connection:

Last, join the Reader with the Writer.

That should do the trick.

M
Badge

Hi.

Add a SHAPE Reader.

Then add the Oracle Spatial Object Writer:

Choose the option: "Copy from Reader"

In the "Dataset" parameter create a New connection:

Last, join the Reader with the Writer.

That should do the trick.

 

It is not working, I am getting this error and I don't know how to solve it.

 

 

 

 

oscard
Rank
Userlevel 2
Badge +19

 

It is not working, I am getting this error and I don't know how to solve it.

 

 

 

 

I can't see the image you have attached.

 

 

G
Badge +3

If you are writing to an existing database, you may need to verify constraints etc.

Often linked table via primary- foreign key force you to do more work. (not wise to temporarily disengage them...a common mistake)

You can read these constraints etc. using an sql statement using a sql-executor. Then pass the values to transformers to prepare data writing. As to make sure you respect the constraints etc.

Maybe your projection is wrong?

But, yes, reload your image so we can see the error, or copy the error and paste it. (not as big as the gdb corrupting question I hope...;)

M
Badge

If you are writing to an existing database, you may need to verify constraints etc.

Often linked table via primary- foreign key force you to do more work. (not wise to temporarily disengage them...a common mistake)

You can read these constraints etc. using an sql statement using a sql-executor. Then pass the values to transformers to prepare data writing. As to make sure you respect the constraints etc.

Maybe your projection is wrong?

But, yes, reload your image so we can see the error, or copy the error and paste it. (not as big as the gdb corrupting question I hope...;)

 

Projection is good. I am trying to connect the base, for beginning. There are some foreign keys but they need to be kept and some that were not needed they are closed in SQL Developer. I can't connect my base with FME, that is my problem and I have no idea how to solve it and I've tried everything that could be a problem.

 

M
Badge

Is there any way to get some feedback about this problem?

 

I have done all from this link but it is not working. I don't know why am I always getting an error when I did everything from the article and still I am not being able to connect my base and FME.
ciarab
Rank
Badge +9

Is there any way to get some feedback about this problem?

 

I have done all from this link but it is not working. I don't know why am I always getting an error when I did everything from the article and still I am not being able to connect my base and FME.
What is your error message?

 

 

M
Badge
What is your error message?

 

 

 

Error connecting to Oracle database: message was `ORA-12170: TNS:Connect timeout occurred'. (serverType=`ORACLE_NONSPATIAL', serverName=`147.91.176.146:1521/orcl', userName=`DRAGICFME', password=`***', dbname=`') Failed to read schema features from dataset '147.91.176.146:1521/orcl' using the 'ORACLE_NONSPATIAL' reader

 

The dataset '147.91.176.146:1521/orcl' was closed successfully

 

The 'ORACLE_NONSPATIAL' reader was destroyed successfully

 

That is it. I have tried everything and I have added infos to the tns names but still nothing.

ciarab
Rank
Badge +9

 

Error connecting to Oracle database: message was `ORA-12170: TNS:Connect timeout occurred'. (serverType=`ORACLE_NONSPATIAL', serverName=`147.91.176.146:1521/orcl', userName=`DRAGICFME', password=`***', dbname=`') Failed to read schema features from dataset '147.91.176.146:1521/orcl' using the 'ORACLE_NONSPATIAL' reader

 

The dataset '147.91.176.146:1521/orcl' was closed successfully

 

The 'ORACLE_NONSPATIAL' reader was destroyed successfully

 

That is it. I have tried everything and I have added infos to the tns names but still nothing.

Can you connect to the Database using SQL Developer or SQL*Plus?

 

 

M
Badge
Can you connect to the Database using SQL Developer or SQL*Plus?

 

 

Yes, I am using this data base regularly with SQL Developer. That is the reason why I asked for help because it is obvious that I am making a mistake somewhere and I can't identify it.

 

 

david_r
Rank
Userlevel 4

Is there any way to get some feedback about this problem?

 

I have done all from this link but it is not working. I don't know why am I always getting an error when I did everything from the article and still I am not being able to connect my base and FME.
Not sure if this has been answered elsewhere in this thread, but try the following:

 

  • Download and install the latest Oracle instant client. Important: if you're running 32-bit FME you have to install the 32-bit instant client. If you're running 64-bit FME your need the 64-bit instant client.
  • Modify the system environment variable PATH and add the complete path to your instant client directory at the very beginning.
  • Restart the computer
If it still doesn't work, let us know.
M
Badge
Not sure if this has been answered elsewhere in this thread, but try the following:

 

  • Download and install the latest Oracle instant client. Important: if you're running 32-bit FME you have to install the 32-bit instant client. If you're running 64-bit FME your need the 64-bit instant client.
  • Modify the system environment variable PATH and add the complete path to your instant client directory at the very beginning.
  • Restart the computer
If it still doesn't work, let us know.
I did all those things that You said and I am still getting the same problem. I have tried everything that I read that could be the problem.

 

 

david_r
Rank
Userlevel 4
I did all those things that You said and I am still getting the same problem. I have tried everything that I read that could be the problem.

 

 

Sounds like you should consider contacting either your FME reseller or Safe support for a more one-on-one support session.
M
Badge
Sounds like you should consider contacting either your FME reseller or Safe support for a more one-on-one support session.
How can I contact Safe Support since I have students license? Thank You once more.

 

 

david_r
Rank
Userlevel 4
How can I contact Safe Support since I have students license? Thank You once more.

 

 

Try this link: https://www.safe.com/support/report-a-problem/
david_r
Rank
Userlevel 4
How can I contact Safe Support since I have students license? Thank You once more.

 

 

By the way, make sure you reference this thread when you contact Safe. That way they'll get a head start.
M
Badge
Not sure if this has been answered elsewhere in this thread, but try the following:

 

  • Download and install the latest Oracle instant client. Important: if you're running 32-bit FME you have to install the 32-bit instant client. If you're running 64-bit FME your need the 64-bit instant client.
  • Modify the system environment variable PATH and add the complete path to your instant client directory at the very beginning.
  • Restart the computer
If it still doesn't work, let us know.
Thank You a lot. I did reference this thread so it was easier to explain my problem. Greetings

 

 

fmelizard
Rank
Userlevel 4
Badge +13

Hi @maliodpalube, can you try adding a Reader (connecting to the same database), are you able to see any tables to select from? Have you been able to test adding a Spatial Writer instead of the Non-Spatial? If you're importing a schema from a spatial table, you might want to consider using the Spatial Writer.

 

Your Oracle library clients (32 vs 84-bit) should be okay if you're able to see the Oracle format as an option to add onto the canvas. Are you trying to connect using the service name, username, and password or with the easy string? Can you confirm that you're using SQL Developer or TOAD to access the database on the same machine you're running FME?

M
Badge

Hi @maliodpalube, can you try adding a Reader (connecting to the same database), are you able to see any tables to select from? Have you been able to test adding a Spatial Writer instead of the Non-Spatial? If you're importing a schema from a spatial table, you might want to consider using the Spatial Writer.

 

Your Oracle library clients (32 vs 84-bit) should be okay if you're able to see the Oracle format as an option to add onto the canvas. Are you trying to connect using the service name, username, and password or with the easy string? Can you confirm that you're using SQL Developer or TOAD to access the database on the same machine you're running FME?

I have tried to add a reader and as an easy connect and also with Server name but I get the same error. I am loading the same db with SQL Developer and I can load and use the same data base. What could be the problem?

 

 

Thank You in advance.

 

 

M
Badge

It seemed that there was a bug in data base since today the admin just reset my username and password to the same as they were and I normally accessed to the data base.

 

 

Thank You all for effort and for all the help that you provided me.
david_r
Rank
Userlevel 4

It seemed that there was a bug in data base since today the admin just reset my username and password to the same as they were and I normally accessed to the data base.

 

 

Thank You all for effort and for all the help that you provided me.
Good to hear, and thanks for sharing the solution!

 

Reply


Community Stats

28,717
Posts
109,495
Replies
36,560
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.