oci_execute(): OCI_SUCCESS_WITH_INFO: ORA-24344 using a trigger/sequence to auto increment an oracle table error

Question

I'm using a sequence and trigger to essentially auto increment a column in a table, however I'm getting an error - ORA-24344: success with compilation error.

I was using this post: How to create id with AUTO_INCREMENT on Oracle? and it worked successfully for two other tables w/ auto increment I made, but there must be something in here I'm not familiar with causing an error.

More edits: Thanks to Polppan we've established that this likely isn't an Oracle issue, rather an OCI with PHP issue. I'm using:

oci_execute($sql);

And as mentioned here (again, thanks Polppan for that link), there's a bit of an issue between EOL characters and oci_execute. It was 11 years ago, so I don't know if that's been patched or not, and I did try his solution but it didn't help. Does anyone know if there are other issues with oci_execute and creating triggers?

Creating the table: (works)

CREATE TABLE RT_documents (
  documentID INT NOT NULL, 
  reviewID varchar2(20) NOT NULL, 
  file_location CLOB NOT NULL,
  version NUMBER(*,3) NOT NULL,
  CONSTRAINT RT_documents_pk PRIMARY KEY (documentID)
)

Creating the sequence: (works)

CREATE SEQUENCE rt_documents_seq

Creating/replacing trigger: (doesn't work)

CREATE OR REPLACE TRIGGER rt_documents_bir
BEFORE INSERT ON RT_documents
FOR EACH ROW

BEGIN
    SELECT RT_documents_seq.NEXTVAL
    INTO :new.documentID
    FROM dual;
END;

EDIT: Exact error message as requested - (Note, I'm executing these query-by-query using OCI/Oracle in PHP. PHP tag added just in case, but pretty sure this is an oracle syntax error or something).

Error:

Notice: oci_execute(): OCI_SUCCESS_WITH_INFO: ORA-24344: success with compilation error in (...)

-I can successfully execute the first two queries, and double checked and the table is there so it worked properly.

Answer 1

Trigger doesn't understand new.id as id doesn't exist in RT_documents table.

Your trigger should be

CREATE OR REPLACE TRIGGER rt_documents_bir
   BEFORE INSERT
   ON RT_documents
   FOR EACH ROW
BEGIN
   SELECT RT_documents_seq.NEXTVAL INTO :new.documentID FROM DUAL;
END;

Update

SELECT * FROM v$version;

Oracle Database 10g Enterprise Edition

CREATE TABLE RT_documents
(
   documentID      INT NOT NULL,
   reviewID        VARCHAR2 (20) NOT NULL,
   file_location   CLOB NOT NULL,
   version         NUMBER (*, 3) NOT NULL,
   CONSTRAINT RT_documents_pk PRIMARY KEY (documentID)
);

Table created.

CREATE SEQUENCE rt_documents_seq;

Sequence created.

CREATE OR REPLACE TRIGGER rt_documents_bir
   BEFORE INSERT
   ON RT_documents
   FOR EACH ROW
BEGIN
   SELECT RT_documents_seq.NEXTVAL INTO :new.documentID FROM DUAL;
END;
/

Trigger created.

INSERT INTO RT_documents (reviewID, file_location, version)
 VALUES ('test', 'test', 1);

1 row created.


SELECT * FROM RT_documents;

DOCUMENTID REVIEWID             FILE_LOCATION
                                    VERSION
---------- -------------------- -------------------------------------------
-------------------------------- ----------
         1 test                 test
                                          1

Answer 2

Thanks to Polppan.

The solution was removing the EOL characters. (I did try this but had, without realising, removed the semi-colons, which caused the same error code)

This was a PHP error after all. Using oci_execute, you must remove EOL characters in triggers:

$sql = "CREATE OR REPLACE TRIGGER ......."; //shortened for easy reading
$sql = str_replace(chr(13),'',$sql);
$sql = str_replace(chr(10),'',$sql);
oci_execute($sql);