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

"it doesn't work" ... catb.org/esr/faqs/smart-questions.html#bespecific ... that said, you have too many single-quotes which means that # and _ are being interpreted as code rather than as part of the string to execute. — Jeffrey Kemp
– Jeffrey Kemp, Commented Feb 2, 2018 at 3:41

Aleksej · Accepted Answer · 2018-02-02 06:41:33Z

3

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

answered Feb 2, 2018 at 6:41

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related