Oracle Function to hardCode Column Name as Parameter

Question

I have 2 tables with same set Column Name (52+ coulmns) . I need to write an Oracle function to compare whether any records get changed between these columns. EMP_ID is the primary Key

I'm trying to use the below function, but it is giving me incorrect result, I'm calling the funcaiton like this:

get_data_change (emp_id, 'DEPT_NAME');
get_data_change (emp_id, 'PHONE_NUMBER');

Function I have created:

CREATE OR REPLACE function  get_data_change (
    in_emp_id varchar2, in_Column_Name varchar2)
return char is

  v_data_changed char;
begin
  select eid, in_Column_Name
    into v_table1_eid, v_table1_Column_Value
    from table 1
   where eid=in_emp_id;

  Select eid, in_Column_Name
    into v_table2_eid, v_table2_Column_Value
    from table 2
   where eid = in_emp_id;


  if ( v_table2_Column_Value != v_table1_Column_Value) 
  then 
    v_data_changed := 'Y'
  else 
    v_data_changed :='N'
  endif
  return v_data_changed 
end
end get_data_change;

As @JeffreyKemp already pointed out, you'll need dynamic SQL for this. Also, your check whether the two values are different (v_table2_column_value != v_table1_column_value) will not work as expected if one of the values is NULL - you need special handling for NULL values. — Frank Schmitt
– Frank Schmitt, Commented Aug 28, 2013 at 8:11

Jeffrey Kemp · Accepted Answer · 2013-08-28 08:04:29Z

2

in_Column_Name is a string variable to which you are assigning a literal string value such as 'DEPT_NAME'.

Therefore, your queries are interpreting this as a literal string value and returning the same thing into v_table1_Column_Value.

To do what you expect you need to use Dynamic SQL, something like:

EXECUTE IMMEDIATE 'select eid, ' || in_Column_Name
               || ' from table1 where eid=:P_emp_id'
into v_table1_eid, v_table1_Column_Value
using in_emp_id;

You need to be aware of the possibility of SQL Injection here - i.e. the value of in_Column_Name cannot be supplied by end-users.

answered Aug 28, 2013 at 8:04

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ragav Over a year ago

Hello Jef, Thanks for the Response. IT WORKED :).

Ragav Over a year ago

i have 2 questions. 1.) what is this :P_emp_id?? whats the impact of this? 2.) i didnt get when u mean in_Column_Name cannot be supplied by end-users? im using the get_data_change (emp_id, 'DEPT_NAME'); to invoke it.. and it works fine. In which Context u referred this.. Appreciate your input on this..Thanks Again for the Solution.

Alex Poole Over a year ago

@balaji - :P_emp_id is a bind variable marker; at run time the using clause puts the value of in_emp_id into that variable. This is more efficient and more importantly prevents a malicious value being supplied. But that only works for values, the column name can't be bound so has to be concatenated. A nasty value of in_Column_Name could make this do something you don't expect. (Search for 'little bobby tables'). You can use dbms_assert.simple_sql_name to reduce the risk, but still be careful.

Ragav Over a year ago

one quick question. can the datatype for this "v_table1_Column_Value" be able to define dynamicallly?? n this case i use DEPT_NAME which is Varcahar2, as well as AGE which is Integar is there a way to define it @ run time?

ibre5041 Over a year ago

You can also compare records on SQL level. ` 'select eid, ' || in_column_name || 'from table1 where eid=:P_emp_id1 interserct select eid, ' || in_column_name || 'from table1 where eid=:P_emp_id2' `. This will return one row only if both records are identical, so you can avoid problems when having different datatypes in your columns

|

Collectives™ on Stack Overflow

Oracle Function to hardCode Column Name as Parameter

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related