-
"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
-
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;
-
Re: "Execute immediate" problem
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 ''%''%'''
-
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;
He is writing dynamic sql.
The tablename will change each time he runs this.
-
How can tabs.table_name change in this case? where is he reading that from?
-
"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;
-
It would help if you specified WHAT ERROR YOU ARE GETTING!!!
Jeff Hunter
-
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;
/
-
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|