How can we help you today?

Start a new topic
Discussions DbVisualizer Forums Support

create view statement requires schema name

[This topic is migrated from our old forums. The original author name has been removed]

Hello everybody @dbvis.com, i'm using good old oracle 8i (good heavens!) and stumbled upon a strange issue when it comes to creating a view in sql commander. The 'create view' statements fails if no schema is provided (e.g. 'create view schema.viewname ...') as long as the definition of the view contains a select statement on a physical table (e.g. not 'dual'), which it usally does. Error message: 'ORA-00942: table or view does not exist' Two examples to clarify this. We put this in sql commander assuming a valid database connection and schema (e.g. 'schema1') is selected: 1. +create table test (col1 number(1));+ +create view v_test as select * from test;+ The second line fails with the error message above. 2. +create view v_test as select * from dual;+ This works. To make example 1 working i have to provide a schema name: +create view schema1.v_test as select * from test;+ This works. When using simple Oracle SQLPlus Shell, example 1 just works fine without the schema name. According to oracle documentation it's not required to provide the name of the schema. (http://docs.oracle.com/html/A85397_01/state10e.htm#2065512). I'm using: Product: DbVisualizer Pro 9.1.10 Build: #2295 (2014-08-08 18:38:31) Java VM: Java HotSpot(TM) Client VM Java Version: 1.6.0_26 Java Vendor: Sun Microsystems Inc. OS Name: Linux OS Arch: i386 OS Version: 3.2.0-70-generic-pae Thanks for help and so long, roland
12 CommentsSorted by Oldest First
[This reply is migrated from our old forums.]

Re: create view statement requires schema name
Hi Roland, First you need to make sure the Database Type setting in the Connection tab for your Oracle connection is set to either Auto Detect or Oracle. Then in the Connect Properties tab select the SQL Commander category in the list. In the right pane there is the "Set Current Schema" settings. Make sure it is set to "Use the Schema". Press Apply and re-connect. Does it now works better without qualifying with schema name when you create the view? (The correct Schema must be selected in the Schema drop-down). Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: create view statement requires schema name
Hello Roger, thanks for super fast reply! The database type is already set to 'Oracle' in the connection tab, and the 'Set current Schema' setting is already set to 'Use the Schema'. I'm using Orcale jdbc driver 10.2.0.4.0 by the way. so long, roland
[This reply is migrated from our old forums.]

Re: create view statement requires schema name
Hi Roland, Okay. Then try enable Debug of DbVisualizer in Tools->Debug Window. Then re-run the SQL in the SQL Commander. Post or email the debug output here. Regards Roger support@dbvis.com
[This reply is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Re: create view statement requires schema name
Hi Roger, ok wilco, see attached file. regards roland
[This reply is migrated from our old forums.]

Re: create view statement requires schema name
Hi Roland, What is the result if you run the following in the SQL Commander: select sys_context( 'userenv', 'current_schema' ) from dual Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: create view statement requires schema name
Hi Roger, the result of select sys_context( 'userenv', 'current_schema' ) from dual is the name of the schema i'm currently connected to: BIHMW regards roland
[This reply is migrated from our old forums.]

Re: create view statement requires schema name
Roland, We will have to wipe the dust off of an old Oracle 8i installation and try this. I'll keep you updated. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: create view statement requires schema name
Hi Roger, uuh, this issue isn't worth to raise such a lot of dust ;-) Assuming nobody else than me is riding such an old system, you don't have to try to hard! I guess, if it works on oracle 11+ then i just get used to qualify the schema name. Just wanted to let you know about this thing. so long, roland
[This reply is migrated from our old forums.]

Re: create view statement requires schema name
Hi Roland, I tried this with Oracle 8 and it works fine here. One last test before closing this: You may try setting the Schema drop-down to another non-default schema and watch the debug window if there is an ALTER SESSION SET current_schema = "" in the log. Once you have made the above, switch back to the correct schema and run the SQL again. Have a nice weekend. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: create view statement requires schema name
Hello Roger, selecting any other schema in the schema drop down list doesn't trigger any new output in my debug log (the checkbox 'Debug DbVisualizer' is checked, of course). Only if i select another database connection (the other drop down to the left), there are some new lines like +'select sys_context( 'userenv', 'current_schema' ) from dual'+ But anyway the problem stays the same after switching back to the correct schema. Regards, roland
[This reply is migrated from our old forums.]

Re: create view statement requires schema name
Hi Roland, The ALTER statement I am referring is (should be) echoed in the debug log only at next execute. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Re: create view statement requires schema name
Hi Roger, ok, thanks for this hint. So i tried this again. I changed to another schema, and run some SQL. Then turned on debug logging, switched back to the current schema and run the 'create view...' statement. The 'ALTER SESSION SET current_schema...' statement is now in the attached log. so long, roland
Comments to this discussion are now closed!
More topics in Support
See all 1311 topics