CODEWITHSUNDEEP
oracle-databasesqlplus
Damien Cunningham
Damien Cunningham

Reputation: 25

ORA-00936 missing expression error

I am trying to run the below query for a logon audit but am getting the error below:

ERROR at line 13:ORA-00936: missing expression.

Here is the script. I am new to this and am unclear which expression is missing. Any help would be appreciated.

whenever sqlerror exist rollback
set feed on
set head on
set arraysize 1
set space 1
set verify on
set pages 25
set lines 80
set termout on
clear screen

spool aud_last_logon.lis

undefine number_of_days

col username for a10
col os_username for a10
col timestamp for a9
col logoff_time for a9
col returncode for 9999
col terminal for a10
col userhost for a10


select  a.username,
    os_username,
    a.timestamp,
    a.logoff_time,
    a.returncode,
    terminal,
    userhost
from dba_audit_session a
where (a.username,a.timestamp) in 
    (select b.username,max(b.timestamp)
        from dba_audit_session b
        group by b.username)
and a.timestamp<(sysdate-&&number_of_days)
/

spool off

Upvotes: 2

Views: 444

Answers (1)

Aleksej
Aleksej

Reputation: 22969

The issue could be in the way you're running it; say you have to run this code:

spool d:\temp\spool.txt
undefine x
select &&x from dual
/

spool off

if you simply cut&paste this code into SQLPLUS, you get:

SQL> spool d:\temp\spool.txt
SQL> undefine x
SQL> select &&x from dual
  2  /
Enter value for x:
old   1: select &&x from dual
new   1: select  from dual
select  from dual
        *
ERROR at line 1:
ORA-00936: missing expression


SQL> spool off
SQL>

The reason is that in interactive mode the newline after the / is used as a "value" for x, hence the error.

If you save this code into a file and run it, you get what you need:

SQL> start d:\temp\test.sql
Enter value for x: 9
old   1: select &&x from dual
new   1: select 9 from dual

         9
----------
         9

Upvotes: 0

Related Questions