CODEWITHSUNDEEP
plsqloracle11gparameter-passingproceduretablename
kaksat
kaksat

Reputation: 729

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

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

Upvotes: 0

Views: 318

Answers (1)

Aleksej
Aleksej

Reputation: 22969

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'[, '#', '_')]';

Upvotes: 3

Related Questions