Ошибка ora 00936 отсутствует выражение - Ремонт и установка крупной бытовой техники

The following statement:

INSERT INTO TABLE1(COL_1,COL2) VALUES(SELECT MAX(COL_1) FROM TABLE1), 'XYZ');

throws the error:

ERROR at line 1:
ORA-00936: missing expression
 at the select clause. 
1. The table is empty for now.
2. COL_1 is a primary key intger field.

Can you help me please?

abatishchev

97.8k86 gold badges295 silver badges432 bronze badges

asked Nov 2, 2012 at 5:54

Try as

    create table TABLE1 (COL_1 number, COL2 varchar2(5));

ALTER TABLE TABLE1
add CONSTRAINT t_pk PRIMARY KEY (col_1);

INSERT INTO TABLE1(COL_1,COL2) VALUES((SELECT nvl(MAX(COL_1),0) FROM TABLE1), 'XYZ');

INSERT INTO TABLE1(COL_1,COL2) VALUES((SELECT nvl(MAX(COL_1+1),0) FROM TABLE1), 'XYZ');

SQL Fiddle Demo

answered Nov 2, 2012 at 6:06

JacobJacob

14.4k64 gold badges206 silver badges317 bronze badges

If you want to use the result of a query as a scalar expression, enclose the entire (sub)query in brackets, like this:

INSERT INTO TABLE1(COL_1,COL2) VALUES (
   (SELECT MAX(COL_1) FROM TABLE1),
  'XYZ'
);

On the other hand, you could simply use a different syntax, here:

INSERT INTO TABLE1(COL_1,COL2)
SELECT MAX(COL_1), 'XYZ' 
FROM TABLE1
group by  'XYZ';

APC

144k19 gold badges168 silver badges281 bronze badges

answered Nov 2, 2012 at 6:12

Andriy MAndriy M

75.8k17 gold badges94 silver badges153 bronze badges

remove one extra bracket

INSERT INTO TABLE1(COL_1,COL2) SELECT MAX(COL_1) , 'XYZ' FROM TABLE1

answered Nov 2, 2012 at 5:57

BuzzBuzz

5,9304 gold badges33 silver badges47 bronze badges

Источник

Select /*+USE_HASH( a b ) */ to_char(date, 'MM/DD/YYYY HH24:MI:SS') as LABEL,
ltrim(rtrim(substr(oled, 9, 16))) as VALUE,
from rrfh a, rrf b,
where ltrim(rtrim(substr(oled, 1, 9))) = 'stata kish' 
and a.xyz = b.xyz

The «from » (3rd line) part of the above query is giving me ORA-00936 Missing EXPRESSION error. Please Help me

NOTE :: rrfh table contains no data.

Aruna

11.9k3 gold badges28 silver badges42 bronze badges

asked Aug 28, 2012 at 9:29

Remove the comma?

select /*+USE_HASH( a b ) */ to_char(date, 'MM/DD/YYYY HH24:MI:SS') as LABEL,
ltrim(rtrim(substr(oled, 9, 16))) as VALUE
from rrfh a, rrf b
where ltrim(rtrim(substr(oled, 1, 9))) = 'stata kish' 
and a.xyz = b.xyz

Have a look at FROM

SELECTING from multiple tables You can include multiple tables in the
FROM clause by listing the tables with a comma in between each table
name

answered Aug 28, 2012 at 9:32

Adriaan StanderAdriaan Stander

162k30 gold badges288 silver badges284 bronze badges

This answer is not the answer for the above mentioned question but it is related to same topic and might be useful for people searching for same error.

I faced the same error when I executed below mentioned query.

select OR.* from ORDER_REL_STAT OR

problem with above query was OR is keyword so it was expecting other values when I replaced with some other alias it worked fine.

answered Jul 3, 2018 at 7:35

Kishor m nKishor m n

451 silver badge7 bronze badges

update INC.PROV_CSP_DEMO_ADDR_TEMP pd 
set pd.practice_name = (
    select PRSQ_COMMENT FROM INC.CMC_PRSQ_SITE_QA PRSQ
    WHERE PRSQ.PRSQ_MCTR_ITEM = 'PRNM' 
    AND PRSQ.PRAD_ID = pd.provider_id
    AND PRSQ.PRAD_TYPE = pd.prov_addr_type
    AND ROWNUM = 1
)

david

3,2259 gold badges29 silver badges43 bronze badges

answered Oct 10, 2013 at 7:44

user2412576user2412576

511 gold badge1 silver badge4 bronze badges

This happens every time you insert/ update and you don’t use single quotes. When the variable is empty it will result in that error. Fix it by using ''

Assuming the first parameter is an empty variable here is a simple example:

Wrong

nvl( ,0)

Fix

nvl('' ,0)

Put your query into your database software and check it for that error. Generally this is an easy fix

answered Feb 19, 2019 at 12:43

csandreas1csandreas1

2,0061 gold badge26 silver badges47 bronze badges

Источник

ORA-00936

ORA-00936: недостаток выражения

Причина:

Требуемая часть предложения или выражения пропущена. Например, оператор SELECT был введен без списка колонок или выражений, или с незавершенным выражением типа (SAL+). Сообщение об ошибке следует также в тех случаях, где резервное слово пропущено, как в SELECT TABLE.

Действие:

Проверьте синтаксис оператора, и введите пропущенную компоненту.

Источник

Did you get an ORA-00936: missing expression error? Learn what it means and how to resolve it in this article.

ORA-00936 Cause

The error you’ve gotten is this:

ORA-00936: missing expression

Oracle’s official “cause and action” that appears along with the error is:

Cause: A required part of a clause or expression has been omitted.
For example, a SELECT statement may have been entered without a list of columns or
expressions or with an incomplete expression.
This message is also issued in cases where a reserved word is misused, as in SELECT TABLE.

Action: Check the statement syntax and specify the missing component.

So, in summary, the query is missing some clause that it needs in order to run.

To resolve the ORA-00936 error:

Check that your column names are all listed correctly in the SELECT clause
Ensure you have a FROM clause in your SELECT statement. Even if you aren’t selecting from a table, you still need FROM in Oracle SQL, so you could use the DUAL table (LINK)
Remove any commas that shouldn’t be in your query

Missing Columns

The ORA-00936 error often occurs when you leave out the columns in the SELECT clause.

For example:

SELECT
FROM students;

ORA-00936: missing expression

This is because you need to list the column names after the word SELECT and before the word FROM.

This query should work:

SELECT student_id, first_name, last_name
FROM students;

Missing FROM

This error can also occur if you don’t have a FROM keyword in your SELECT statement.

For example, this query will display an error:

SELECT first_name, last_name
WHERE student_id = 5;

There is no FROM clause in this query, so you’ll get an error.

Correct the query to add the FROM clause, so it knows which table to query.

SELECT first_name, last_name
FROM students
WHERE student_id = 5;

Remove Commas

Sometimes you have all of the right keywords, but you’re still getting the ORA-00936: missing expression error.

For example this query gives an error:

SELECT first_name, last_name,
FROM students
WHERE student_id = 5;

The reason for this is because there is a comma after the final column “last_name”, and then there is the FROM keyword.

Commas should only be used when you want to specify another column or table, and not before a keyword like we have in this example.

To correct it, remove the comma.

SELECT first_name, last_name
FROM students
WHERE student_id = 5;

ORA-00936 in UPDATE Statement

If you’re getting an ORA-00936: missing expression in an UPDATE statement, then the same steps can be taken:

Check that you have all the keywords that are required (UPDATE, SET)
Check there are no extra commas where there shouldn’t be.
If you’re using a subquery inside the UPDATE statement, then ensure that subquery has all the right keywords and no extra commas as mentioned earlier

ORA-00936 in INSERT Statement

Just like the UPDATE statement, you can also get an ORA-00936: missing expression in an INSERT statement.

The same steps can be taken:

Check that you have all of the required keywords.
Check there are no extra commas
Check that the number of values and the number of columns are the same
If you’re using a subquery inside the INSERT statement, then ensure that subquery has all the right keywords and no extra commas as mentioned earlier

So, that’s how you resolve the ORA-00936 error in your SQL query. If you have any questions on this error, leave a comment below.

Источник

Description

ORA-00936 : missing expression is one of the common error everybody working in Oracle SQL must have faced some time. This generally happens when you omit important thing in the Sql statement i.e you left out an important chunk of what you were trying to run

ORA-00936 missing expression

Reference : Oracle documentation

This Oracle error is mainly related to the SQL SELECT statements. One obvious reason is select column list is missing or expressions in the selected columns are incomplete.

Check list to run to resolve the ORA-00936 missing expression error

(1) It happens when you forget to list the column in the select statement

Select from mrp_details;

select from mrp_details;
ERROR at line 1:
ORA-00936: missing expression

The correct way would be list the column you want to select

Select col1,col2 from mrp_details;

(2) We sometimes makes mistake in the usage of Distinct statement. Following statement will fail with ORA-00936

select distinct a, b,c,d, distinct e from tab_example
where b=’ABCD’ and c =1 and d= ‘JOHN’
ERROR at line 1:
ORA-00936: missing expression

Having two distinct clause does not make sense and give error

Another example

select a, b,c,d, distinct e from tab_example
where b=’ABCD’ and c =1 and d= ‘JOHN’
ERROR at line 1: ORA-00936: missing expression

distinct can be used in the starting only

So correct statement would be

select distinct a, b,c,d, e from tab_example
where b=’ABCD’ and c =1 and d= ‘JOHN’

(3) This error is caused when part of the expression is omitted , some examples are

select 2**8 from dual; 
select 2**8 from dual;
ERROR at line 1: ORA-00936: missing expression

** operators works in PLSQL but not in SQL, We need to use Power function for it, So correct way would be

select power(2,3) from dual;
POWER(2,3)
--------
8

(4) Another example

select dept_name||' '|| from dept;
select dept_name||' '|| from dept
ERROR at line 1: ORA-00936: missing expression

Here you forget to mention column name after the concatenation operator, the correct SQL would be

select dept_name||' '||dept_no from dept;

(5) When you add extra commas in the list of column

select dept_no, dept_name, ,dept_location from dept_table;
select dept_no, dept_name, ,dept_location from dept_table;
ERROR at line 1: ORA-00936: missing expression

So we need to double check the SQL statement when we hit this error and make sure we are doing the common mistake

(6) This error will also come if you omit the From in the SQL statement

select dept_no, dept_name, ,dept_location where dept_name like ‘A%’;
select dept_no, dept_name, ,dept_location where dept_name like ‘A%’;
ERROR at line 1: ORA-00936: missing expression

Here we missed to mention the from clause.SELECT statement has three parts: to wit: “SELECT->FROM->WHERE
You can omit where clause but select and from are necessary

select dept_no, dept_name, ,dept_location from dept_table where dept_name like ‘A%’;

(7) It can also occurs in insert statement like below

insert into table1 (col1,col2) values as select col1,col2 from table2;
ERROR at line 1: ORA-00936: missing expression

We don’t need values as in this statement

insert into table1 (col1,col2) select col1,col2 from table2;

(8) We can sometimes mix up user-defined functions and Oracle functions, and doing so can lead to confused syntax that would result in an error message.So avoid them

(9) There are Oracle some bugs also
(a) Bug:4567818 base Bug#:4192148 – unpublished on 9207
(b) 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.

In nutshell, ORA-00936 missing expression can be resolved by carefully checking your SQL statement.

Related articles
ORA-00911: invalid character
ORA-03113: end-of-file on communication channel
ORA-00257
ORA-27154: post/wait create failed during startup
ORA-29913 with external tables
ora-20001 in Gather schema stats on 11g(FND_HISTOGRAM_COLS)
Concurrent Manager:cleanup_node failed due to ORA-01427

Источник