Troubleshoot fix resolve ORA-00936: missing expression

advertisements

_____________________________________________________________________________________________________________________

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.

_____________________________________________________________________________________________________________________

Website Stats