2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

pl/sql to get end date from start date

I"m trying to create a trigger to get value of validity from plan table and add it to the value of startdate to get the enddate into dblog table.I have written this trigger.

My dblog and plan table schema is like this

desc dblog;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 PLANID                                    NOT NULL NUMBER

 STARTDATE                                          DATE

 ENDDATE                                   NOT NULL DATE 

desc plan;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 PLANID                                    NOT NULL NUMBER

 VALIDITY                                  NOT NULL NUMBER

STARTDATE is SYSDATE.

CREATE OR REPLACE  TRIGGER trg2

BEFORE INSERT

   ON dblog  FOR EACH ROW

DECLARE

   l_startdate date :=  SYSDATE;

   l_enddate date;

   l_validity number;

BEGIN

 SELECT validity INTO l_validity from plan where planid=:new.planid;

 l_endate := l_startdate + l_validity;

 SET :NEW.enddate := l_enddate;

end;

/

it shows following error:

    10/2     PL/SQL: SQL Statement ignored

    10/6     PL/SQL: ORA-00922: missing or invalid option

Am I using wrong concept or what? how can i get this done?

userimage

You have not declared the data type of the variable, SYSDATE is an inbuilt function and not a data type.

If you want to assign the value at the time of data type declaration, then do it as:

DECLARE

   l_startdate DATE := SYSDATE;

For example,

SQL> set serveroutput on

SQL> DECLARE

  2    start_dt DATE := SYSDATE;

  3  BEGIN

  4    dbms_output.put_line(start_dt);

  5  END;

  6  /

29-SEP-15

PL/SQL procedure successfully completed.

SQL>

The below query will fail:

SELECT validity INTO l_validity from plan where planid=:new.planid;

since your trigger is defined on dblog table, however, you are referring to the :new values for plantable.

Also,

SET :NEW.enddate := l_enddate;

You don't need the SET clause, simply do it as:

:NEW.enddate := l_enddate;

Answer is