Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

SQLPlus ORA-00922: missing or invalid option

Irma Fisher
Occasional Contributor

SQLPlus ORA-00922: missing or invalid option

I am trying to execute the following sql but keep getting ORA-00922 and it's pointing to the REM statement. Then when I change the REM to -- then it gets the same error on SET HEADING OFF. I am loading the statements to the buffer and then running them with the RUN command. Why doesn't it like either the REM or SET command?


REM * test
SET HEADING OFF
SET LINESIZE 1600
SET WRAP OFF
SET PAGESIZE 0
SET TRIMSPOOL ON
SPOOL ________.txt
SELECT
SUBSTR ( rpad ( nvl ( xx1_policy_reference,' '),030,' '),001,030)||
SUBSTR ( rpad ( nvl ( xx1_policy_quote_indicator,' '),001,' '),001,001)||
'--xx1'
FROM xx2_policies
WHERE xx1_policy_reference LIKE 'P%'
SPOOL OFF
4 REPLIES
Jeff_Traigle
Honored Contributor

Re: SQLPlus ORA-00922: missing or invalid option

You're missing the semi-colon at the end of your SELECT statement.
--
Jeff Traigle
Patti Johnson
Respected Contributor

Re: SQLPlus ORA-00922: missing or invalid option

If you put all you commands in a file and run the file with
start file.sql
or
@file.sql

It will run.

If you paste those same statements into the buffer the set commands will fail.

SQL> ed
Wrote file afiedt.buf
1 SET HEADING OFF
2 SET LINESIZE 1600
3 SET WRAP OFF
4* select * from dual
5 /
SET HEADING OFF
*
ERROR at line 1:
ORA-00922: missing or invalid option

Yogeeraj_1
Honored Contributor

Re: SQLPlus ORA-00922: missing or invalid option

hi,

can you copy and paste the exact error message as displayed in your SQLPLUS.

Also, try to add ';' to the end of this line and retry:

WHERE xx1_policy_reference LIKE 'P%;


revert!
kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Irma Fisher
Occasional Contributor

Re: SQLPlus ORA-00922: missing or invalid option

Thanks to all for your replys.

I did find an article that helped me understand this. It said that sql*plus commands are not stored in the buffer. I could see this when I used the START or @ to run the statements. When I LISTed the buffer contents, I noticed that the sql*plus commands were not loaded therefore, I get a good run. But when I open the command file, it loads both the sql*plus and sql commands into the buffer so when I try to RUN them, I get the error on the sql*plus commands.