PL/SQL: iterator value from FOR loop as a variable not working-CodePudding

I don't understand behaviour of my code. Why when I have select min(i.column_name) into value_min from EMPLOYEES; in my code (see belowe) I get error but if i put in this palce string with correct name of table I get result select min(SALARY) into value_min from EMPLOYEES;


declare 
    cursor c_column_name is select column_name, data_type from USER_TAB_COLUMNS where table_name = 'EMPLOYEES';
/***************************
(...)
SALARY          NUMBER
(...)
***************************/    
    value_name_col varchar2(100);
    value_min number;
    value_max number;
    value_result number;
    type t_rekord is table of EMPLOYEES%ROWTYPE INDEX BY BINARY_INTEGER;    

begin
    for i IN c_column_name loop
    --DBMS_OUTPUT.PUT_LINE(i.column_name);
        if i.data_type = 'NUMBER' and i.column_name = 'SALARY' then 

--        coment line get ERROR    
--        select min(i.column_name) into value_min from EMPLOYEES; 
--        select max(i.column_name) into value_max from EMPLOYEES;

--      code below get results
        select min(SALARY) into value_min from EMPLOYEES; 
        select max(SALARY) into value_max from EMPLOYEES;

        select round(dbms_random.value(value_min,value_max),2) into value_result from dual;
        DBMS_OUTPUT.PUT_LINE(i.column_name || ' = ' || value_result);
        end if;
    end loop;
end;

Error which I get:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 27
06502. 00000 -  "PL/SQL: numeric or value error%s"

I prepare this for show you how my problem look like, i'ts not exactly what I do.

CodePudding user response：

If you want to specify table or column names using a variable, you need dynamic SQL (e.g. with execute immediate) as Jeff Holt commented.

Try this instead:

execute immediate 'select min(' || i.column_name 
  || '), max(' || i.column_name 
  || ') from EMPLOYEES' into value_min, value_max;

You're getting a "numeric or value error" because it's doing select min('SALARY') and then it tries to save the output (the string 'SALARY') into value_min, which is a NUMBER data type.