Skip to main content
Question

Staturate Oracle using the Schema reader to retrieve geographical tables

A
Forum|alt.badge.img

Hi everyone,

I have a script which contains two readers, an Oracle Spatial-Object reader to connect to the Oracle database, and a Schema reader to get information about the database.

I use this script to get all geographical tables from the database and it's work find.

But when I try with a complex/largest database I'm getting an Oracle ORA-00600 error for the SQL SELECT statement FME made. This happened when I use FME 2014 but not when I use FME 2013. I ask a database analyst who tell me that the SQL statement made by FME it is not optimized and saturate the Oracle SGA. I think this is because the user has access to many Oracle schemas so there are like 500 tables.

Is there a better solution to get all geographical tables with FME ? Did the SQL statement change with newer FME versions ?

Thanks in advance

5 replies

david_r
Celebrity
  • david_rCelebrity
  • May 19, 2016

That is a really weird error to get, I've never seen it myself. The error comes from Oracle, not from FME, but it is of course possible that FME is doing something to provoke the error on the Oracle side, although I cannot imagine how, really.

Is installing FME 2016.1 an option to see if this is a bug that has already been fixed?

You can have several different versions of FME installed simultaneously, as long as you install them into different folders.

You should probably also try to update the Oracle client libraries.

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • May 19, 2016

I would try to avoid reading too many tables at a time. This can be done by using the schema reader and follow by a WorkSpaceRunner. That transformer can call a second workspace with the Oracle Spatial Reader and the parameter "Feature types to read" as a published parameter. The schema reader can feed the tables to the second workspace one by one and so avoid reading all tables at once.

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • May 19, 2016

An additional idea: Instead of the Schema reader use the SQLCreator reading the table names from the Oracle view USER_TAB_COLUMNS (or ALL_TAB_COLUMNS) so you can filter on tables having the data type SDO_GEOMETRY.

A
Forum|alt.badge.img
david_r wrote:

That is a really weird error to get, I've never seen it myself. The error comes from Oracle, not from FME, but it is of course possible that FME is doing something to provoke the error on the Oracle side, although I cannot imagine how, really.

Is installing FME 2016.1 an option to see if this is a bug that has already been fixed?

You can have several different versions of FME installed simultaneously, as long as you install them into different folders.

You should probably also try to update the Oracle client libraries.

Thanks for the answer.

Unfortunately I can't try with FME 2016. For the moment I use FME 2013. I will try to optimize my script because I do not think the Schema reader is made to retrieve geographical tables like I want. Like erik_jan says maybe with an SQLCreator.

A
Forum|alt.badge.img
erik_jan wrote:

An additional idea: Instead of the Schema reader use the SQLCreator reading the table names from the Oracle view USER_TAB_COLUMNS (or ALL_TAB_COLUMNS) so you can filter on tables having the data type SDO_GEOMETRY.

Hi thank you for the answer.

Yes I think i will try the SQLCreator approch, because at the end I just want the list of the geographical tables and not all the information retrieved by the Schema Reader.

Reply


Helpful Members This Week

Community Stats

31,182
Posts
118,186
Replies
38,791
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

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