Skip to content
Advertisement

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

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.

Advertisement

Answer

You just need to add a space before set. Currently your table name is appended to set keyword and it is assuming it as a table name i.e MyTableSet

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;
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement