0

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?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.