CODEWITHSUNDEEP
sqloracle-databaseplsqltriggerstimestamp
user2801653
user2801653

Reputation: 37

getting error while inserting values into table?

Table structure is 

Name          Null Type         
------------- ---- ------------ 
T_NO               NUMBER       
T_NAME             VARCHAR2(10) 
ENTERING_TIME      TIMESTAMP(6) 
LEAVING_TIME       TIMESTAMP(6) 
TO_DATE            DATE

Trigger

create or replace trigger t4
before insert
on t4
for each row
declare
d_entering_time timestamp(6):=to_char('09:00:00AM','HH12:MM:SSAM');
begin
if (:new.entering_time <= d_entering_time)  then
raise_application_error
(-20002,'Date of joining cannot be after system date.');
end if;
end;

and my inserting query

insert INTO t3 (entering_time) values ( TO_date('8:31:51AM','HH:MI:SSAM'))

I am getting the following error:

SQL Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYSTEM.T3", line 2
ORA-04088: error during execution of trigger 'SYSTEM.T3'
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

Can any one suggest me where error occuring?

Upvotes: 1

Views: 1536

Answers (2)

Srini V
Srini V

Reputation: 11355

Try this:

CREATE OR REPLACE TRIGGER T4
    BEFORE INSERT
    ON T3
    FOR EACH ROW
DECLARE
    SSSSS_ENTERING_TIME NUMBER := 32400;
BEGIN
    IF ( TO_NUMBER ( TO_CHAR ( :NEW.ENTERING_TIME,
                          'SSSSS' ) ) <= SSSSS_ENTERING_TIME )
    THEN
        RAISE_APPLICATION_ERROR (
                             -20002,
                             'Date of joining cannot be after system date.' );
    END IF;
END;

INSERT INTO
      T3 ( ENTERING_TIME )
VALUES
      ( TO_TIMESTAMP ( '01/01/2010 8:31:51AM',
                    'DD/MM/RR HH:MI:SSAM.FF' ) );

Note: I have extracted the total seconds from the time part and converted to a number for comparing. Hence I used 32400 seconds which is nothing but the actual 9 AM.

In Oracle we can turn dates into numbers and apply arithmetic to them in a variety of ways.

Hence to_char(some_date, 'SSSSS') gives us its time element as the number of seconds since midnight.

Upvotes: 1

Dba
Dba

Reputation: 6639

It looks like there is some errors in your code,

  • You are trying to store a string literal into to timestamp variable.

d_entering_time timestamp(6):=to_char('09:00:00AM','HH12:MM:SSAM');

  • It's HH12:MI:SSAM, not HH12:MM:SSAM, MI is for minute and MM is for month.

You can try like this,

    CREATE OR REPLACE TRIGGER t4 
    BEFORE INSERT ON t3 FOR EACH ROW 
    DECLARE 
         d_entering_time TIMESTAMP :=to_timestamp('09:00:00AM','HH12:MI:SSAM.FF');
    BEGIN
         IF (:NEW.entering_time <= d_entering_time) THEN
              raise_application_error (-20002,'Date of joining cannot be after system date.');
         END IF;
    END;

Insert query,

   INSERT INTO t3 (entering_time) VALUES ( to_timestamp('8:31:51AM','HH:MI:SSAM.FF'));

Upvotes: 1

Related Questions