SQLPlus ORA-00922: missing or invalid option

[Irma Fisher]

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

[Jeff_Traigle]

You're missing the semi-colon at the end of your SELECT statement.

[Patti Johnson]

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]

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

[Irma Fisher]

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.