Whats wrong in this code

Get new comments by email
You can cancel email alerts at anytime.
Comments (2)

Mohan in Asia/Pacific Region

66 months ago

I have written the following code..
But it is not getting compiled..
Could anybody guide me what's srong with this code

CREATE OR REPLACE
TRIGGER TEST BEFORE INSERT OR UPDATE ON XXX_DATAPOINTS_BKP_TEST
FOR EACH ROW
DECLARE
v_time varchar2(10);
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET TIME_ZONE=''+2:00''';
select sessiontimezone into v_time from dual;
IF (v_time = '+5:30') THEN
--subtract 3:30 hrs from DATAPOINT_DATETIME filed to insert it relative to GMT +2:00
--:new.datapoint_datetime := :new.datapoint_datetime - (3.5/24);
ELSIF (v_time = '+1:00') THEN
-- add 1:00 hrs to DATAPOINT_DATETIME filed to insert it relative to GMT +2:00
--:new.datapoint_datetime := :new.datapoint_datetime + (1/24);
ELSIF (v_time = '+2:00') THEN
--No thing to do with this timezone
END IF;

END;

- Was this comment helpful? Yes / No Reply - Report abuse

ashu2077 in Ballwin, Missouri

64 months ago

Can you please post the error message when compiling.
Without the message it would take too long to analyze.
I am going to take a stab at it anyway.
In my experience if you use EXECUTE IMMEDIATE once in the code then all other DML and DDL stmt much use EXECUTE IMMEDIATE.
Convert "select sessiontimezone into v_time from dual;" into EXECUTE IMMEDIATE and try it. The syntax for using execute immediate with INTO clause is different then one used just a select. google for syntax if needed.
Let me know what happens.

- Was this comment helpful? Yes / No Reply - Report abuse

» Sign in or create an account to comment on this topic.