Troubleshoot fix resolve ORA-00936: missing expression

ORA-00936: missing expression an Overview:
This error is related to Oracle expressions which is used in SQL or PL/Sqls . An incomplete or missing expression in DML/DDL will fire ORA-00936 error. A part of a clause or expression has been missed or omitted from the oracle expression. This is mainly related to the SELECT statement.
Some examples
Example #1
SQL> select age+ from emp;
select age+ from emp
            *
ERROR at line 1:
ORA-00936: missing expression
SQL> select name||' '|| from emp;
select name||' '|| from emp
                   *
ERROR at line 1:
ORA-00936: missing expression

SQL> select name||' '|| last_name from emp;

NAME||''||LAST_NAME
-----------------------------------------
James
Toad
John


SQL> select 2**2 from dual;
select 2**2 from dual
         *
ERROR at line 1:
ORA-00936: missing expression
SQL> select power(3,2) from dual;

POWER(3,2)
----------
         9

Example #3
In this example, ** is used instead of power function. ** can be used in PL/SQL for exponential function but not accepted in SQLs

SQL> select mod(8,) from dual;
select mod(8,) from dual
             *
ERROR at line 1:
ORA-00936: missing expression


SQL> select mod(8,3) from dual;

  MOD(8,3)
----------
         2
Example #4
SQL>  insert into example (id) values as select id from TRANS.CONTACT_INFO;
 insert into example (id) values as select id from TRANS.CONTACT_INFO
                                 *
ERROR at line 1:
ORA-00936: missing expression

SQL>  insert into example (id) values (select id from TRANS.CONTACT_INFO);
 insert into example (id) values (select id from TRANS.CONTACT_INFO)
                                  *
ERROR at line 1:
ORA-00936: missing expression

Correct statement is
insert into example (id) select id from TRANS.CONTACT_INFO;

Ora-00936 with IMPDP
                In some cases oracle data pump utility and the job throws the ora-00936 error on existing tables in the target database.
ORA-31693: Table data object "SYSADM"."PS_JOBCODE_TBL" failed to load/unload and is being skipped due to error:
ORA-00936: missing expression

                Cause for this data pump error is either table has LONG column or the table has a unique index and impdp fired with CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=TRUNCATE
This is a data pump restriction and the wrong error message is handled in bug:5598437 and permanently fixed in Oracle 11g. There are some workaround for this.
·         Migrate the LONG columns to CLOB
·         Drop the unique index before impdp on the tables with LONG columns and recreate them once after the impdp.
·         Recreate the table upon import (the table data will be lost)

How to fix ORA-00936 with SQLs
Check the SQL statement expression and correct with proper expression. Check the arguments for the functions mentioned in the expression.


Oracle Bugs related to ORA-00936 error:
There are some oracle bugs related to ORA-00936. Below mentioned are two bugs related to ORA-00936.
1) Bug:4567818 base Bug#:4192148 - unpublished on 9207

2) Bug:4212516 (unpublished) on oracle 10.1.0.4.0.
With these bugs, ORA-00936 error is thrown when the SELECT ON view fails. Basically, ORA-00936 is thrown when a SQL view is created from "create or replace view MY_VIEW as select t.*,other_tab_col from tab t, other_tab". This creates a view definition that is incorrect in the DBA_VIEWS, thus throwing ORA-00936 and possible core dumps.
In order to fix the bugs and resolve ORA-00936, MetaLink offers these solutions for the appropriate version:
Fix for 9.2.0.7 :

Patch 4192148 is available for Solaris (64bit) and AIX5L Based Systems (64-bit).

Fix for 10.1.0.4 :

Patch 4212516 is available for most of the platforms.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google