Remove Oracle Job Process

Trying to kill a database job can be frustrating sometimes and they just won't die if you don;t do it right, especially if the job is owned by a different user.

So first identify the job you are looking for using:

SELECT * FROM dba_jobs ;

Then use DBMS_IJOB to remove the job. Use the value from the JOB column above as the parameter value.

EXEC SYS.DBMS_IJOB.REMOVE(?);

I'm using Oracle 11 but I believe that, although this is undocumented, this has been around for several years now.

Removed.

Oracle REGEXP to Strip Numbers From a String

This example uses the Oracle REGEXP functions to check if a string contains numbers and also strip away unwanted characters. Unful to prevent errors when converting strings to numbers with TO_NUMBER.

WITH testData AS ( SELECT '12ab34' theString FROM DUAL UNION ALL SELECT 'a12b34c' FROM DUAL UNION ALL SELECT '%1$AB2.34' FROM DUAL UNION ALL SELECT 'abcd' FROM DUAL UNION ALL SELECT '12.34' FROM DUAL UNION ALL SELECT '1234' FROM DUAL ) SELECT theString, REGEXP_REPLACE(theString,'[^0-9]') justNumbers, REGEXP_REPLACE(theString,'[^0-9\.]') numbersAndDecimal, (CASE REGEXP_INSTR(theString,'[0-9]') WHEN 0 THEN 0 ELSE 1 END) existsCheck, (CASE REGEXP_INSTR(theString,'\D') WHEN 0 THEN 1 ELSE 0 END) onlyNumbersCheck FROM testData ;

Gives the output:

THESTRING JUSTNUMBE NUMBERSAN EXISTSCHECK ONLYNUMBERSCHECK --------- --------- --------- ----------- ---------------- 12ab34 1234 1234 1 0 a12b34c 1234 1234 1 0 %1$AB2.34 1234 12.34 1 0 abcd 0 0 12.34 1234 12.34 1 0 1234 1234 1234 1 1 6 rows selected.