By sdohn
Table statements:
ALTER TABLE MP$PATHLOADER.ISALIVE
DROP PRIMARY KEY CASCADE;
DROP TABLE MP$PATHLOADER.ISALIVE CASCADE CONSTRAINTS;
CREATE TABLE MP$PATHLOADER.ISALIVE
(
ISALIVE_PK NUMBER,
INSERTTS TIMESTAMP (6) NOT NULL,
UPDATETS TIMESTAMP (6) NOT NULL,
VERSIONID NUMBER NOT NULL,
NAME VARCHAR2 (256 CHAR) NOT NULL,
ACTIVE NUMBER (1) DEFAULT 0 NOT NULL
);
CREATE UNIQUE INDEX MP$PATHLOADER.ISALIVE_PK
ON MP$PATHLOADER.ISALIVE (ISALIVE_PK);
ALTER TABLE MP$PATHLOADER.ISALIVE
ADD ( CONSTRAINT ISALIVE_PK PRIMARY KEY (ISALIVE_PK)
USING INDEX MP$PATHLOADER.ISALIVE_PK);
A Testvalue as follow:
Insert into MP$PATHLOADER.ISALIVE
(ISALIVE_PK,
INSERTTS, UPDATETS, VERSIONID, NAME, ACTIVE)
Values
(3, TO_TIMESTAMP('01/21/2013 15:29:02,000000','MM/DD/YYYY HH24:MI:SS,FF'), TO_TIMESTAMP('01/24/2013 13:49:40,013000','MM/DD/YYYY HH24:MI:SS,FF'), 8781, '010123vb',
1);
And now the query for getting the differences in minutes between the timestamps:
(SELECT ABS (
TO_NUMBER (TO_CHAR (updatets, 'dd')) * 24 * 60
+ TO_NUMBER (TO_CHAR (updatets, 'hh24')) * 60
+ TO_NUMBER (TO_CHAR (updatets, 'mi'))
- TO_NUMBER (TO_CHAR (SYSDATE, 'dd')) * 24 * 60
- TO_NUMBER (TO_CHAR (SYSDATE, 'hh24')) * 60
- TO_NUMBER (TO_CHAR (SYSDATE, 'mi')))
AS BACKLOG_MINUTES
FROM MP$PATHLOADER.ISALIVE
WHERE active = '1');
But then in a PL/SQL Script this does not further work anymore:
SET SERVEROUTPUT ON
SET FEEDBACK OFF
DECLARE
CTABLES VARCHAR2 (20);
BEGIN
EXECUTE IMMEDIATE
(SELECT ABS (
TO_NUMBER (TO_CHAR (updatets, 'dd')) * 24 * 60
+ TO_NUMBER (TO_CHAR (updatets, 'hh24')) * 60
+ TO_NUMBER (TO_CHAR (updatets, 'mi'))
- TO_NUMBER (TO_CHAR (SYSDATE, 'dd')) * 24 * 60
From: http://www.unix.com/programming/221397-pl-sql-heartbeat-query-has-errors.html