-1

So i have this code : 

create or replace TRIGGER tr_after_logon
AFTER LOGON ON SCHEMA

DECLARE

var_date VARCHAR(20);

BEGIN

SELECT TO_CHAR(SYSDATE, 'DD:MM:YYYY HH24:MI:SS')
  INTO var_date FROM DUAL ;

  INSERT INTO activity_log (numero, idsession, action, detail, objet, quand) 

 VALUES (seq_activity.nextval, USERENV('SESSIONID'), 'CONNEXION', ORA_DATABASE_NAME,
  ORA_CLIENT_IP_ADDRESS, TO_DATE(var_date, 'DD:MM:YYYY HH24:MI:SS');

END;

I get the ORA-01830 Error - because i can't convert my CHAR in DATE format right ? I don't manage to solve this problem

Thx for you help in advance !

Nazmul Hasan
  • 10,130
  • 7
  • 50
  • 73
Yannick Berger
  • 3
  • 1
  • 5

1 Answers1

3

I can't see a problem with your code, assuming quand is of type date, but why not just do this?

create or replace TRIGGER tr_after_logon
AFTER LOGON ON SCHEMA

BEGIN

  INSERT INTO activity_log (numero, idsession, action, detail, objet, quand) 

 VALUES (seq_activity.nextval, USERENV('SESSIONID'), 'CONNEXION', ORA_DATABASE_NAME,
  ORA_CLIENT_IP_ADDRESS, SYSDATE);

END;
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • yes you're right 'quand' is of type 'date' ! I know it just work fine with the SYSDATE but I wanted to add the hour:minutes:seconds to my sysdate so that I had better tracability instead of just the date of the day – Yannick Berger Jan 26 '16 at 12:31
  • 4
    @YannickBerger - then you don't understand how dates work in Oracle. If you insert SYSDATE then you get exactly the same value and precision as with your conversion to string and back - where else would the hh:mi:ss be coming from? You just need to specify the format mask when you query your activity table. – Alex Poole Jan 26 '16 at 12:35
  • 1
    @YannickBerger You clearly don't understand Oracle `date` data type. Please help yourself and read e.g. [DATE Data Type](https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#sthref152). – user272735 Jan 26 '16 at 12:36
  • 2
    @YannickBerger - in Oracle DATE values contain the fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. I know the name of the type is DATE, but they're really more of a timestamp-ish kind of thing. Converting a date to a character string doesn't magically add hours:minutes:seconds - they're already there. Best of luck. – Bob Jarvis - Слава Україні Jan 26 '16 at 12:43
  • @BobJarvis Indeed... I didn't see things like this but it make sense now :) thanks for your help (all of you) – Yannick Berger Jan 26 '16 at 12:53