Variable field name

[jdmartin74]

I have a function which is passed a value. I wish to make up a field name based on that value:

v_field := 'ADDRESS_' || p_line;

The above line would give v_field a value of 'ADDRESS_1' for example.

I then want to do:

SELECT v_field INTO v_address from COMPANIES;

This works in so far as the value of v_address becomes 'ADDRESS_1' in this case, and not the value of that field if I had:

SELECT ADDRESS_1 INTO v_address from COMPANIES;

Does anyone know how I can in affect use a variable field name?

Thanks

 

[SantaMufasa]

JD,

Consider this code:

select * from companies;

NAME            ADDRESS_1       ADDRESS_2
--------------- --------------- ---------------
Ajax            123 Anystreet   Anytown, USA

declare
    sql_stm   varchar2(100);
    v_field   varchar2(30);
    v_address varchar2(100);
begin
    for p_line in 1..2 loop
        v_field := 'ADDRESS_' || p_line;
        sql_stm := 'SELECT '||v_field||' from COMPANIES';
        execute immediate sql_stm into v_address;
        dbms_output.put_line(v_address);
    end loop;
end;
/

123 Anystreet
Anytown, USA

PL/SQL procedure successfully completed.

Let us know if this model provides you the capabilities for which you are looking.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[jdmartin74]

I'm not in a loop, but the lines:

sql_stm := 'SELECT '||v_field||' from COMPANIES';
execute immediate sql_stm into v_address;

are exactly what I needed.

Thanks so much.