"Execute immediate" problem

[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

[thomasp]

why dont you write it as

stmt1 varchar2(8000)
      := 'Update tabs.table_name set ID_CTLGRP = REPLACE(ID_CTLGRP,'''','_') WHERE ID_CTLGRP LIKE '%''%'';

EXECUTE IMMEDIATE STMT1; 

[gandolf989]

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.

execute immediate 'Update '       || tabs.table_name ||
' set ID_CTLGRP = REPLACE(ID_CTLGRP,chr(39),''_'')'  ||
' WHERE ID_CTLGRP LIKE ''%''%'''

[gandolf989]

Originally posted by thomasp
why dont you write it as

stmt1 varchar2(8000)
      := 'Update tabs.table_name set ID_CTLGRP = REPLACE(ID_CTLGRP,'''','_') WHERE ID_CTLGRP LIKE '%''%'';

EXECUTE IMMEDIATE STMT1; 

He is writing dynamic sql.
The tablename will change each time he runs this.

[thomasp]

How can tabs.table_name change in this case? where is he reading that from?

[locus87]

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;

[marist89]

It would help if you specified WHAT ERROR YOU ARE GETTING!!!

[gandolf989]

Does this work?

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;
/

[thomasp]

what is the error message that you're getting?
type
sql> show errors

and what do you get?

For one you havent opened the cursor c1.
why dont you just do a
begin
for x in (select table_name from cols where column_name = 'ID_CTLGRP' and table_name like 'TUA%') 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;

[thomasp]

declare
.....
begin
for x in (select table_name from cols where column_name = 'ID_CTLGRP' and table_name like 'TUA%') loop

dbms_output.put_line ('Processing Table:'||tabs.table_name);

execute immediate 'Update '|| x.table_name ||' set ID_CTLGRP = REPLACE(ID_CTLGRP,chr(39),''_'')'||' WHERE ID_CTLGRP LIKE ''%''%''';

End loop; --tables
dbms_output.put_line ('Job Complete');
end;