EXECUTE IMMEDIATE PL/SQL Block return type
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;
Jordan Cortes
From Pluto. Web & DB programmer. I love design, music, books and bread. "Efficiency is doing things right; effectiveness is doing the right things".-Peter Drucker
Updated on June 13, 2022Comments
-
Jordan Cortes almost 2 years
As part of a test I want to run a PL/SQL block using
EXECUTE IMMEDIATE
but when I try to fetch the result withINTO
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 byEXECUTE IMMEDIATE
, but I don't know the type. I already tried to changel_output
toCLOB
,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; /