| union all and sql*plus [message #41387] |
Thu, 26 December 2002 10:51  |
Eugene
Messages: 44
Registered: August 2001
|
Member |
|
|
Hi all,
Why when I run the following:
select a from table_1
union all
select b form table_2@remote_db;
I get: unknown command "union all" - rest of line ignored
What am I doing wrong???
|
|
|
|
| Re: union all and sql*plus [message #41388 is a reply to message #41387] |
Thu, 26 December 2002 14:11  |
Anne
Messages: 36
Registered: April 2002
|
Member |
|
|
Maybe it can't read the remote access to the second table. Table 2 in not on the same database..i take it.
Therefore remote access to the second table might be a problem.
Ann
|
|
|
|
| Re: union all and sql*plus [message #41433 is a reply to message #41387] |
Thu, 02 January 2003 12:27 |
Ivan
Messages: 180
Registered: June 2000
|
Senior Member |
|
|
I completely disagree with your comment, Anne :)
The "SP2-0042: unknown command "text" - rest of line ignored." is returned by SQL*Plus when the command typed does not exist in the list of valid SQL*Plus commands. You'll get this error if you start typing the command as
UNION ALL SELECT ...
The result will be:
SP2-0042: unknown command "UNION ALL" - rest of line ignored.
The problem here is the way SQL*Plus interprets the commands passed to it. Remember the "SQL command terminators"? Semicolumn (;), slash (/) and a blank line
I bet Eugene had a blank line right before UNION ALL, something line this:
SELECT a FROM table_1
UNION ALL
SELECT b FROM table_2@db_link_1
This cause SQL*Plus to ignore the first statement (SELECT a FROM table_1), and return the error on the next line (UNION ALL).
That's all :)
Eugene, I hope this explains it
|
|
|
|
|
|
|
|
| Re: union all and sql*plus [message #652191 is a reply to message #652189] |
Fri, 03 June 2016 18:43 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Eugene posts a question.
Anne posts an answer.
Ivan disagrees with Anne and posts a different answer.
bbarreira responds to Eugene's posts, saying, "I have to say, your answer is totally correct!"
So, 13 years later, who is bbarreira saying is correct, Anne or Ivan?
Just for the record, I agree with Ivan.
If you don't have any blank lines, such code works fine:
SCOTT@orcl_12.1.0.2.0> select * from dual
2 union all
3 select * from dual@loopback@orcl
4 /
D
-
X
X
2 rows selected.
If you do have blank lines, and sqlblanklines is off, then SQL*Plus will generate the following error:
SCOTT@orcl_12.1.0.2.0> select * from dual
2
SCOTT@orcl_12.1.0.2.0> union all
SP2-0042: unknown command "union all" - rest of line ignored.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> select * from dual@loopback@orcl
2 /
D
-
X
1 row selected.
If you set sqlblanklines on, then it allows blank lines and does not raise an error:
SCOTT@orcl_12.1.0.2.0> set sqlblanklines on
SCOTT@orcl_12.1.0.2.0> select * from dual
2
3 union all
4
5 select * from dual@loopback@orcl
6 /
D
-
X
X
2 rows selected.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
