ORACLE SQL APEX using sequence numbers gives an missing expression error-CodePudding

I created sequence for manager first.

CREATE SEQUENCE manager_seq
START WITH 200
MAXVALUE 299
CYCLE 
CACHE 10;

Then I inserted some values using .NEXTVAL

INSERT INTO manager
VALUES (manager_seq.NEXTVAL,'')

When I try to query with where statement it says ORA-00936: missing expression

select * from manager where number = 201;

Why it isn't working with sequnce numbers how can I use them?

CodePudding user response：

Column name can't be number, that's a reserved word - reserved for datatype. For example:

SQL> create table manager (id     number,
  2                        number number);       --> this
                      number number)
                      *
ERROR at line 2:
ORA-00904: : invalid identifier

Therefore, post table description (so that we could suggest what to do) or use valid query.

CodePudding user response：

If you use a reserved word as an identifier then you need to quote it everywhere you use it. So, if you have the table:

CREATE TABLE manager (
  "NUMBER"  NUMBER,
  something VARCHAR2(10)
);

Then you insert rows:

INSERT INTO manager VALUES (manager_seq.NEXTVAL,'');
INSERT INTO manager VALUES (manager_seq.NEXTVAL,'');

Then, if you use an unquoted identifier:

select * from manager where number = 201;

You get the error:

ORA-00936: missing expression

But, if you use a quoted identifier with the correct case then you can query the table:

select * from manager where "NUMBER" = 201;

Which outputs:

NUMBER SOMETHING

201 null

NUMBER	SOMETHING
201	null

Note: In Oracle, '' and NULL are identical.

db<>fiddle here