Dynamically assigning variables oracle sql

Question

I have a table attribute_config with below columns:

table_name column_name key

Let us say is has below 2 rows

account accountphone accountnum

customer customernumber customerid

Key can be only accountnum or customerid.

I have to write code which will accept (i_accountnum,i_customerid) and;

fetch the respective values from columns mentioned in column_name in tables mentioned in table_name using the key in where condition.

For ex: select accountphone from account where accountnum = i_accountnum select customernumber from customer where customerid = i_customerid

the complete query should be formed dynamically, whether to pass i_accountnum or i_customerid in the query also needs to be decided dynamically. if key - accountnum, i_accountnum will be passed to where condition.

I have been trying on these lines so far, this is not working, i know it is wrong.

declare
v_accountnum varchar2(20);
v_customerid varchar2(20);
v_attribute_value varchar2(20);
v_stmt varchar2(255);
begin
Account_Num := 'TestCustomer';  -- input to the function
v_customer_ref := 'TestAccount'; -- input to the function
for i in (Select * from attribute_config) loop
v_stmt := 'select ' || i.column_name || ' from ' ||  i.table_name ||' where ' || i.key|| ' = v_' || i.key;
execute immediate v_Stmt into v_attribute_value;
end loop;
end;

Answer 1

This will fix your code, but I do not see any advantage of using dynamic query when your code should accept 2 parameters(i_accountnum,i_customerid) - which is already static situation and fetch the relevant values, perhaps only in learning purposes.

declare
   procedure fecth_values(i_accountnum account.accountnum%type,
                          i_customerid customer.customerid%type) return varchar2 is
      v_attribute_value varchar2(20);
   begin
      for i in (select * from attribute_config) loop
         execute immediate 'select ' || i.column_name || ' from ' ||
                           i.table_name || ' where ' || i.key || ' = ' || case when i.key = 'accountnum' then i_accountnum when i.key = 'customerid' then i_customerid end;
         into v_attribute_value;
         dbms_output.put_line(v_attribute_value);
      end loop;
      return null;
   end;
begin
   fecth_values(1, 1);
end;

Your where clause was wrong the i.key should be compared against the inputed values, not the 'v_' || i.key, which is undeclared when you execute your stmt.