Re: Find special characters in the database

On Wed, 21 Mar 2001, Shakeel Qureshi wrote:

> Hi Jared,
>
> Its Oracle 8i and just need to do this operation on
> a small table, using SQL and PL/SQL.
>
> I would appreciate if you could guide me
>
> a. As to how to find out the special characters
> b. Change values
>
> Looking forward to your help.
>

Well, I have some time and this sounded kinda fun.

The first script creates 2 tables from the demo account 'SCOTT'.

The second script is a brute force method to use dynamic SQL to change all the values.

I suggest you study the script carefully before running it.

You will need to edit the values in the tabList, specialChars and replaceChars arrays.

Jared

---

-- script 1

--create test tables and muck up the data


drop table cust;
drop table emp;

create table cust
as
select *
from scott.s_customer
/

create table emp
as
select *
from scott.s_emp
/


-- add goofy characters to the data

begin

        for frec in (
                select id, name, address, city
                from cust
                for update
        )
        loop
                update cust
                set
                        name = substr(name,1,5) || chr(10) || substr(name,6)
                        , address = substr(address,1,7) || chr(185) || 
substr(address,8)
                        , city = substr(city,1,4) || chr(7) || substr(city,5)
                where id = frec.id;
        end loop;
        commit;

end;
/


------------

-- script 2
--change the special characters to something else


declare

        type specialCharType is varray(100) of varchar2(1);
        type tabListType is varray(100) of varchar2(30);

        -- these are the characters you want to replace
        specialChars specialCharType := specialCharType (
                chr(10), chr(185), chr(7)
        );

        -- these are the characters to replace the
        -- ones above with
        -- there must be a one-to-one correspondence
        -- to the above array
        -- replace:
        -- chr(10) with a blank
        -- chr(128) with '^'
        -- chr(7) with a '@'
        replaceChars specialCharType := specialCharType (
                chr(32), chr(94), chr(64)
        );

        --tables to fix
        -- you need to edit this
        tabList tabListType := tabListType (
                'CUST','EMP'
        );

        sqlStatement varchar2(1000);

        cursor colnameCur( table_name_in user_tables.table_name%type )
        is
        select column_name
        from user_tab_columns
        where table_name = upper(table_name_in)
        and data_type in ('CHAR','VARCHAR','VARCHAR2');


begin

        for chrnum in specialChars.first .. specialChars.last
        loop
                --dbms_output.put_line( chrnum );

                for tabnum in tabList.first .. tabList.last
                loop
                        --dbms_output. put_line(tabList(tabnum));
                        sqlStatement := 'update ' || tabList(tabnum) || ' set ';
                        for colrec in colnameCur(tabList(tabnum))
                        loop
                                --dbms_output. put_line( colrec.column_name);
                                sqlStatement := sqlStatement || colrec.column_name
                                        || ' = translate(' || colrec.column_name || ','
                                        || 'chr(' || ascii(specialChars(chrnum)) || ')'
                                        || ',' || 'chr(' || ascii(replaceChars(chrnum))
                                        || ')),';

                        end loop;

                        -- get rid of the last comma
                        sqlStatement := 
substr(sqlStatement,1,instr(sqlStatement,',',-1)-1);
                        execute immediate sqlStatement;
                        commit;

                end loop;


        end loop;
end;
/




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).