Re: Oracle Auto_Increment Error

Roger, You can see the real Oracle error number if you replace the getMessage() calls with getDebugInfo() or getUserInfo(). Making this change gave this output: Connecting . . . CREATE TRIGGER test_trigger before insert on test for each row begin

     select test_seq.nextval into :new.id from dual
     [nativecode=ORA-24344: success with compilation error ]

The ORA-24344 indicates the PL/SQL compiler has put more diagnostics in the ALL_ERRORS table. Querying from the ALL_ERRORS table (actually I logged into SQL*Plus and did SHOW ERRORS TRIGGER TEST_TRIGGER) lead me to see that you are missing "; end;" off the create trigger command. The statement that worked for me is:

    $sql = "CREATE TRIGGER test_trigger before insert on test for each
              row begin select test_seq.nextval into :new.id from dual; end;";

Note the semi-colon in the string after the "end" keyword. It is needed because the CREATE TRIGGER command uses the PL/SQL language. Regards, Chris Roger Spears wrote:

Hello, BACKGROUND: I'm able to create tables in oracle, populate fields in oracle tables, select, retrieve and update fields in oracle. So I know my connection code is working. PROBLEM: I'm working with PHP and PEAR and I'm trying to create an auto_increment field in an oracle database table. The table has 3 fields and they are id, firstName, and lastName. Below I have included my code for creating, sequencing and triggering the table. I'm also including my insert statement which is currently creating an error. All I get is "DB Error: unknown error". Can anyone please tell me why this error is being generated? CODE: //################ // This will create table //################ $sql = "CREATE TABLE test (id INT NOT NULL PRIMARY KEY, firstName VARCHAR(255) NOT NULL, lastName VARCHAR(255) NOT NULL)"; $result = $db->query($sql); if (DB::isError($result)) { die ($result->getMessage()); } //######## // sequence //######## $sql = "CREATE SEQUENCE test_seq start with 1 increment by 1 nomaxvalue"; $result = $db->query($sql); if (DB::isError($result)) { die ($result->getMessage()); } //############ // trigger //############ $sql = "CREATE TRIGGER test_trigger before insert on test for each row begin select test_seq.nextval into :new.id from dual"; $result = $db->query($sql); if (DB::isError($result)) { die ($result->getMessage()); } //######### // insert //######### $sql = "INSERT INTO test (firstName,lastName) VALUES ('Roger', 'Dodger')"; $result = $db->query($sql); if (DB::isError($result)) { die ($result->getMessage()); } Thanks in advance, Roger