Hi!

I created an empty table, lets say, tb_test (col1 varchar2(20 char), col2 varchar2(20 char));

Now I created a view of this table, lets say, vw_test as select * from tb_test;

If I do select * from vw_test; I will get nothing, it is empty.

If I prepend a with clause with the same structure of my empty table, lets say, tb_test (col1, col2) as (select 'a', 'b' from dual) and do select * from vw_test; I get data from tb_test (not the table one, but the subquery temp table one), like some sort of table override feature.

My question is: is that really the expected behavior? If yes, is there any real possibility of changes on this behavior? If no, so I think I can try some of these in real production solutions.

Thank you very much! Best regards,

EDIT:

Just to simplify, I'm running the following SQL through SQL Developer:

with

my_table as
(
select dummy from dual
)

select *
from my_table
;

The table my_table has 530982 in my schema, but this query returns a single row with the column dummy and value X.

I tried it via LiveSQL, but it was just a different behavior. It does select from the table of the schema, not the subquery temporary one. As I'm not the DB administrator, I don't know what exactly "enables" this behavior on our database. Do you know what could it be?

Thanks again!

SQL> create table my_table as select table_name from all_tables where rownum <= 10;

Table created.

SQL> select * from my_table;

TABLE_NAME
------------------------------
TYPE_MISC$
ATTRCOL$
ASSEMBLY$
LIBRARY$
VIEWTRCOL$
ICOLDEP$
OPQTYPE$
REFCON$
NTAB$
SUBCOLTYPE$

10 rows selected.

--
-- the WITH "overrides" the table name
--
SQL>
SQL> with
  2  my_table as
  3  (
  4  select dummy from dual
  5  )
  6  select *
  7  from my_table
  8  ;

D
-
X

1 row selected.

--
-- and here things look 'confusing' but noticed the small
-- typo, the WITH refers to "mytable" not "my_table" so we 
-- end up using the one in the dictionary
--
SQL>
SQL> with
  2  mytable as
  3  (
  4  select dummy from dual
  5  )
  6  select *
  7  from my_table
  8  ;

TABLE_NAME
------------------------------
TYPE_MISC$
ATTRCOL$
ASSEMBLY$
LIBRARY$
VIEWTRCOL$
ICOLDEP$
OPQTYPE$
REFCON$
NTAB$
SUBCOLTYPE$

10 rows selected.

SQL>