Compilation Error

[sujosh]

create trigger ins_uomconv_trig
before insert or update on uom_conversion
for each row

begin
Declare as_xml varchar2(255),as_from_uom varchar2(20),as_to_uom varchar2(20),as_tab_name varchar2(50),as_oper varchar2(20);

as_from_uom = :new.from_uom;
as_to_uom = :new.to_uom;
as_tab_name := 'uom_conversion';
as_oper := 'INSERT';
as_xml := '<from_uom>'+:as_from_uom+'</from_uom>';
as_xml := :as_xml+'<to_uom>'+:as_to_uom+'</to_uom>';
end;
/
When I compile I get the following error.

PLS-00103: Encountered the symbol &quot;DECLARE&quot; when expecting one of the following:

begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cursor
form current external language

Where am I going wrong?

Thanks

 

[carp]

I see two problems:

1. The DECLARE section must occur before BEGIN.
2. Your code doesn't actually do anything (perhaps you omitted it for brevity?).

 

[sujosh]

Thanks for pointing out. I did try that and I am getting the same error. You are right I am calling a procedure from that trigger.

This the snippet of that code.

create trigger ins_uomconv_trig
before insert or update on uom_conversion
for each row

Declare
as_xml varchar2(255);
as_from_uom varchar2(20);
as_to_uom varchar2(20);
as_tab_name varchar2(50);
as_oper varchar2(20);
begin

as_from_uom = :new.from_uom;
as_to_uom = :new.to_uom;
as_tab_name := 'uom_conversion';
as_oper := 'INSERT';
as_xml := '<from_uom>'+:as_from_uom+'</from_uom>';
as_xml := :as_xml+'<to_uom>'+:as_to_uom+'</to_uom>';
-- Call the procedure
end;
/

 

[VSU]

Concatenation operator is '||'. Not '+'.
[tt]
CREATE TRIGGER ins_uomconv_trig
BEFORE INSERT OR UPDATE ON uom_conversion
FOR EACH ROW
DECLARE
as_xml VARCHAR2(255);
as_from_uom VARCHAR2(20);
as_to_uom VARCHAR2(20);
as_tab_name VARCHAR2(50);
as_oper VARCHAR2(20);
BEGIN
as_from_uom := :NEW.from_uom;
as_to_uom := :NEW.to_uom;
as_tab_name := 'uom_conversion';
as_oper := 'INSERT';
as_xml := '<from_uom>' || as_from_uom || '</from_uom>';
as_xml := as_xml || '<to_uom>' || as_to_uom || '</to_uom>';
-- Call the procedure
END;
/
[/tt]
It seams to me that [tt]

IF INSERTING THEN
as_oper := 'INSERT';
ELSIF UPDATING THEN
as_oper := 'UPDATE';
END IF; [/tt]

will be more usefull than [tt]

as_oper := 'INSERT';[/tt]

 

[sujosh]

Hi VSU,

Thank you so much!! I really appreciate it. You have been very helpful. I have been working on SQL Server and Sybase for a long time but not in Oracle. Thanks for your post of that book. Also do you have a tutorial website of yours or would you recommend any about triggers and store procedures? I have written about 30 triggers and few SP for a current project and would like to convert it into Oracle and was wondering if I could get a jumpstart on the basic syntax.

Thanks again for your help VSU