SQL & PL/SQL

Hi,

I am trying to write the Luhn-10 algorithm in sql query (Oracle 10g). I did tried with below mention query but it is not working for me. I want to find out all the values where check digit is 0.

SELECT mod(ROUND(val + 5, -1) - val,10)
FROM (
SELECT SUM(CASE WHEN MOD(val2, 2) = 1 THEN
CASE WHEN val * 2 > 10 THEN (val * 2) - 9
ELSE val * 2 END
ELSE VAL END) val
FROM (
SELECT TO_NUMBER(SUBSTR('378282246310005', rownum, 1)) val, rownum val2
from dual
CONNECT BY LEVEL <= LENGTH('378282246310005')
order by 2 desc));

Note:- I am able to do this in PL/SQL procedures and functions , However would be very happy to do this in SQL query

MOD10 Logic says that every 2nd digit from right to left should get doubled(or multiply by 2) and the digit doubled if greater than 10, then it should be subtracted by 9 .Finally the sum of all the digits will be divided by 10.

Thanks & regards