ORA-00927
ORA-00927: недостает знака равенства
Причина:
Знак равенства пропущен в одном из следующих мест:
- в SET предложении оператора UPDATE
- в условии поиска для определения неравенства.
Действие:
Проверьте синтаксис, вставьте знак равенства, где это требуется, затем выполните выражение снова.
Im creating my first sql trigger,
CREATE OR REPLACE TRIGGER totalsalary
AFTER INSERT ON Employee
FOR EACH ROW
WHEN ( NEW.Dno IS NOT NULL )
BEGIN
UPDATE Department
SET totalSalary totalSalary + NEW.salary
WHERE Dno = NEW.Dno;
END
;
but i got this error message and i dont know how to fix it
Error at line 3: PL/SQL: ORA-00927: missing equal sign
1. CREATE OR REPLACE TRIGGER SueldoTotal
2. AFTER INSERT ON EMPLEADO
3. FOR EACH ROW
4. WHEN ( NEW.Dno IS NOT NULL )
5. BEGIN
asked May 15, 2015 at 4:32
SET totalSalary totalSalary + NEW.salary
You have a missing equal sign in the SET clause.
CREATE OR REPLACE TRIGGER totalsalary
AFTER INSERT ON Employee
FOR EACH ROW
WHEN ( NEW.Dno IS NOT NULL )
BEGIN
UPDATE Department
SET totalSalary = totalSalary + :NEW.salary
WHERE Dno = :NEW.Dno;
END;
/
NEW.salary
Also, this is incorrect while referencing OLD and NEW values:
:NEW.salary
answered May 15, 2015 at 4:39
Lalit Kumar BLalit Kumar B
47.3k13 gold badges96 silver badges123 bronze badges
0
ORA-00927: missing equal sign error occurs when the equal sign is missing in the expression in the sql query’s WHERE clause or SET clause. The equal sign is used to assign a value. In the SET clause, the equal sign is used to check for equality, and in the WHERE clause, the equal sign is used to check for equality. Oracle could not assign a value or validate the equality of the values if the equal sign was missing in the SET or WHERE clause. The error ORA-00927: missing equal sign will be thrown if the equal sign is not present in the WHERE clause or the SET clause.
The equal symbol ( = ) is used to equalize the values in the expression. In the assignment operator, the equal sign is used. The assignment operator is used to assign a value or variable value to another variable. In the SET clause of an update statement, Oracle will use the equal sign as the assignment operator. The equal sign is used to compare the equality of two variables’ values. In relational operators, the equal sign is used. The two relational operators used to check equality in Oracle are equals to and not equals to. The error ORA-00927: missing equal sign will be thrown if the equal sign is not present.
When the ORA-00927 error occurs
The error will be thrown if the equal sign is not used or is missing in either the assignment or relational operators. If you write a sql query, such as an update or select query, and the equal sign is missing, Oracle will look for it to validate the query. In the sql query, the equal sign is missing. The error message ORA-00927: missing equal sign will be shown.
Problem
select * from employee where id !5;Error
ORA-00927: missing equal sign
00927. 00000 - "missing equal sign"
*Cause:
*Action:
Error at Line: 30 Column: 34Root Cause
If the equal sign is missing in the SET clause of an update statement, the value in the table cannot be updated. The equal sign indicates which column’s value should be changed. The column and value could not be mapped if the equal sign was not present. Oracle could not determine which relational operator should be used in between operands if the equal sign was missing in the WHERE clause.
Solution 1
If the equal sign is missing in a select statement’s WHERE clause, the value cannot be compared to another variable or value. In the sql query, the relational operator will fail to execute. To compare two values in the sql query, if the equal sign is missing in the relational operator, the equal sign should be added. This will resolve the error ORA-00927: missing equal sign.
Problem
select * from employee where id !5;
ORA-00927: missing equal sign
00927. 00000 - "missing equal sign"Solution
select * from employee where id !=5;Solution 2
If the equal sign is missing in the SET clause of an update statement, the value cannot be assigned to a table column. The update statement will seek for the assignment operator to assign a value to a table column. The update statement will fail to execute since the assignment has not been completed. If the equal sign is added in between the value and the column name in the SET clause of a update statement, the error ORA-00927: missing equal sign will be resolved.
Problem
update employee set name 'kim' where id=4;
ORA-00927: missing equal sign
00927. 00000 - "missing equal sign"Solution
update employee set name = 'kim' where id=4;Solution 3
The error message ORA-00927: missing equal sign will be displayed if there is a syntax problem in the SET clause or the WHERE clause of the sql query. The error message will be misleading in this scenario. The error will be fixed if the syntax error in the update and select statement is corrected. The following example illustrates an update statement with a syntax error.
Problem
update employee set nvl(name ='kim' where id=4;
ORA-00927: missing equal sign
00927. 00000 - "missing equal sign"Solution
update employee set nvl(name) ='kim' where id=4;
Learn the cause and how to resolve the ORA-00927 error message in Oracle.
Description
When you encounter an ORA-00927 error, the following error message will appear:
- ORA-00927: missing equal sign
Cause
You tried to execute a statement, but missed an equal sign. This can happen in either the SET clause of a UPDATE statement or in a search condition.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
If this error occurred in the SET clause of an UPDATE statement, add the missing equal sign and re-execute the statement.
For example, if you tried to execute the following:
UPDATE suppliers SET supplier_name 'IBM' WHERE supplier_id = 1000;
You would receive the following error message:
You could correct this error by adding the missing equal sign, as follows:
UPDATE suppliers SET supplier_name = 'IBM' WHERE supplier_id = 1000;
Option #2
If this error occurred in a search condition when you are trying to determine a «not equals» condition, add the missing equal sign and re-execute the statement.
For example, if you tried to execute the following:
SELECT * FROM suppliers WHERE supplier_id ! 1000;
You would receive the following error message:
You could correct this error by adding the missing equal sign, as follows:
SELECT * FROM suppliers WHERE supplier_id != 1000;
This statement would return all suppliers whose supplier_id is not equal to 1000.
Learn the cause and how to resolve the ORA-00927 error message in Oracle.
Description
When you encounter an ORA-00927 error, the following error message will appear:
- ORA-00927: missing equal sign
Cause
You tried to execute a statement, but missed an equal sign. This can happen in either the SET clause of a UPDATE statement or in a search condition.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
If this error occurred in the SET clause of an UPDATE statement, add the missing equal sign and re-execute the statement.
For example, if you tried to execute the following:
UPDATE suppliers SET supplier_name 'IBM' WHERE supplier_id = 1000;
You would receive the following error message:
You could correct this error by adding the missing equal sign, as follows:
UPDATE suppliers SET supplier_name = 'IBM' WHERE supplier_id = 1000;
Option #2
If this error occurred in a search condition when you are trying to determine a «not equals» condition, add the missing equal sign and re-execute the statement.
For example, if you tried to execute the following:
SELECT * FROM suppliers WHERE supplier_id ! 1000;
You would receive the following error message:
You could correct this error by adding the missing equal sign, as follows:
SELECT * FROM suppliers WHERE supplier_id != 1000;
This statement would return all suppliers whose supplier_id is not equal to 1000.