I want to create a for loop with parameters in a function with PL/SQL. I want to reach something like this:
CREATE OR REPLACE FUNCTION f_example(p_table, p_column)
BEGIN
FOR v_iter in (EXECUTE IMMEDIATE 'select ' || p_column || ' from ' || p_table) LOOP
DBMS_OUTPUT.PUTLINE(v_iter.p_column);
END LOOP;
END;
Is there a working way to do this? Thank you in advance.
If you really need this sort of dynamics, then use(as one of the options) weakly typed cursor. Here is an example (used dual table in this example).
set serveroutput on;
declare
l_c1 sys_refcursor;
l_query varchar2(255);
--------------------
l_column varchar2(11) := 'dummy'; -- column name
l_tabname varchar2(11) := 'dual'; -- table name
--------------------
l_res varchar2(1);
begin
l_query := 'select ' || dbms_assert.simple_sql_name(l_column) ||
' from ' || dbms_assert.simple_sql_name(l_tabname);
open l_c1 for l_query;
loop
fetch l_c1 into l_res;
exit when l_c1%notfound;
dbms_output.put_line(l_res);
end loop;
end;
result:
X
PL/SQL procedure successfully completed.
Note, that you'd need to know the type of variable(s) (or a collection if you'd decide to use bulk collect into) result would be fetched into, beforehand. Second, since identifiers (e.g. column names, table names) cannot be bound, you'd have to concatenate them explicitly after some sort of validation to prevent SQL injection.
Hope this below snippet helps.
CREATE OR REPLACE
FUNCTION dummy_function_test(
lv_col IN VARCHAR2,
lv_tab IN VARCHAR2)
RETURN
DBMS_SQL.VARCHAR2_TABLE
AS
lv_table DBMS_SQL.VARCHAR2_TABLE;
BEGIN
EXECUTE IMMEDIATE 'select '||lv_col||' from '||lv_tab BULK COLLECT INTO lv_table;
FOR I IN lv_table.FIRST..lv_table.LAST
LOOP
dbms_output.put_line(lv_table(i));
END LOOP;
RETURN lv_table;
END;
DECLARE
lv_1 dbms_sql.varchar2_table;
BEGIN
lv_1:=dummy_function_test('sysdate','dual');
END;
you should use dynamic SQL by using EXECUTE IMMEDIATE like this
CREATE OR REPLACE FUNCTION f_example(p_table, p_column) return .... is
plsql_block varchar2(4000);
begin
plsql_block : = 'BEGIN
FOR v_iter in (select ' || p_column || ' from ' || p_table ||' ) LOOP
DBMS_OUTPUT.PUTLINE(v_iter.p_column);
END LOOP;
END;'
EXECUTE IMMEDIATE plsql_block;
END;
for more information about dynamic sql or using in out params in dynamic sql you can read article below
https://docs.oracle.com/cloud/latest/db112/LNPLS/dynamic.htm#LNPLS011
