|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 2nd, 2004, 06:52 AM
|
|||
|
|||
|
'Execute immediate' sql error
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 all apostrophes with underscores in the field ID_CTLGRP for a number of tables. Thanks
|
|
#2
November 2nd, 2004, 11:34 AM
|
|||
|
|||
|
Code:
Update Real_Table set ID_CTLGRP = REPLACE(ID_CTLGRP,CHR(39),'_') WHERE ID_CTLGRP LIKE '%'; Lose the execute immediate. Then do a select (from all_tables using the owner) to create a list of table names you need to update, edit the list (put a table name in place of real_table) -> create a sql script. Or you can mess around with three or four ' characters - I find it easier NOT to do that. It's too prone to error. Code:
execute immediate 'Update ' ||tabs.table_name|| 'set ID_CTLGRP = REPLACE(ID_CTLGRP,CHR(39),'_') WHERE ID_CTLGRP LIKE ''''%'''' ';
|
|
#3
November 2nd, 2004, 12:56 PM
|
|||
|
|||
|
Quote:
But if you want to search for entries that start with a % you need to escape it: Code:
WHERE CTLGRP LIKE '\%%' ESCAPE '\';
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > 'Execute immediate' sql error |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
