CREATE VIEW gives Invalid character error 1

[sjh]

Hi, my script contains these two create view statements. When I run the script, it gives me ORA-00911: invalid character error message on "));" line.

When I run the two statements separately, it works fine. So I know that each statement on its own has no problem.

Please help!

Thank you!

CREATE OR REPLACE VIEW V_MYVIEW1 AS (
(
select blah
from blah
where blah
)
UNION
(
select blah
from blah
where blah
)); <----------- Error message!

CREATE OR REPLACE VIEW V_MYVIEW2 AS (
select blah
from blah
where blah
);

 

[johnherman]

See "Looking for a script to create Views",
thread759-844448
for syntax of create view with UNION.
HTH

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[SantaMufasa]

SJH,

So far, I'm puzzled by your error. Although your inner parentheses are extraneous, they are also harmless. Here are my results from doing something similar to your exercise:

create view blah as
(select id, last_name name from s_emp
union
select id, name from s_dept);

View created.

create view blah2 as
((select id, last_name name from s_emp) union
(select id, name from s_dept));

View created.

So, unless you can see anything different between your code and mine, I'm presently stumped.

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:12 (02Aug04) UTC (aka "GMT" and "Zulu"), 16:12 (02Aug04) Mountain Time)

 

[zephan]

sjh, using another client than sqlplus may cause this error. I used to get the same error with a third party software, and could run may script only in sqlplus emulator.
I think those softs get pure sql statment and send it to the server to execute including thier own ';'.
Regards,
Zephan

 

[sjh]

A big thank you to everyone who responded! Zephan was right. In PL/SQL, I was using File-Open-SQL Script, which was giving me the invalid character error. I selected File-New-Command Window and my scripts ran successfully.

Thank you!
SJH