Skip to main content
Question

Unable to access Oracle views generated by Workspace Manager

1
Forum|alt.badge.img+1

Hi

I'm wondering if anyone has encountered this - I'm looking to read some data held in views in an Oracle workspace. The views are generated by Oracle Workspace Manager (viewname_CONF for conflicts seems the naming convention).

I can read user-generated views using the Oracle Spatial Reader, but I cannot seem to read the workspacemanager-generated views using the Reader.

I can access the data in the workspacemanager-generated views using SQLCreator transformer, but then I have the overhead of exposing all the attributes (which I can probably automate).

So FME can access the data as the user I'm connecting as, but seems to ignore these views. Has anyone else encountered this? Is there a way to access them via Reader? some setting I'm missing??

Thanks

Mary

9 replies

david_r
Celebrity
  • david_rCelebrity
  • June 5, 2018

I've not encountered this specific issue, but I suspect it could be related to missing Oracle metadata in user_sdo_geom_metadata.

More info here: https://knowledge.safe.com/articles/782/adding-metadata-entries-for-oracle-spatial-tables.html

lars_de_vries
Forum|alt.badge.img+10

The only thing I can think of is you do not have the correct rights to read those views. But I would expect you to already have checked that. Otherwise I would not know.

1
Forum|alt.badge.img+1
lars_de_vries wrote:

The only thing I can think of is you do not have the correct rights to read those views. But I would expect you to already have checked that. Otherwise I would not know.

Thanks - I can access the data as my user via SQLCreator in FME, and also external to FME in SQLDeveloper, so I don't think it's permissions, but I'm happy to be wrong,..

 

 

1
Forum|alt.badge.img+1
david_r wrote:

I've not encountered this specific issue, but I suspect it could be related to missing Oracle metadata in user_sdo_geom_metadata.

More info here: https://knowledge.safe.com/articles/782/adding-metadata-entries-for-oracle-spatial-tables.html

Thanks - I created an entry in user_sdo_geom_metadata for some of the views but still unable to access

 

 

debbiatsafe
Safer
Forum|alt.badge.img+20
Hi @1spatialmary

 

Could you please try manually typing the view name within the reader? Is the reader able to access the workspace manager-generated view?

 

steveatsafe
Safer
Forum|alt.badge.img+12
Hi @1spatialmary,

 

Do you have any non spatial tables that you can test to see if you can see those OMS generated views? If this isn't an user_sdo_geom_metadata issue then I'm not sure what it could be.

 

 

Further to David's response.... As a test, are you able to connect as the schema owner of the views? This would ensure you are seeing the correct sdo_geom_metadata information. If this works, then try adding an entry to the user_sdo_geom_metadata for the user you are connecting as to see if that helps...

 

 

What version of FME?

 

What version of Oracle is involved here?

 

Is there anything special in how these OMS versioned tables were created?

 

If we need to try to reproduce this any information on how these tables were created are helpful.

 

SteveatSafe
1
Forum|alt.badge.img+1
debbiatsafe wrote:
Hi @1spatialmary

 

Could you please try manually typing the view name within the reader? Is the reader able to access the workspace manager-generated view?

 

Thanks @DebbieAtSafe - manually entering the view name has worked - it picks up the view and it's attributes. So I'm guessing the 'issue' (and it's probably intentional) is with the schema scan part of the Reader perhaps. Would it be possible to alter/add something to enable other system generated tables/views like this? I've tried using the 'system tables' setting, but this doesn't find it automatically either

 

1
Forum|alt.badge.img+1
steveatsafe wrote:
Hi @1spatialmary,

 

Do you have any non spatial tables that you can test to see if you can see those OMS generated views? If this isn't an user_sdo_geom_metadata issue then I'm not sure what it could be.

 

 

Further to David's response.... As a test, are you able to connect as the schema owner of the views? This would ensure you are seeing the correct sdo_geom_metadata information. If this works, then try adding an entry to the user_sdo_geom_metadata for the user you are connecting as to see if that helps...

 

 

What version of FME?

 

What version of Oracle is involved here?

 

Is there anything special in how these OMS versioned tables were created?

 

If we need to try to reproduce this any information on how these tables were created are helpful.

 

Hi @SteveAtSafe - manually entering the view name worked (Debbie suggested above).

 

I'm using FME 2018 (w64) build 18305, Oracle 12.1.0.2 (although the environment I will be ultimately be working with will be 12.1 I think).

 

As I'm only reading the data, I've asked how the views are generated - and there is nothing special in the OWM configuration.

 

It's probably best if I raise a support case with some details, that should help with building a replication scenario

 

Thanks

 

1
Forum|alt.badge.img+1
1spatialmary wrote:
Hi @SteveAtSafe - manually entering the view name worked (Debbie suggested above).

 

I'm using FME 2018 (w64) build 18305, Oracle 12.1.0.2 (although the environment I will be ultimately be working with will be 12.1 I think).

 

As I'm only reading the data, I've asked how the views are generated - and there is nothing special in the OWM configuration.

 

It's probably best if I raise a support case with some details, that should help with building a replication scenario

 

Thanks

 

Case number C137219 for reference

 

 

Reply


Helpful Members This Week

Community Stats

31,344
Posts
118,865
Replies
38,931
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.