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;