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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
