Home » SQL & PL/SQL » SQL & PL/SQL » Search Characters automatically in a whole Database (Oracle 9.2.0.1.0 Enterprise Edition, Windows NT)
Search Characters automatically in a whole Database [message #394144] Wed, 25 March 2009 17:07 Go to next message
sberger86
Messages: 5
Registered: March 2009
Location: Eisenach, Thüringen, D...
Junior Member
Hi,

First of all, I am very new in Oracle and SQL and PL/SQL.

I have to search through a whole database some special characters like ä, ö, ü, ñ, Ñ and then replace them for a special code, for example !ae! or something else.
My question is: is there any possibility to do this automatically with PL/SQL?

Because I have searched and maybe the answer is right in front of me but I havent see it.

What I have done is just replacing these Values in a known table with known columns. For example:

declare
cursor c1 is select * from tab1 for update;
begin
for rec in c1
loop
if rec.vorname like '%ä%' then
update tab1 set vorname = (select replace(vorname,'ä','!ae!') from tab1 where vorname = rec.vorname) where current of c1;
end if;
if rec.name like '%ä%' then
update tab1 set name = (select replace(name,'ä','!ae!') from tab1 where name = rec.name) where current of c1;
end if;
end loop;
commit;
end;

I could not post my SQLPLUS* session because the PC is not at home and I am not allowed to take any Information outside the office.

So this program works just for any character ä in the two columns: name and vorname from the table tab1.

I found also that in the public tables are the names of all columns and tables (table: all_tab_columns) in my database, so I think maybe if I can take those values with a cursor and the use them with another ref_cursor or so, I could maybe do all automatic. but can I use a cursor inside another one. Can I update the value in a ref_cursor. could you please help me?

I am really confused.

I would really apreciate it, if you can help me.

Sincerely,

sberger86
Re: Search Characters automatically in a whole Database [message #394146 is a reply to message #394144] Wed, 25 March 2009 17:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Row by row, is slow by slow.
Your approach will work, but is slower than pure SQL.


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

You can use all_tab_columns, but you need to only process VARCHAR2 columns.
Re: Search Characters automatically in a whole Database [message #394148 is a reply to message #394144] Wed, 25 March 2009 17:31 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
This is basically a somewhat bad idea.

You could possibly search for varchar/char/CLOB fields in the data dictionary tables and try to replace strings in those fields.

But there are too many things that can go wrong. Just one example, when you have a varchar2(20) field, and the content is already 20 characters, what then? What if the special character is in a foreign key or something? What about the special characters you will almost certainly miss?

So the first thing I have to ask is: *Why* you need to replace those characters. Maybe there is a better solution altogether.

If it's just a problem on the display or input side, you know you can choose the codepage the characters are displayed in on the cliend side with the NLS_LANG environment variable?
Re: Search Characters automatically in a whole Database [message #394149 is a reply to message #394144] Wed, 25 March 2009 18:07 Go to previous messageGo to next message
sberger86
Messages: 5
Registered: March 2009
Location: Eisenach, Thüringen, D...
Junior Member
Hi Thomas,

We are migrating a database from Oracle 9 to Oracle 10 and the problem is that we are lossing some data when we just change the nls character set from WE8ISO8859P15 to AL32UTF8. That´s why we wanted to make a codification before the export and after the import againg making the decodification. But you're right when the variables have a fix length.

what would you recommend me to do? to avoid the data losing with Umlaute, I mean ü, ä, ö and so on.

Thanks,
Re: Search Characters automatically in a whole Database [message #394150 is a reply to message #394149] Wed, 25 March 2009 18:22 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A straight export/import should work.

Just set NLS_LANG to German_Germany.WE8ISO8859P15 when you do the export and to German_Germany.AL32UTF8 when you do the import.

Have a look with

select parameter, value from nls_database_parameters;


on the source and target database first, to check for the NLS_CHARACTERSET. And then use exactly that character set for the export/import.

And be aware of possible problems with NLS_LENGTH_SEMANTICS, which might make varchar2 fields only half as big as you think they are.
Re: Search Characters automatically in a whole Database [message #394154 is a reply to message #394144] Wed, 25 March 2009 20:14 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm#sthref1515

The highlighted utility (above), might identify where problem characters exists.
In any case, this manual provides instructions on how to switch character sets.

>we just change the nls character set from WE8ISO8859P15 to AL32UTF8.

IIRC, the only supported mechanism to make the changes desired as stated above is to use export/import.
Re: Search Characters automatically in a whole Database [message #395541 is a reply to message #394144] Wed, 01 April 2009 17:34 Go to previous messageGo to next message
sberger86
Messages: 5
Registered: March 2009
Location: Eisenach, Thüringen, D...
Junior Member
Hi !

Thanks BlackSwan and ThomasG for your help!

I have just one more question:

so I have to set NLS_LANG in MS-DOS like

c: set NLS_LANG=GERMAN_GERMANY.WE8ISO8859P15 


before I do the export.

and then when I am going to do the import:

c: set NLS_LANG=GERMAN_GERMANY.AL32UTF8


and do I have to set up the character set in my new database as NLS_LANG=GERMAN_GERMANY.AL32UTF8 ?

I really appreaciate your help.
Re: Search Characters automatically in a whole Database [message #395634 is a reply to message #395541] Thu, 02 April 2009 02:17 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
1) First, when you create the new database you have to specify the right character set in the CREATE DATABASE statement.

2) I'm not 100% sure if it works with the environment variable in Windows. You might have to set it in the registry in
\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\NLS_LANG, too.

According to this document I found,

Quote:

Care must be taken that the character
set of the NLS_LANG for Export and Import sessions contain all characters to be migrated.



You can try the export/import pretty quick if you just export/import a small table with umlauts and check them on the target database, and then do the whole database with the same settings.
Previous Topic: analyzing how Oracle processes
Next Topic: How to use UNION with a CLOB [split topic]
Goto Forum:
  


Current Time: Thu Dec 08 16:22:40 CST 2016

Total time taken to generate the page: 0.24421 seconds