Topic: [SOLVED] support for Oracle schemas

Hi,
If your user is not allowed to access a schema, then the schema won't be visible in DataCleaner - we can't bypass you databases security ;-)
But what I think you mean is that your user has access to several schemas? In DataCleaner 1.0 this should be possible (to my knowledge, but please let me know if I'm wrong!) and in the current development version I think it's one of the known shortcomings?

I'm not able to test it before tomorrow though - anyone else than me who has something to add to this?

This has been fixed as of revision [446].
If you want this bug fixed and can't wait for version 1.2, here's what you need to do:

• Download the gui jar and put it in target.
• Download the core jar and put it in target/lib.
• Download the metamodel jar and put it in target/lib.

Also I've noticed - FIRST table of every schema is loaded OK, but not the rest.

Yes, you have described that all correctly. The thing is - Oracle doesn't have any privileges for indexes specifically, so in fact its probably their JDBC driver problem (according to this its been like that for a long time:
http://coding.derkeiler.com/Archive/Java/comp.lang.java.databases/2005-06/msg00026.html
)

So skipping index introspection is very important for Oracle users I think.

I have two: MetaModel-1.0.jar and MetaModel-0.3-SNAPSHOT.jar Which one should I replace?

Yes, it works! Thank you very much!

Not quite solved though - the logs still see a lot of errors before I start to get this:

WARN JdbcDataContextStrategy - Could not retrieve index info for table: ALL$OLAP2UHIERDIM_KEYCOL_MAP
INFO JdbcDataContextStrategy - ORA-01702: a view is not appropriate here

WARN JdbcDataContextStrategy - Could not retrieve index info for table: ALL$OLAP2UHIER_CUSTOM_SORT
INFO JdbcDataContextStrategy - ORA-01702: a view is not appropriate here

Could not retrieve index info for table: ALL$OLAP2UJOIN_KEY_COLUMN_USES
INFO JdbcDataContextStrategy - ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded

ERROR JdbcDataContextStrategy - Error retriving table metadata
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
at oracle.jdbc.driver.OracleDatabaseMetaData.getColumns(OracleDatabaseMetaData.java:262)
at dk.eobjects.metamodel.JdbcDataContextStrategy.populateTables(JdbcDataContextStrategy.java:202)
at dk.eobjects.metamodel.JdbcDataContextStrategy.getSchemaByName(JdbcDataContextStrategy.java:680)
at dk.eobjects.metamodel.DataContext.getSchemaByName(DataContext.java:260)
at dk.eobjects.metamodel.DataContext.getSchemas(DataContext.java:201)
at dk.eobjects.datacleaner.gui.widgets.SchemaTree.updateTree(SchemaTree.java:54)
at dk.eobjects.datacleaner.gui.widgets.SchemaTree.update(SchemaTree.java:86)
at java.util.Observable.notifyObservers(Unknown Source)
at java.util.Observable.notifyObservers(Unknown Source)
at dk.eobjects.datacleaner.gui.model.DataContextSelection.setDataContext(DataContextSelection.java:67)
at dk.eobjects.datacleaner.gui.GuiHelper.selectSchemaByDatabase(GuiHelper.java:161)
at dk.eobjects.datacleaner.gui.dialogs.OpenDatabaseDialog$2.actionPerformed(OpenDatabaseDialog.java:157)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.Dialog$1.run(Unknown Source)
at java.awt.Dialog$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.Dialog.show(Unknown Source)
at java.awt.Component.show(Unknown Source)
at java.awt.Component.setVisible(Unknown Source)
at java.awt.Window.setVisible(Unknown Source)
at java.awt.Dialog.setVisible(Unknown Source)
at dk.eobjects.datacleaner.gui.widgets.OpenDatabaseButton$1.actionPerformed(OpenDatabaseButton.java:40)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Looks to me like the index error deosn't release connection properly or something like that...

Hmm I can't really reproduce the scenario (my oracle doesn't seem to have the same problem, don't know why) so I will wait for your confirmation before releasing this ... Can you please try out this MetaModel jar?

http://www.eobjects.dk/metamodel/MetaModel-1.0.2.jar

OK, I'm on my way out of the door right now, but I will look at again in a couple of hours and try to recreate the scenario "for real" so I won't have to use you as my beta-tester :) I'll post what I find out at least within the next 24 hours.

This is great - I finally was able to load everything correctly in Profiler. It took ages though as SYS schema in oracle contains a lot of objects that frankly one should not be looking at anyway. So is it possible to exclude some schemas from introspection (or at least an option to exclude SYS schema)? Generic solution could be loading schema metadata on demand, i.e. initially you load only schemas then when user clicked on expand in the tree - tables, but I that might not be possible if your metadata object is totally separate from the GUI...

Anyway its a good start and I already have a few suggestions about profiler which I'll describe in another thread...

Having option to filter out the views is good one, however it doesn't solve this particular problem with SYS views as I still want to be able to access the views in all other schemas apart from SYS ones...

Just a little notice. We're now done with the lazyloading tree ticket - you can [BuildingDataCleaner build datacleaner yourself] or wait for the 1.3 release to start using it :)

Hi BenBor,

Honestly, we haven't tested it with that many tables but I did experience some lag-time when opening the one of the system schemas that holds a little over 100 tables as far as I recall. I'm pretty convinced that if you wait a little while, you'll eventually get a working app, but this is of course not acceptable performance-wise. One thing I'm thinking is that perhaps the "thin" driver is not appropriate for this kind of operation? I'm no Oracle expert but they have different types of JDBC drivers and perhaps some of them are better for more performance-intensive operations?

Did u try to only select tables or views, not both?

I took the liberty of formatting your reply a bit to make it look nicer with our website :) If you want formatting tips, you can check out: WikiFormatting. I'm going to try and reply to your comments one by one :)

1. OK, but I guess it's also not satisfactory to just rule out views or tables.
2. I think you can shut it down now :)
3. This is really the interesting part. Your assumptions are right as with the eager loading of metadata and I guess we could perhaps tweak that part a bit but it's not really the issue and since we use MetaModel as our data access component this is not just a matter of how the tree works but how the model for the whole app works. In order to get closer to diagnosing this problem, can you please inform me of:

• What version of Oracle you are running
• What JDBC driver you're using
• The structure of the DSSADM.FACT_IMMUNISATION_SUMM_IDX_0 index. This is crucial I think so all the info you have please.
• The structure of the FACT_IMMUNISATION_SUMMARY tables, including indexes, foreign and primary keys.
• If it's possible, a little .sql script with a snippet of the table would be best, then I'll create an exact copy of your scenario and will have the best ways of testing it.

4. I agree that there are strengths and weaknesses in both projects and we are talking with them on ways we can collaborate, but I don't believe we'll see that the projects merge. We will however probably coorporate on some of the data quality "input" such as regexes, dictionaries etc.
5. We're always open ears for new ideas. And if you want to help us along a lot of things can be done of course. Our project structure is informal and if some one has an idea that they want to work with we won't stop them unless it interferes with other plans.

1. I agree, normally you won't profile views. But to some users it may be easier to get an overview of their data by looking at the views - The only example I can come up with is for the Javascript evaluator, it would be beneficial to create a joined view if you where to check the integrity of two columns from different tables, then you could reference the joined values in the same javascript.
2. I think you can shut it down - at least I'm pretty sure we'll overcome this bug eventually, even though it has been a pain in the a...
3. Yes please email it to me at kasper@eobjects.dk then I'll give it a go. I'm running Oracle 10g (Express) but hopefully it will let me recreate the scenario.

I agree with that also :) I think the line of thought by some may be that they think of tables as the "sources to views" and then may feel uncomfortable with doing DQ analysis on behalf of the result as apposed to the source. If you profile or validate a table you'll get results that apply directly to the table your working with. If you want to make corrections to a view you'll have to find the actual table and do the corrections there.

I fully agree that in the scenarios you describe it is sensible to profile a view.[[BR]]
However, I would not recommend these scenarios. If an organisation is serious about Data Quality, it should have a Data Quality expert who performs the profiling, formats the results into plain English (or whatever the local language is) and discusses it with the business.[[BR]][[BR]]
I would not expect users to do their own data profiling.