0 
0 Riddle me this: Can an object exist in all_objects before it is created?
Well no, of course not.
I was under the assumption that if you create a table with a select-statement, a CTAS – Create Table As Select – that the select was executed prior to the creation of the table. Read Consistency dictates that the result set returned by a Select statement, returns the values as of that particular moment in time.
To demonstrate this Tom Kyte has an example similar to this:
SQL> var rc refcursor
SQL> create table t
2 as
3 select *
4 from dual
5 /
Table created.
SQL> exec open :rc for select * from t
PL/SQL procedure successfully completed.
SQL> delete from t
2 /
1 row deleted.
SQL> commit
2 /
Commit complete.
SQL> print
D
-
X
SQL>
The shortest possible script to demonstrate Read Consistency. The result you see after the Delete followed by a Commit, are returned by the Ref Cursor, which was executed prior to the delete.
Odd or at least Remarkable?
Isn’t this odd then?
SQL> drop table t purge
2 /
Table dropped.
SQL> select owner, object_name
2 from all_objects
3 where owner = 'ALEX'
4 and object_name = 'T'
5 /
no rows selected
SQL> create table t
2 as
3 select *
4 from all_objects
5 /
Table created.
SQL> select owner, object_name
2 from t
3 where owner = 'ALEX'
4 and object_name = 'T'
5 /
OWNER OBJECT_NAME
------------------------------ -----------
ALEX T
SQL>
How is it possible that an entry is alread present in the newly created Table T, which should be a copy of ALL_OBJECTS? Apparently, when you create a table, an entry in the datadictionary is created prior to executing the Select-statement…. strange, don’t you think?
That goes to show it, the datadictionary is "special".
Related posts:
About nulls in the database Death of the End Date – How LAG and LEAD help fight redundancy 
Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table Dinsdag 18 september: Oracle Open World & JavaOne 2012 Preview mini-conferenties – 12 presentaties op 1 avond 
Hotsos 2013 – Presentation material “Creating Structure in Unstructured Data”
Dear Sunil,
I took the liberty of removing your phone numbers from the blog for privacy matters.
To answer your question I do not know what GUI is used with Oracle 11i. Maybe this blog helps to answer your question: http://technology.amis.nl/blog/?p=169
Alex
Dear Sir
If possible would you please reply my query?
We want to develop a web based application and we would incorporate a GUI (Graphical User Interface) in our application just like oracle 11i application UI (User interface). I would like to know that technology behind to develop oracle 11i Web User Interface (Presentation Layer) only.
Thanks
Sunil Gidwani
Indore (Madhya Pradesh) – INDIA
That’s a good one, Edwin! Talking about the magic of the Data Dictionary…
Create table is DDL; so for that part read consistency is not required. First the table is created; does that part do an implicit commit? Then the query is started read consistency starts at that point in time?
Always nice to see these kind of mind-breakers.
Too bad you haven’t put the
create table B as select * from all_tables equivalent as step 1 in it.
No data found.
So we have an object and we have no table..
Add on to that thought, if the SELECT fails, the CREATE TABLE fails and is ‘rolled back’.
So what you are actually seeing in T is uncommited changes in the system tables underlying the data dictionary views. If the SELECT ran for long enough, and you were doing a simultaneous SELECT from all_objects in another session, the ‘T’ object shouldn’t be there.
I don’t think it’s special.
You can hardly expect the Select to execute before the table is created – where would the data go?
I’ve always assumed the steps are:
1. Parse
2. Create new table
3. Open cursor
If you look at DBMS_SQL, you’ll find that BEFORE executing a query, you’ll need to FIRST define the result set (DEFINE_COLUMN). In other words, Oracle can create the table before actually executing the query, apparenlty becasue this is inherent to SQL processing.