A short way to execute a dynamic SQL string I have a table, and a column with a query in it. And I want to update this table, converting the value to the result of it's execution.

This is a well known example of such table actually: all_tab_partitions. It has a column HIGH_VALUE with values like:

TO_DATE('2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS').


One could extract the date via substrings or regexps, but may be Oracle-guys think of exactly converting it via dynamic execution.
I've found a rather straight way to do this:

CREATE OR REPLACE FUNCTION PARSE_(STR VARCHAR2)
RETURN DATE
IS
    c NUMBER;
    r INTEGER;
    val DATE;
BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(c, 'SELECT ' || str || ' FROM DUAL', DBMS_SQL.NATIVE);
    DBMS_SQL.DEFINE_COLUMN(c, 1, val);

    r := DBMS_SQL.EXECUTE(c);
    LOOP
        IF DBMS_SQL.FETCH_ROWS(c) &gt; 0 THEN
            -- get column values of the row
            DBMS_SQL.COLUMN_VALUE(c, 1, val);
        ELSE
            EXIT;
        END IF;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(c);

    RETURN val;

EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
        DBMS_SQL.CLOSE_CURSOR(c);
    END IF;

    RETURN NULL;
END;
/

SELECT PARSE_('TO_DATE(''2016-01-01 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')') AS d FROM DUAL;
-- 01.01.2016


And if you want to use it with an all_tab_partitions.HIGH_VALUE, the HIGH_VALUE field has to be converted previously. I've made this in 2 step:


create temp table with TO_LOB(HIGH_VALUE) AS HV field
select from this temp table with PARSE_(TO_CHAR(HV)).