[sloppypoet]

While purging transmissions we received the Oracle error 00920: invalid relational operator. The procedure called to purge transmissions has not been changed so we are confused as to why this would occur. We are using 4.5 on Oracle 9i in a Linux environment.....any help is appreciated.

[Shells]

Has this ever worked before for you guys or are you just now trying to use the purge process? I would not change the existing purge package as 1) next time you install a patch it will overwrite and 2) might cause you more issues in the future.

As a side note -we purge with our own edited procedure/jobs using the below as an example:
Procedure:
CREATE OR REPLACE PROCEDURE Clear_Transmission_Xml(v_status VARCHAR2) AS
v_days_range NUMBER := 21;
v_days_keep NUMBER := 20;
v_count NUMBER := 0;
v_commit_count NUMBER := 200;
v_records_left NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count
FROM I_TRANSMISSION
WHERE status = v_status
AND insert_date >= (TRUNC(SYSDATE) - v_days_range)
AND insert_date <= (TRUNC(SYSDATE) - v_days_keep)
AND xml_blob IS NOT NULL;
IF v_count = 0 THEN
dbms_output.put_line('No records cleared');
ELSE
v_records_left := v_count;
LOOP
IF v_records_left > v_commit_count THEN
UPDATE I_TRANSMISSION
SET xml_blob = NULL
WHERE status = v_status
AND insert_date >= (TRUNC(SYSDATE) - v_days_range)
AND insert_date <= (TRUNC(SYSDATE) - v_days_keep)
AND xml_blob IS NOT NULL
AND ROWNUM <= v_commit_count;
COMMIT;
v_records_left := v_records_left - v_commit_count;
ELSE
UPDATE I_TRANSMISSION
SET xml_blob = NULL
WHERE status = v_status
AND insert_date >= (TRUNC(SYSDATE) - v_days_range)
AND insert_date <= (TRUNC(SYSDATE) - v_days_keep)
AND xml_blob IS NOT NULL;
COMMIT;
EXIT;
END IF;
END LOOP;
END IF;
dbms_output.put_line('Records cleared = '||v_count);
END;
/

Job example:

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'clear_transmission_xml(''PROCESSED'');'
,next_date => to_date('23/07/2007 23:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1) + 23/24'
,no_parse => TRUE
);
END;
/

[sloppypoet]

Shells,

Thanks for the reply. Yes, this has worked in the past. Also, it appears to be working when we run it but still seeing the error. Not sure why.

Thanks for the example. I'll take a look at it to see if something similar will work for us.