PL/SQL Beginner – Return Row Value from Dynamic SQL Function

dynamic-sqlfunctionsoracleplsql

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).

Best Answer

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
Related Question