Home » SQL & PL/SQL » Client Tools » Semicolon ; is not interpreted correctly.. :(
icon9.gif  Semicolon ; is not interpreted correctly.. :( [message #452073] Mon, 19 April 2010 22:29 Go to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Hi again.

I really hate this problem.

I always get this error..
"SP2-0734: unknown command beginning "elect"


But the problem is, there's no syntax error in my script. It's just that I do not put them separated by a line, only the semicolon itself.

For example
select 'test 1' from dual;
select 'test 2' from dual;
select 'test 3; from dual;


would result..
SQL> select 'test 1' from dual;
select 'test 2' from dual;
select 'test 3' from dual;
'TEST1
------
test 1

SQL> SP2-0734: unknown command beginning "elect 'tes..." - rest of line ignored.
SQL>


'TEST3
------
test 3         


The second command was not correctly interpreted as it eats out the first 's' there.

Is there anything I can do to avoid this?

I just do not want my script to be occupied by those unnecessary blank lines.

Thank you.

Re: Semicolon ; is not interpreted correctly.. :( [message #452074 is a reply to message #452073] Mon, 19 April 2010 22:33 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
works OK for me

SQL> !cat a.sql
select 'test 1' from dual;
select 'test 2' from dual;
select 'test 3' from dual;

SQL> @a
SQL> select 'test 1' from dual;

'TEST1
------
test 1

SQL> select 'test 2' from dual;

'TEST2
------
test 2

SQL> select 'test 3' from dual;

'TEST3
------
test 3

SQL> 

Re: Semicolon ; is not interpreted correctly.. :( [message #452075 is a reply to message #452073] Mon, 19 April 2010 22:33 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
My environment is HP-UX...

NIS CEL | uname -a
HP-UX s53kj113 B.11.11 U 9000/800 1510201964 unlimited-user license      


I ran the command directly from the server's sqllplus.

My .profile content..
ORACLE_HOME=/oracle/ora9.2
export ORACLE_HOME
PATH=/usr/bin:/etc:/oracle/ora9.2/bin:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/contrib/bin:.
export PATH
ORACLE_SID=celdwh
export ORACLE_SID
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
PS1='oracle9i >>'
export PS1
SHELL=/sbin/sh
export SHELL

stty erase "^H" kill "^U" intr "^C" eof "^D"

if [ -s "$MAIL" ]           # This is at Shell startup.  In normal
then echo "$MAILMSG"        # operation, the Shell checks
fi                          # periodically.

alias list='ls -lF|pg'
alias move='mv -i'
alias copy='cp -i'
alias rem='rm -i'
alias remA='rm -r -i'
alias remdir='rmdir -i'
alias ora_pro='ps -ef|grep ora_'
alias usage='cd /data/celusr/usage/'
alias cel01='cd /data/celusr/cel01/'
alias dba='cd /oracle/ora9.2/dba_/'
alias ctl='cd /oracle/ora9.2/dba_/ctl_file/'
alias tmmdata='cd /data/tmnusr/'
alias celdata='cd /data/celusr/'
alias reject='cd /data/celusr/reject'
alias oradata='cd /data/oradata/celdwh/'
alias celsub='cd /data/celusr/POSTPAID/SUBSCRIBER'
alias celnon='cd /data/celusr/POSTPAID/Non_CDR'
alias celusg='cd /data/celusr/POSTPAID/USAGE'
#alias alert='cd /oracle/oracle10g/product/10.2.0/admin/SIPIKAT/bdump'

EDITOR=vi;export EDITOR
Re: Semicolon ; is not interpreted correctly.. :( [message #452076 is a reply to message #452074] Mon, 19 April 2010 22:38 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
BlackSwan wrote on Tue, 20 April 2010 11:33
works OK for me

SQL> !cat a.sql
select 'test 1' from dual;
select 'test 2' from dual;
select 'test 3' from dual;

SQL> @a
SQL> select 'test 1' from dual;

'TEST1
------
test 1

SQL> select 'test 2' from dual;

'TEST2
------
test 2

SQL> select 'test 3' from dual;

'TEST3
------
test 3

SQL> 


Thanks BlackSwan.

Yep, that's the problem. Logically, it should have work.

But in my case, it always escaped the 1st character of the next command after the ';' and it will be repeatedly like that.

In other words, if I have 10 lines of error-free SQL command, only those odd line numbers command will work.

Example:
SQL> select 'test 1' from dual;
select 'test 2' from dual;
select 'test 3' from dual;
select 'test 4' from dual;
select 'test 5' from dual;
select 'test 6' from dual;
select 'test 7' from dual;
select 'test 8' from dual;
select 'test 9' from dual;
select 'test 10' from dual;
'TEST1
------
test 1

SQL> SP2-0734: unknown command beginning "elect 'tes..." - rest of line ignored.
SQL>
'TEST3
------
test 3

SQL> SP2-0734: unknown command beginning "elect 'tes..." - rest of line ignored.
SQL>
'TEST5
------
test 5

SQL> SP2-0734: unknown command beginning "elect 'tes..." - rest of line ignored.
SQL>
'TEST7
------
test 7

SQL> SP2-0734: unknown command beginning "elect 'tes..." - rest of line ignored.
SQL>

'TEST9
------
test 9

SQL> SP2-0734: unknown command beginning "elect 'tes..." - rest of line ignored.       


Errrrrrrrr.... Sad
Re: Semicolon ; is not interpreted correctly.. :( [message #452077 is a reply to message #452076] Mon, 19 April 2010 22:48 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
works OK for me

SQL> !cat a.sql
select 'test 1' from dual;
select 'test 2' from dual;
select 'test 3' from dual;
select 'test 4' from dual;
select 'test 5' from dual;
select 'test 6' from dual;
select 'test 7' from dual;
select 'test 8' from dual;
select 'test 9' from dual;
select 'test 10' from dual;

SQL> @a
SQL> select 'test 1' from dual;

'TEST1
------
test 1

SQL> select 'test 2' from dual;

'TEST2
------
test 2

SQL> select 'test 3' from dual;

'TEST3
------
test 3

SQL> select 'test 4' from dual;

'TEST4
------
test 4

SQL> select 'test 5' from dual;

'TEST5
------
test 5

SQL> select 'test 6' from dual;

'TEST6
------
test 6

SQL> select 'test 7' from dual;

'TEST7
------
test 7

SQL> select 'test 8' from dual;

'TEST8
------
test 8

SQL> select 'test 9' from dual;

'TEST9
------
test 9

SQL> select 'test 10' from dual;

'TEST10
-------
test 10

SQL> 

Re: Semicolon ; is not interpreted correctly.. :( [message #452078 is a reply to message #452077] Mon, 19 April 2010 22:51 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
BlackSwan wrote on Tue, 20 April 2010 11:48
works OK for me

SQL> !cat a.sql
select 'test 1' from dual;
select 'test 2' from dual;
select 'test 3' from dual;
select 'test 4' from dual;
select 'test 5' from dual;
select 'test 6' from dual;
select 'test 7' from dual;
select 'test 8' from dual;
select 'test 9' from dual;
select 'test 10' from dual;

SQL> @a
SQL> select 'test 1' from dual;

'TEST1
------
test 1

SQL> select 'test 2' from dual;

'TEST2
------
test 2

SQL> select 'test 3' from dual;

'TEST3
------
test 3

SQL> select 'test 4' from dual;

'TEST4
------
test 4

SQL> select 'test 5' from dual;

'TEST5
------
test 5

SQL> select 'test 6' from dual;

'TEST6
------
test 6

SQL> select 'test 7' from dual;

'TEST7
------
test 7

SQL> select 'test 8' from dual;

'TEST8
------
test 8

SQL> select 'test 9' from dual;

'TEST9
------
test 9

SQL> select 'test 10' from dual;

'TEST10
-------
test 10

SQL> 


Yeah.. It should!

I think this is has something to do with Unix.

Are there any Unix expertise here?

Thank you.
Re: Semicolon ; is not interpreted correctly.. :( [message #452079 is a reply to message #452078] Mon, 19 April 2010 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
>Are there any Unix expertise here?

SQL> !uname -a
Linux bcm-laptop 2.6.31-20-generic #58-Ubuntu SMP Fri Mar 12 05:23:09 UTC 2010 i686 GNU/Linux


od -c a.sql
0000000   s   e   l   e   c   t       '   t   e   s   t       1   '    
0000020   f   r   o   m       d   u   a   l   ;  \n   s   e   l   e   c
0000040   t       '   t   e   s   t       2   '       f   r   o   m    
0000060   d   u   a   l   ;  \n   s   e   l   e   c   t       '   t   e
0000100   s   t       3   '       f   r   o   m       d   u   a   l   ;
0000120  \n   s   e   l   e   c   t       '   t   e   s   t       4   '
0000140       f   r   o   m       d   u   a   l   ;  \n   s   e   l   e
0000160   c   t       '   t   e   s   t       5   '       f   r   o   m
0000200       d   u   a   l   ;  \n   s   e   l   e   c   t       '   t
0000220   e   s   t       6   '       f   r   o   m       d   u   a   l
0000240   ;  \n   s   e   l   e   c   t       '   t   e   s   t       7
0000260   '       f   r   o   m       d   u   a   l   ;  \n   s   e   l
0000300   e   c   t       '   t   e   s   t       8   '       f   r   o
0000320   m       d   u   a   l   ;  \n   s   e   l   e   c   t       '
0000340   t   e   s   t       9   '       f   r   o   m       d   u   a
0000360   l   ;  \n   s   e   l   e   c   t       '   t   e   s   t    
0000400   1   0   '       f   r   o   m       d   u   a   l   ;  \n
0000417
Re: Semicolon ; is not interpreted correctly.. :( [message #452081 is a reply to message #452079] Mon, 19 April 2010 22:58 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
BlackSwan wrote on Tue, 20 April 2010 11:53
>Are there any Unix expertise here?

SQL> !uname -a
Linux bcm-laptop 2.6.31-20-generic #58-Ubuntu SMP Fri Mar 12 05:23:09 UTC 2010 i686 GNU/Linux


od -c a.sql
0000000   s   e   l   e   c   t       '   t   e   s   t       1   '    
0000020   f   r   o   m       d   u   a   l   ;  \n   s   e   l   e   c
0000040   t       '   t   e   s   t       2   '       f   r   o   m    
0000060   d   u   a   l   ;  \n   s   e   l   e   c   t       '   t   e
0000100   s   t       3   '       f   r   o   m       d   u   a   l   ;
0000120  \n   s   e   l   e   c   t       '   t   e   s   t       4   '
0000140       f   r   o   m       d   u   a   l   ;  \n   s   e   l   e
0000160   c   t       '   t   e   s   t       5   '       f   r   o   m
0000200       d   u   a   l   ;  \n   s   e   l   e   c   t       '   t
0000220   e   s   t       6   '       f   r   o   m       d   u   a   l
0000240   ;  \n   s   e   l   e   c   t       '   t   e   s   t       7
0000260   '       f   r   o   m       d   u   a   l   ;  \n   s   e   l
0000300   e   c   t       '   t   e   s   t       8   '       f   r   o
0000320   m       d   u   a   l   ;  \n   s   e   l   e   c   t       '
0000340   t   e   s   t       9   '       f   r   o   m       d   u   a
0000360   l   ;  \n   s   e   l   e   c   t       '   t   e   s   t    
0000400   1   0   '       f   r   o   m       d   u   a   l   ;  \n
0000417

Ooo OK..

Ermm.. Maybe that '\n' (newline) is the culprit i.e. in my case, after the 1st ';', the second line is not interpreted as a new line I guess.

So, any idea to overcome this?

Thanks.
Re: Semicolon ; is not interpreted correctly.. :( [message #452082 is a reply to message #452081] Mon, 19 April 2010 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
>So, any idea to overcome this?
I (we) don't know EXACTLY what you have.

post the results for YOUR file

od -c your.sql
Re: Semicolon ; is not interpreted correctly.. :( [message #452083 is a reply to message #452082] Mon, 19 April 2010 23:12 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
BlackSwan wrote on Tue, 20 April 2010 12:01
>So, any idea to overcome this?
I (we) don't know EXACTLY what you have.

post the results for YOUR file

od -c your.sql

OK thanks.

So here it is..

NIS CEL | od -c test.sql
0000000   s   e   l   e   c   t       '   t   e   s   t       1   '
0000020   f   r   o   m       d   u   a   l   ;  \n   s   e   l   e   c
0000040   t       '   t   e   s   t       2   '       f   r   o   m
0000060   d   u   a   l   ;  \n   s   e   l   e   c   t       '   t   e
0000100   s   t       3   '       f   r   o   m       d   u   a   l   ;
0000120  \n   s   e   l   e   c   t       '   t   e   s   t       4   '
0000140       f   r   o   m       d   u   a   l   ;  \n   s   e   l   e
0000160   c   t       '   t   e   s   t       5   '       f   r   o   m
0000200       d   u   a   l   ;  \n   s   e   l   e   c   t       '   t
0000220   e   s   t       6   '       f   r   o   m       d   u   a   l
0000240   ;  \n   s   e   l   e   c   t       '   t   e   s   t       7
0000260   '       f   r   o   m       d   u   a   l   ;  \n   s   e   l
0000300   e   c   t       '   t   e   s   t       8   '       f   r   o
0000320   m       d   u   a   l   ;  \n   s   e   l   e   c   t       '
0000340   t   e   s   t       9   '       f   r   o   m       d   u   a
0000360   l   ;  \n   s   e   l   e   c   t       '   t   e   s   t
0000400   1   0   '       f   r   o   m       d   u   a   l   ;  \n
0000417                                                                       

Re: Semicolon ; is not interpreted correctly.. :( [message #452084 is a reply to message #452073] Mon, 19 April 2010 23:16 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
But it works perfect when I issue this

SQL> start test.sql


'TEST1
------
test 1



'TEST2
------
test 2



'TEST3
------
test 3



'TEST4
------
test 4



'TEST5
------
test 5



'TEST6
------
test 6



'TEST7
------
test 7



'TEST8
------
test 8



'TEST9
------
test 9



'TEST10
-------
test 10                   


But I can't do this..
SQL> @test.sql
SP2-0042: unknown command "test.sql" - rest of line ignored. 


Anyway, my initial problem is when I directly paste the command into the SQL prompt.
Re: Semicolon ; is not interpreted correctly.. :( [message #452085 is a reply to message #452083] Mon, 19 April 2010 23:19 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
Your file matches mine so the problem is NOT due to input to sqlplus.

Therefore I suspect the problem is due to how sqlplus is configured.
Re: Semicolon ; is not interpreted correctly.. :( [message #452086 is a reply to message #452085] Mon, 19 April 2010 23:26 Go to previous message
aimy
Messages: 209
Registered: June 2006
Senior Member
BlackSwan wrote on Tue, 20 April 2010 12:19
Your file matches mine so the problem is NOT due to input to sqlplus.

Therefore I suspect the problem is due to how sqlplus is configured.

Oh God..

You are very very right!

This is actually due to the SET PAUSE ON that I have set.

Thank you so much for your assistance! Really appreciate.

I've been looking for a solution to this problem like hell.. Sad

[Updated on: Mon, 19 April 2010 23:34]

Report message to a moderator

Previous Topic: SQL Output
Next Topic: SP2-0317: expected symbol name is missing
Goto Forum:
  


Current Time: Thu Aug 22 19:27:10 CDT 2019