CODEWITHSUNDEEP
oracle-databaseplsqloracle10gtimestamp
HalfWebDev
HalfWebDev

Reputation: 7648

Timestamp subtraction

I am getting the error

PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

When I try to run this procedure. The column EXPIRES in the code below is of type TIMESTAMP(6) in the SESSIONS table. As far code is concerned I am having two consistent datatypes being subtracted from each other. So why this error?

CREATE OR REPLACE PROCEDURE demo_restrict_multlogin (p_login       varchar2,
                                                     p_out     OUT NUMBER,
                                                     p_msg     OUT VARCHAR2)
IS
   vl_val    NUMBER;
   vl_diff  TIMESTAMP(6);
BEGIN
   p_out := 0;

   SELECT   SYStimestamp-EXPIRES
     INTO   vl_diff
     FROM   sessions
    WHERE   LOGIN_DETAILS = p_login;


   SELECT   CASE WHEN COUNT (1) > 0 THEN 1 ELSE 2 END
     INTO   vl_val
     FROM   sessions
    WHERE   LOGIN_DETAILS = p_login AND TO_CHAR (vl_diff, 'mi') > 30;

   IF vl_val = 1
   THEN
      p_msg := 'Pass expired';
   ELSE
      p_msg := 'Pass not expired ';
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      p_out := 1;
END;

Upvotes: 1

Views: 5607

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

When you subtract one value that is of timestamp data type from another one, which is also of timestamp data type, result of that subtraction will be of interval data type, not timestamp, so when you are trying to select/assign a value of interval data type into/to a variable that is of timestamp data type, you will inevitably receive the PL/SQL: ORA-00932:/PLS-00382 error message. A simple solution is to assign the result of timestamp subtraction to a variable of interval data type. To that end you:

  1. Re-declare your vl_diff variable as a variable of interval data type:

    vl_diff interval day to second;

  2. use extract() function to extract minutes from the value assigned to vl_diff variable:

    extract(minute from vl_diff)

    Your second query might look like this:

    select case when count(1) > 0 then 1 else 2 end
  into vl_val
  from sessions
 where login_details = p_login 
   and extract(minute from vl_diff) > 30

Upvotes: 1

Jero Franzani
Jero Franzani

Reputation: 473

Have you tried:

SELECT TRUNC(SYStimestamp - EXPIRES)
  INTO vl_diff
  FROM sessions
 WHERE LOGIN_DETAILS = p_login;

Upvotes: 0

Related Questions