Replacing strings in oracle databases

Source: Internet
Author: User
When I encountered such a problem during application, the Database entered some basic data, and sometimes all the characters in the database containing "Unicom" may be replaced with "Telecom ", if you do not want to replace some tables, you have written a simple replacement program. If the data is important, you 'd better back up the data before calling it.

When I encountered such a problem during application, the Database entered some basic data, and sometimes all the characters in the database containing "Unicom" may be replaced with "Telecom ", if you do not want to replace some tables, you have written a simple replacement program. If the data is important, you 'd better back up the data before calling it.

When I encountered such a problem during application, the Database entered some basic data, and sometimes all the characters in the database containing "Unicom" may be replaced with "Telecom ", I wrote a simple replacement program for such a function. Of course, if you don't want to replace some tables, you can modify a replacement rule.
If the data is important, it is best to back up the data before calling. It is irreversible after replacement. Oracle
Create or replace procedure STR_REPLACE_ALL (oldStr in varchar2, newStr in varchar2) isTABLE_NAME VARCHAR2 (45); -- table name COLUMN_NAME VARCHAR2 (100); -- field name SQL _STR VARCHAR2 (200 ); -- dynamically executed SQLtype cur_type is ref cursor; -- defines the cursor_columns cur_type; -- query field cursor cursor_tables is -- Query table cursor select table_name from user_tables; begin open cursor_tables; loop -- traverse all database tables. fetch cursor_tables into TABLE_NAME; exit when cursor_tables % notfound; DBMS_OUTPUT.put_line (TABLE_NAME); -- traverse all struct fields of the current table. SQL _STR: = 'select COLUMN_NAME from user_tab_columns where TABLE_NAME = ''' | TABLE_NAME | ''' and DATA_TYPE in (''char '', ''varchar2 '')'; open cursor_columns for SQL _STR; loop fetch cursor_columns into COLUMN_NAME; exit when cursor_columns % notfound; -- DBMS_OUTPUT.put_line ('---' | COLUMN_NAME); -- replaces the SQL _STR: = 'update' | TABLE_NAME | 'set' | COLUMN_NAME | '= replace (' | COLUMN_NAME | ', ''' | oldStr | ''', ''' | newStr | ''') '; -- DBMS_OUTPUT.put_line (SQL _STR); execute immediate SQL _STR; commit; end loop; end STR_REPLACE_ALL;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.