topic Re: SQLPlus ORA-00922: missing or invalid option in Operating System - HP-UX

SQLPlus ORA-00922: missing or invalid option

Irma Fisher — Fri, 08 Aug 2008 16:18:25 GMT

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

Re: SQLPlus ORA-00922: missing or invalid option

Jeff_Traigle — Fri, 08 Aug 2008 16:48:15 GMT

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

Re: SQLPlus ORA-00922: missing or invalid option

Patti Johnson — Fri, 08 Aug 2008 16:49:26 GMT

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

Re: SQLPlus ORA-00922: missing or invalid option

Yogeeraj_1 — Fri, 08 Aug 2008 16:50:05 GMT

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

Re: SQLPlus ORA-00922: missing or invalid option

Irma Fisher — Fri, 08 Aug 2008 17:41:41 GMT

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.