DbVisualizer 5.1.1.6 is now available

Roger, now that we are on the subject of mviews and the rebuild actions, why not have it rebuild in the background by submitting a dbms_scheduler job that does the rebuild, instead of in the users session. Those rebuild actions can take a while to complete. The same applies to rebuilding indexes. regards, Ronald.

I got 'item should be declared'. This surprized me since the generated sql looked good to me and worked when I copied it to the sql commander and enclosed it between '--/' and '/'. I can try to reproduce in debug mode I guess. An error occured while performing the operation: ORA-06550: line 1, column 26: PLS-00201: identifier 'SH.CAL_MONTH_SALES_MV' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored generated sql was: An error occured while performing the operation: ORA-06550: line 1, column 26: PLS-00201: identifier 'SH.CAL_MONTH_SALES_MV' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored In debug window: 21:09:41 [DEBUG AWT-EventQueue-1 N.?] evaluate expression: #method.equals('') ? '' : ',\'' + #method + '\'' Error: target is null for method equals 21:09:41 [DEBUG AWT-EventQueue-1 N.?] evaluate expression: #method.equals('') ? '' : ',\'' + #method + '\'' Error: target is null for method equals 21:09:45 [DEBUG AWT-EventQueue-1 N.?] evaluate expression: #method.equals('') ? '' : ',\'' + #method + '\'' Error: target is null for method equals 21:09:45 [DEBUG AWT-EventQueue-1 N.?] evaluate expression: #method.equals('') ? '' : ',\'' + #method + '\'' Error: target is null for method equals 21:09:50 [DEBUG AWT-EventQueue-1 N.?] evaluate expression: #method.equals('') ? '' : ',\'' + #method + '\'' Error: target is null for method equals 21:09:50 [DEBUG AWT-EventQueue-1 N.?] evaluate expression: #method.equals('') ? '' : ',\'' + #method + '\'' Error: target is null for method equals 21:09:50 [DEBUG Thread-29 N.?] evaluate expression: #method.equals('') ? '' : ',\'' + #method + '\'' Error: target is null for method equals 21:09:50 [DEBUG Thread-29 B.execute] CommandId: oracle-materializedview-refresh sql: begin DBMS_MVIEW.REFRESH('${schema}.${objectname}'${_method}); end; 21:09:50 [DEBUG Thread-29 B.?] SQL contains (null) for Command: oracle-materializedview-refresh 21:09:50 [DEBUG Thread-29 B.?] SQL: begin DBMS_MVIEW.REFRESH('SH.CAL_MONTH_SALES_MV'(null)); end; 21:09:50 [DEBUG Thread-29 L.setCurrentCatalog] Database='test10.ronald-g4' Catalog='null' Schema='null' NewCatalog='null' 21:09:50 [DEBUG Thread-29 L.execute] Executing... 21:09:50 [DEBUG Thread-29 L.prepareStatement] createStatement 21:09:50 [DEBUG Thread-29 L.prepareStatement] SQL: begin DBMS_MVIEW.REFRESH('SH.CAL_MONTH_SALES_MV'(null)); end; 21:09:50 [DEBUG Thread-29 L.prepareStatement] nativeSQL from driver: begin DBMS_MVIEW.REFRESH('SH.CAL_MONTH_SALES_MV'(null)); end; Roger, I think it has to do with the fact that I selected none of the radio buttons assuming that 'default' would already be selected and honoured. When looking at the debug window I think the default option has not been initialized. The command executes fine when I selected 'default', which should already have been initialized. R.

the action refresh materialized view is not working. When I copy the generated pl/sql to the commander it works right away (after adding the plsql begin/end characters)

regards,
R.

When in SGA -> poorly performing sql's a sort has been done, the wrong sql appears in the SQL screen.

Oh, and the currentSchema setting on the driver properties is exactly what I'm looking for (so it seems at first glance). I connect and my default schema is set correctly. So the moral of the story is "know your driver". Thanks, Mark

> Do you mean that when completing for example ALL_<AC> it should list all tables in all schemas?

Well, as you know, I have a few requests out for the auto complete so you can add that to the list (though probably as a much lower priority).

I'd anticipate the AC function working as follows:

1) Start typing
2) Hit the ctrl-space - AC box appear listing all table names that contain the supplied text (anywhere in the name) from all schemas
3) The suggested table is completed in the editor, but I'm still able to overtype to narrow my selection (I could just hit enter to accept the suggested table)
4) Keep typing, the list of tables narrows
5) I've found the four tables I want to join so I multi select them from the AC box and hit enter
6) The four tables are dropped into the editor


So if I type "alloc" I should get
appschema.vw_allocations
appschema.vw_allocationtypes
auditschema.vw_oldallocations


In order of priority (for me) I'd go for (not necessarily in order of complexity):

High: Searching for tables matching my typed text anywhere in the table name
Mid: Multi-select tables from the AC box
Low: Type-ahead suggestion of the best fit table (probably not needed as hitting enter already inserts the top table in the list)
Low: searching across all schemas

Mark

>Or do you mean a new property where you specify the name of the "default schema?" I think I need a new feature then. Yes, I'll be connecting with one profile (which may have a matching schema) but my activity will be against our application's default schema. I need the connection to remember that. But while I'm here, why does the auto-complete only run against the currently selected schema? I imagine performance considerations are at the heart of it. But could this be opened out to include all schemas? That in itself would alleviate my problem. I know I've already got a few auto-complete requests in the mix but it would be useful to consider opening it up across schemas too. Mark

Hi Mark, I'm looking into this issue now. Can you please clarify a couple of things? > I have a feeling that by default > the dropdown list in the SQL Editor setup was set to > use the same filters as on the explorer tree. So I > was able to filter down to the schema that I wanted > and that would be the only one listed which helps > with auto-complete. (I'll have a complain about > auto-complete only working for the currently selected > schema later...). I don't think the > filtering works now. You're right. The Schema and Catalog comboboxes used to have the same filtering applied as the Object Tree. The change was done when we added the new Create Table and Alter Table dialogs, since they use the same comboboxes as the SQL Editor and it wasn't clear to us at the time what the "best" behavior would be. I (now) believe it would be more consistent to have the filter apply in all places where catalogs and schemas are listed, i.e., both in the Object Tree and in all places where they can be selected from comboxes. Is that what you ask for, considering it also applies to the Create Table and Alter Table dialogs? > Ideally, what we > should have is a "default schema' setting > against the connection itself. If we can set that the > connection will remember where we want to be and set > the dropdowns accordingly. Mind you, this > is only an issue because auto-complete only works > against the currently selected schema. You can already set the "Show only default database or schema" option for a specific connection. See the Properties tab in the Object View when you have the connection selected in the Object Tree. Or do you mean a new property where you specify the name of the "default schema?" Thanks, Hans

Roger,

I'm only using DB2 8.2, not Oracle.

I have a feeling that by default the dropdown list in the SQL Editor setup was set to use the same filters as on the explorer tree. So I was able to filter down to the schema that I wanted and that would be the only one listed which helps with auto-complete (I'll have a complain about auto-complete only working for the currently selected schema later...).

I don't think the filtering works now.

Ideally, what we should have is a "default schema" setting against the connection itself. If we can set that the connection will remember where we want to be and set the dropdowns accordingly.

Mind you, this is only an issue because auto-complete only works against the currently selected schema.

Mark