Home » SQL & PL/SQL » SQL & PL/SQL » union all and sql*plus
union all and sql*plus [message #41387] Thu, 26 December 2002 10:51 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #652188 is a reply to message #41387] Fri, 03 June 2016 15:30 Go to previous messageGo to next message
bbarreira
Messages: 1
Registered: June 2016
Location: Brazil
Junior Member
I have to say, your answer is totally correct!
Re: union all and sql*plus [message #652189 is a reply to message #652188] Fri, 03 June 2016 15:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
bbarreira wrote on Fri, 03 June 2016 13:30
I have to say, your answer is totally correct!

After 13+ years, somebody provides feedback!

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Re: union all and sql*plus [message #652191 is a reply to message #652189] Fri, 03 June 2016 18:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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.


Re: union all and sql*plus [message #652215 is a reply to message #41387] Sat, 04 June 2016 07:57 Go to previous messageGo to next message
EdStevens
Messages: 1332
Registered: September 2013
Senior Member
Eugene wrote on Thu, 26 December 2002 10:51
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???


One thing you did wrong was transcribe (incorrectly) your sql statement. In the statement above you mis-spelled 'from' in the second select, which would lead to :

SQL> select a from table_1
  2  union all
  3  select b form table_2;
select b form table_2
              *
ERROR at line 3:
ORA-00923: FROM keyword not found where expected


When dealing with code and error messages, you should always use copy and paste to present your work. In this particular case you are lucky that no one else caught that, or they correctly assumed the incorrect transcription and chose to focus on what was likely the real issue.
Re: union all and sql*plus [message #652311 is a reply to message #652215] Mon, 06 June 2016 17:53 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
The error he was getting would have taken precedence over ORA-00923 so that typo could have been in the original.
Re: union all and sql*plus [message #652329 is a reply to message #652311] Tue, 07 June 2016 06:38 Go to previous message
EdStevens
Messages: 1332
Registered: September 2013
Senior Member
cookiemonster wrote on Mon, 06 June 2016 17:53
The error he was getting would have taken precedence over ORA-00923 so that typo could have been in the original.


And my error was in responding to the OP without noticing how old the thread is ... Embarassed
Previous Topic: Convert Rows to Columns
Next Topic: Number to words
Goto Forum:
  


Current Time: Tue Jul 27 20:30:07 CDT 2021