"Execute immediate" problem
Can anyone tell what is the error in this statement.
execute immediate 'Update '||tabs.table_name||' set ID_CTLGRP = REPLACE(ID_CTLGRP,'''','_') WHERE ID_CTLGRP LIKE '%''%'';
I am replacing apostrophes in a field by underscore for a number of tables.
Thanks you
Re: "Execute immediate" problem
Quote:
Originally posted by locus87
Can anyone tell what is the error in this statement.
execute immediate 'Update '||tabs.table_name||' set ID_CTLGRP = REPLACE(ID_CTLGRP,'''','_') WHERE ID_CTLGRP LIKE '%''%'';
I am replacing apostrophes in a field by underscore for a number of tables.
Thanks you
Try this.
Code:
execute immediate 'Update ' || tabs.table_name ||
' set ID_CTLGRP = REPLACE(ID_CTLGRP,chr(39),''_'')' ||
' WHERE ID_CTLGRP LIKE ''%''%'''
"execute immediate" problem
Hi,
Thanks for your help so far. I have tried your suggestion. However it does not work. I still get errors.
My code is below:
set serveroutput on size 1000000;
declare
cursor c1 is
select table_name from cols where column_name = 'ID_CTLGRP' and table_name like 'TUA%';
begin
--loop through tables with field CTLGRP
For tabs in c1 loop
dbms_output.put_line ('Processing Table:'||tabs.table_name);
execute immediate 'Update '|| tabs.table_name ||' set ID_CTLGRP = REPLACE(ID_CTLGRP,chr(39),''_'')'||' WHERE ID_CTLGRP LIKE ''%''%''';
End loop; --tables
dbms_output.put_line ('Job Complete');
end;
Re: "execute immediate" problem
Does this work?
Code:
SET serveroutput on size 1000000;
DECLARE
CURSOR c1
IS SELECT table_name
FROM cols
WHERE column_name = 'ID_CTLGRP' AND
table_name LIKE 'TUA%';
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
--loop through tables with field CTLGRP
FOR v_tabs IN c1
LOOP
DBMS_OUTPUT.put_line( 'Processing Table:' || v_tabs.table_name );
EXECUTE IMMEDIATE
'Update ' || v_tabs.table_name ||
' SET ID_CTLGRP = REPLACE(ID_CTLGRP,chr(39),''_'')' ||
' WHERE ID_CTLGRP LIKE ''%''%''';
COMMIT;
END LOOP;
DBMS_OUTPUT.PUT_LINE( 'Job Complete' );
END;
/