Automation Designer Actionable Query Uppercase Bug?

Toad for Oracle
1

I tried to use REGEXP_SUBSTR in an Actionable Query and kept getting errors. I used SQL Tracker to see what was being submitted to the database and the pattern was in uppercase. This caused \d (only digits) to become \D (anything but digits) which broken the code. Code works as expected when run directly from Toad.

I got it working by using a different pattern but this removes all the POSIX classes and all the lower case Perl-Influenced Extensions from use.

2

Here is some code you can paste into an Actionable Query to test.

DECLARE
MY_DATE DATE;
BEGIN
SELECT TO_DATE (REGEXP_SUBSTR ('My import file (05052020).txt', '\d{8}'),
'MMDDYYYY')
INTO MY_DATE
FROM DUAL;
:RESULT:=1;
END;

Gives:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 4

3

I can reproduce that and will fix. Thank you.