PL/SQL: procedure with table and column name as parameters

Question

I would like to write a procedure in PL\SQL, which would change the special characters (in the example below: a hash) to a apriori declared characters (in the example below: an underscore). The procedure should take a table name and a column name as parameters. I wrote such a code, however, it doesn't work:

create or replace procedure change_chars(table_name in varchar2, column_name in varchar2)
    begin
        execute immediate 'update ' || table_name ||
        ' set ' || column_name || ' = replace(' || column_name ||', '''#''', '''_''')';
    end;

Any help would be appreciated

Answer 1

You're missing the IS just before the BEGIN and you have too many quotes in your string:

create or replace procedure change_chars(table_name in varchar2, column_name in varchar2) is
begin
    execute immediate 'update ' || table_name ||
    ' set ' || column_name || ' = replace(' || column_name ||', ''#'', ''_'')';
end;

To handle strings with quotes, you could use the Q operator instead:

' set ' || column_name || ' = replace(' || column_name || q'[, '#', '_')]';