A short way to execute a dynamic SQL string

Question

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.

• BriteSponge: Not entirely sure what it is you are trying to achieve. You seem to be suggesting that 'TO_DATE('2016年01月01日 00:00:00', 'YYYY-MM-DD HH24:MI:SS')' is a query when it clearly isn't and you can't just update the all_tab_partitions VIEW. You will use the ALTER TABLE command to modify partitions - docs.oracle.com/cd/B28359_01/server.111/b28286/…

Answer 1

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) > 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:

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

• Andrew Brennan: I would just note that this is rife for an SQL injection attack
• egor7: Definitely it is. I've mentioned the system oracle table as a main target for this query. I would not use it for anything else)