SELECT * FROM inviso_test_v3 would display only COL1 and COL2, but the DESC statement should include a row for each column.
A column in either a view or a table is either invisible or not. If it is invisible, it should not show up on a SELECT (unless directly referenced in the query) or in a DESCribe (unless the COLINVISIBLE setting is set to ON). A column should not be invisible in a SELECT but visible by default in the DESCRIBE. I don't have my 12c database up at the moment so I can't do a test case at the moment to demonstrate. However, my normal practice is to run all the SQL in my guides against the appropriate DB and then Copy & Paste the results directly to minimize typos (and brain farts). Every once in a while if I am rushed I'll skip this step, but I have been burned often enough when doing so that I try not to do so.
The following article by Tom Kyte demonstrates this behavior for a table, but not a view.
jaramill Thank you. I'd not come across that link.
Matthew Morris Taking a closer look at what I'd been doing, I see that my environment was not configured appropriately. I was executing the DESC statement via TOAD that was using the 11g client version. Once I used the 12c client I got the same result as in your book. And similar to Tom's article, setting COLINVISIBLE ON also made COL3 appear for the view. Thank you and I stand corrected!
Hello Mr. Morris,
Thank you for the book study guide for the 1Z0-071 exam, I bought the book last week, just a little "stupid" question :
Studying the guide, query and example are using an aircraft database (finctional), with table aircraft_types,airport and so on..., can I download the DDL and inserts? thanks in advance
...can I download the DDL and inserts?
The script is available on the 1Z0-071 page of the Oracle Certification Prep website.
Passed the exam with the help of Matthew's book!
I am starting to prepare the 1Z0-071 Oracle certification, so I've downloaded Oracle 11g Express Edition to test your book examples. In your book you say Oracle 12c XE doesn't exist yet so I won't be able to test the new parts like, as you say, row limiting clausole.
I would like to know how many parts of the book I won't be able test on my Oracle 11g Express Edition and if you think I will be able to pass the exam without testing these parts (but only studying). Please, let me know if you have some advice for me.
Some time ago I sent you another message asking you if you were preparing some excercises to add on your book. Let me know if you have some news about that, please.
I am looking forward to hearing from you soon.
I am planning to take the exam in few weeks. Just want to get a feel of how was the exam in terms of complexity. I have gone through the Matthew's book alone. is that sufficient?
Its a challenging exam and you have to move through the questions quickly. I strongly recommend you take a practice exam. Matt's book provides a good foundation, but it should be supplemented with other resources. Here's a great list.
(I tried Replying to your post, but something's wacky with the UI and prevents me from clicking the Add Reply button)
I am studying the Morris' book and I am running the first example on page 40 about the order by clausole. I am running it in Oracle 11c XE but the element order doesn't match with the book example.
I create a table in this way:
create table nicola(
I inserted values and i queried it. It is the result:
The book reports another result. does somebody know where I am getting wrong?
The NLS_SORT value of a database can alter the sort results of a query. I believe that the database character set can as well. The primary intent of that chapter is to point out that the order chosen when sorting character data is not always what you might expect. For example -- in your results, 100 sorts between 1 and 2. People new to SQL often try to sort numeric data that is stored in VARCHAR2 columns and get confused by the result.
Even sorting all character-based data can generate unusual results when the data has different cases. For example, depending on the sort settings of the database "aardvark" might sort after "Ant" due to the upper-case 'A'. You don't need to know the exact results a given query might return for the exam (because it is dependent on database settings), but you should be aware of how character-based sorts work in Oracle.
Thank you for your quick response.
I have another question for you. I don't have Oracle 12c installed on my machine so I am using Oracle 11g XE to test your examples.
I am studying the FETCH FIRST and OFFSET which don't work in 11g. How could I test them? Shall I install 12c on my machine? Or is there an instance somewhere to connect and do the test?
Thank you indded!!!!
Definitely, I am feelling better
Do you have any practice tests for 1Z0-071 available yet? I checked your website but cant see them.
I will be sitting the exam next week and while your study guide has been great, I would really like to sit at least one practice test before the real deal.