EXECUTE IMMEDIATE PL/SQL Block return type

Question

As part of a test I want to run a PL/SQL block using EXECUTE IMMEDIATE but when I try to fetch the result with INTO it always returns the same error regardless the content of the PL/SQL block I want to run.

DECLARE
    l_output    VARCHAR2(10);
BEGIN
    EXECUTE IMMEDIATE 'BEGIN COMMIT; END;' INTO l_output;
END;
/

And the error is

ORA-01007: variable not in select list

I know this error has to with l_output not being the same type as the returning type by EXECUTE IMMEDIATE, but I don't know the type. I already tried to change l_output to CLOB, BLOB, NUMBER and nothing changes. Any idea?

OK, this is another example, same result.

DECLARE
    l_output    VARCHAR2(10);
BEGIN
    EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(''TEST''); END;' INTO l_output;
END;
/

Answer 1

Oracle is complaining because your PL/SQL does not return anything for it to store in l_output. What are you expecting the value of l_output to be?

One would use EXECUTE IMMEDIATE...INTO with something like this to return a value from a PL/SQL block.

DECLARE
    l_output    VARCHAR2(10);
BEGIN
    EXECUTE IMMEDIATE 'SELECT ''ABC'' FROM DUAL' INTO l_output;
    dbms_output.put_line('l_output = ' || l_output);
END;
/

UPDATE

If you want, you can do this:

DECLARE
    l_output    VARCHAR2(10);
BEGIN
    EXECUTE IMMEDIATE 'BEGIN :1 := 5; END;' USING IN OUT l_output;
    dbms_output.put_line('l_output = ' || l_output);
END;