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
How can I assign the output from the dynamic SQL query to the
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: : = , ( @ % ;
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.
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).