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
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
