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