How to create a temporary / dynamic / virtual table when a SQL runs in Oracle?

Question

I have some data that has to be measured which are not in any table. I can not insert it to a table nor I can create any table and insert these data. So I used dual like the following to get that table. I used this to join with other tables.

with movie_genre as
(
select '10' as "id", 'action' as "genre" from dual
union select '20' as "id", 'horror' as "genre" from dual
union select '30' as "id", 'comedy' as "genre" from dual
union select '40' as "id", 'adventure' as "genre" from dual
union select '50' as "id", 'drama' as "genre" from dual
union select '60' as "id", 'mystery' as "genre" from dual
union select '70' as "id", 'musical' as "genre" from dual
)
select * from movie_genre
;

So that I get the result -

id  genre
10  action
20  horror
30  comedy
40  adventure
50  drama
60  mystery
70  musical

My question is, is there any better way to do this? Any suggestion will be a life saver.

An example -

Lets say we have a table -

create table movies (
id number,
name varchar2(50),
genre_id number
);

insert into movies values (1, 'the hulk', 10);
insert into movies values (2, 'dumb and dumber', 30);
insert into movies values (3, 'frozen', 70);

And we need a result like this -

name                genre        is_in_genre
the hulk            action       yes
the hulk            horror       no
the hulk            comedy       no
the hulk            adventure    no
the hulk            drama        no
the hulk            mystery      no
the hulk            musical      no
dumb and dumber     action       no
dumb and dumber     horror       no
dumb and dumber     comedy       yes
dumb and dumber     adventure    no
dumb and dumber     drama        no
dumb and dumber     mystery      no
dumb and dumber     musical      no
frozen              action       no
frozen              horror       no
frozen              comedy       no
frozen              adventure    no
frozen              drama        no
frozen              mystery      no
frozen              musical      yes

Here, we DO NOT have any movie_genre table.

Answer 1

You can pass the genre as string in the order you want and use regular expression to generate the movie_genre table.The sql fiddle here

with movie_genre as
( 
 select level * 10 as id, regexp_substr(genre,'[^,]+',1,level) as genre
 from
 (
  select ('action,horror,comedy,adventure,drama,mystery,musical') 
  as genre from dual
  )
  connect by level <=REGEXP_COUNT(genre,'[^,]+')
 )
 select * from movie_genre;

Answer 2

If you need to get the full list, you want a cross join, with some additional logic:

with movie_genre as (
      select '10' as "id", 'action' as "genre" from dual union all
      select '20' as "id", 'horror' as "genre" from dual union all
      select '30' as "id", 'comedy' as "genre" from dual union all
      select '40' as "id", 'adventure' as "genre" from dual union all
      select '50' as "id", 'drama' as "genre" from dual union all
      select '60' as "id", 'mystery' as "genre" from dual union all
      select '70' as "id", 'musical' as "genre" from dual
     )
select m.name, mg.genre,
       (case when m.genre_id = mg.id then 'yes' else 'no' end) as IsInGenre
from movies m cross join
     movie_genres mg;