On 09/01/12 16:30, Bruce Johnson wrote:
...but I'm trying to do this via a script.
The code:
create or replace trigger resource_key before insert on resources
for each row
begin
select resource_id_seq.nextval into :new.resource_id from dual;
end;
Works in sqlplus, but does not when executed via dbi or via jdbc.
What I end up with instead is the code in the trigger:
create or replace trigger resource_key before insert on resources
for each row
begin
select resource_id_seq.nextval into :new.resource_id from dual
And an error about an invalid sql command for the 'end;' bit.
How do I make this work within dbi?
The following works fine for me:
use DBI;
use strict;
use warnings;
my $h = DBI->connect('dbi:Oracle:host=xxx.yyy.zzz;sid=xxx', 'xxx', 'yyy',
{RaiseError => 1});
my $sql = <<'EOT';
create or replace trigger job_audit_t1 before insert or update on job_audit for
each row
begin
IF INSERTING THEN
select job_audit_seq.nextval INTO :new.job_audit_id from dual;
:new.created_by_user := user;
ELSIF UPDATING THEN
:new.modified_by_user := user;
END IF;
end;
EOT
$h->do($sql);
Show us the code, error and the SQL instead of just the SQL.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
