Search code examples
plsqlplsqldeveloperplsql-package

Passing Variable in Sum Function in PLSQL

This is the simple code to just calculate the sum of a column in a table. However, the column name is dynamic each time it is changed. for that, I tried to do the following code but it is not working when passing the variable in the function, but if I passed hardcoded value then it is working.

DECLARE
v_col varchar2(100);
v_sum varchar2(100);
begin
v_col:='"15-JAN-2012"';
dbms_output.put_line(v_col);
SELECT SUM(v_col) INTO v_sum from BAL_HISTORY;
dbms_output.put_line(v_sum);
end;

This is Error: ORA-01722: invalid number ORA-06512: at line 7

Solution

  • You need to use dynamic sql:

    DECLARE
v_col varchar2(100);
v_sum varchar2(100);
begin
v_col:='"15-JAN-2012"';
dbms_output.put_line(v_col);
EXECUTE IMMEDIATE 'SELECT SUM('||v_col||')  from BAL_HISTORY' INTO v_sum;
dbms_output.put_line(v_sum);
end;

    BTW: database designed that way looks strange to me, but code above will work...