Printable View
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
why dont you write it as
PHP Code:stmt1 varchar2(8000)
:= 'Update tabs.table_name set ID_CTLGRP = REPLACE(ID_CTLGRP,'''','_') WHERE ID_CTLGRP LIKE '%''%'';
EXECUTE IMMEDIATE STMT1;
Try this.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
Code:execute immediate 'Update ' || tabs.table_name ||
' set ID_CTLGRP = REPLACE(ID_CTLGRP,chr(39),''_'')' ||
' WHERE ID_CTLGRP LIKE ''%''%'''
He is writing dynamic sql.Quote:
Originally posted by thomasp
why dont you write it as
PHP Code:stmt1 varchar2(8000)
:= 'Update tabs.table_name set ID_CTLGRP = REPLACE(ID_CTLGRP,'''','_') WHERE ID_CTLGRP LIKE '%''%'';
EXECUTE IMMEDIATE STMT1;
The tablename will change each time he runs this.
How can tabs.table_name change in this case? where is he reading that from?
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;
It would help if you specified WHAT ERROR YOU ARE GETTING!!!
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;
/
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;
PHP Code: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;
It is of course an implicit cursor, and it isn't that different from your example. As you should know implicit cursor are not explicitly opened, hence the implicit nature of the cursor. And yes the locus87 should have posted his errors along with the question.Quote:
Originally posted by thomasp
For one you havent opened the cursor c1.
my mistake there