Re: More of an oracle question than dbi...

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