How to pass table name as a parameter in update procedure in Oracle?

Question

I am new to Oracle so please sorry the question that seems to be very easy for you.

I need to get the following procedure with UPDATE query with replace function

CREATE OR REPLACE PROCEDURE proc_replace_space_1
(
    p_table user_tables.table_name%TYPE,
    p_search IN varchar2,
    p_replace IN varchar2
)
IS 
BEGIN 
    EXECUTE IMMEDIATE 
            'update ' || p_table || 
            'set docnum = replace(docnum, :2, :3 )'
            USING p_search, p_replace;
END;

This procedure removes all spaces.

But when I call it

BEGIN 
    proc_replace_space_1('cm_risk.fct_loans_temp', ' ', '');
END;

I've got the following error

SQL Error [971] [42000]: ORA-00971: missing SET keyword
ORA-06512: at "CM_RISK.PROC_REPLACE_SPACE_1", line 9
ORA-06512: at line 2

How can I modify my code to handle the problems?

Thank you.

Answer 1

Dynamic SQL is hard because it turns compilation errors into runtime errors. So I urge you to acquire the good habit of assembling your dynamic SQL as string variables which you can persist to a log table if you have such a thing (and if you don't it would be another good habit to acquire) or display using dbms_output.put_line.

So your procedure would look like this:

CREATE OR REPLACE PROCEDURE proc_replace_space_1
(
    p_table user_tables.table_name%TYPE,
    p_search IN varchar2,
    p_replace IN varchar2
)
IS

  l_sql varchar2(32767);
 
BEGIN 

  l_sql := 'update ' || p_table || 
            'set docnum = replace(docnum, :2, :3 )';

    EXECUTE IMMEDIATE 
            l_stmt
            USING p_search, p_replace;

exception
  when others then
    dbms_output.put_line(l_sql);
    raise;
END;

This approach allows you to see the actual SQL your procedure executed. Probably you'll be able to spot the syntax error immediately (in this case it's the missing space between table name and set). Otherwise you can try to run the statement for yourself and see what the SQL compiler highlights.

---

NB: depending on your environment you may need to enable DBMS_OUTPUT before you can see the message.