Oracle 10gR2 trigger error

Question

I have Oracle 10gR2. I am trying to create autoincrement trigger. Here is the sample:

CREATE SEQUENCE TEST_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
/
CREATE TABLE TESTER  (
ID_TESTER INTEGER NOT NULL,
   VAL VARCHAR2(20) NOT NULL
)
/
CREATE OR REPLACE TRIGGER TIB_TESTER BEFORE INSERT
ON TESTER FOR EACH ROW 
BEGIN
SELECT TEST_SEQ.NEXTVAL 
INTO :NEW.ID_TESTER
FROM DUAL;
END;
/

Trigger creation gives warning:

warning : ORA-24344: success with compilation error

And when I get error value:

select OCI_SUCCESS_WITH_INFO;
/

It gives error:

error : ORA-00923: FROM keyword not found where expected

Answer 1

What client are you using to issue these commands? ORA-24344 is a perculiar error.

In SQL*PLus we can get more information about compilation errors like this:

SQL>  show errors

As for the ORA-00923 error, that is because in Oracle's version of SQL we always have to select from a table. So you should execute

select OCI_SUCCESS_WITH_INFO
from dual
/

I'm not sure how much sense that makes, but at least you won't get the error.

---

"It was Navicat problem"

That doesn't surprise me, as I ran your code against my database and it built without a hitch.