I have a plsql block which I am trying to execute in ODI stored procedure.
DECLARE
v_time_cols VARCHAR2(100) := 'DATA_REFRESH_TIME, UPDATE_DT';
v_sql VARCHAR2(1000);
v_result DATE;
v_out varchar2(100);
BEGIN
SELECT 'SELECT MAX(GREATEST(' ||
LISTAGG('NVL(' || TRIM(REGEXP_SUBSTR(v_time_cols, '[^,]+', 1, LEVEL)) ||
', TO_DATE(''1900-01-01'', ''YYYY-MM-DD''))', ', ')
WITHIN GROUP (ORDER BY LEVEL) ||
')) FROM MY_TABLE'
INTO v_sql
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(v_time_cols, ',') + 1;
EXECUTE IMMEDIATE v_sql into v_out;
DBMS_OUTPUT.PUT_LINE(v_out);
END;
Now, the output of v_out I wanted to store into another ODI variable. Is there any way to do that without involving any temp table in DB?