0

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

2

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
  • 1
    @Wilson You have an unnecessary semicolon (;) at the end of line 26. – Balazs Papp Jan 16 '17 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.