CODEWITHSUNDEEP
sqloracle-databaseloopsplsql
cifrapalota
cifrapalota

Reputation: 17

How to write a select statement with parameters and iterate it with PL/SQL?

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.

Upvotes: 0

Views: 2336

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

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;

Upvotes: 0

Vecchiasignora
Vecchiasignora

Reputation: 1315

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

Upvotes: 0

Nick Krasnov
Nick Krasnov

Reputation: 27251

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.

Upvotes: 1

Related Questions