Beginner PL/SQL: Return row value from dynamic SQL function

Question

I have a PL/SQL function that successfully compiles:

01 WITH FUNCTION find_CV_errors(
02 l_table_name IN VARCHAR
03 ,l_field_name IN VARCHAR
04 ,l_domain_name IN VARCHAR
05 ) 
06 RETURN VARCHAR
07 AS
08 l_return_value VARCHAR(128);
09 BEGIN
10 
11 EXECUTE IMMEDIATE
12 'SELECT 
13 LISTAGG(CAST(' || l_field_name || ' AS VARCHAR(50)), ' || '''; ''' || ') WITHIN GROUP (ORDER BY NULL)
14 FROM ' ||
15 l_table_name ||
16 ' LEFT JOIN 
17 (
18 SELECT CODE
19 FROM ENG.D_CV_ENG_VW
20 WHERE DOMAIN = :bv1
21 )
22 ON ' || l_field_name || ' = code
23 WHERE ' ||
24 l_field_name || ' IS NOT NULL
25 AND code IS NULL'
26 INTO l_return_value
27 USING l_domain_name;
28 RETURN l_return_value; 
29 END;

The function has a dynamic sql query that outputs a single column in a single row via LISTAGG.

I want the function to return the the dynamic sql output as it's return value. However, right now it just returns NULL, because I haven't assigned a value to the l_return_value variable.

How can I assign the output from the dynamic SQL query to the l_return_value variable?

Update #1:

I've added INTO l_return_value; to the query (LINE 26), but now I'm getting an error:

ORA-06553:PLS-103: Encountered the symbol "L_FIELD_NAME" when expecting one of the following: : = , ( @ % ;

Update #2:

When I remove the semicolon from the end of line 26, the function returns the value of l_field_name, not the value from the dynamic sql query output.

Update #3:

In addition to the errors Balazs Papp pointed out, I had a bind variable on line 13, when really I should have used a concatenated variable. Since Balazs Papp's answer uses generic examples, and not a corrected version of the question, I've corrected the original question myself (lines 07, 13, 26, 27, 28).

• User1974: There's another option here: dba.stackexchange.com/questions/159851/…. It's a loop, rather than a function.
• User1974: And then there's the union idea: dba.stackexchange.com/questions/160758/…

Answer 1

For a single row, use EXECUTE IMMEDIATE ... INTO. A simple example:

set serveroutput on
declare
 l_result number;
begin
 execute immediate 'select count(*) from dual' into l_result;
 dbms_output.put_line(l_result);
end;
/
1

For multiple rows, use EXECUTE IMMEDIATE .. BULK COLLECT INTO, here is an example:

https://stackoverflow.com/questions/21117021/bulk-collect-into-and-execute-immediate-in-oracle

 declare
 type x is table of t.id%type index by pls_integer;
 xx x;
 begin
 execute immediate
 'select id from t' bulk collect into xx;
 dbms_output.put_line(xx.count);
 end;
 /

Ok, another example, with the 12c WITH function:

with function f1 (p_a number) return number
as
 l_result number;
begin
 execute immediate 'select count(*) from dba_objects where object_id = :B1' 
 into l_result
 using p_a;
 return l_result;
end;
select f1(object_id) from dba_objects where object_name = 'HELP';
/
F1(OBJECT_ID)
-------------
 1

• Balazs Papp: @Wilson You have an unnecessary semicolon (;) at the end of line 26.