Is it feasible to make Oracle decides and thinks !!

[farrokhp]

I guess this is an interesting question came to my mind:

Assume that we have a big table of all emplyees, let's say one million record and we actually forgot to implace the gender field in the table. Now we alter the table and do:

SQL> alter table employee
add (gender VARCHAR2(1) NOT NULL);

but the puzzle is I need oracle somehow start thinking and go throught all 1 million records and find out if the employee is man or women ( depends on their first name ) and populate this new feild with M or W.

How it can be a reality and implemented?

[uday]

, I will wait for chrisrlong and sambavan to handle this question.

uday

[santoshym]

Hi

If you really want to implement a mistake which was on the whole the fault of the designing team who designed the schema and maybe some extent on the part of the developers who did not get to understand the user's requirment it is tremondous effort you are aking on the part of oracle to do

It is not easy to change the design of the schema after 1 million records have been added.

Allthough if you change the design I dont know any such option as to make oracle make a guess work of the gender depdening on the first name taking into consideration the Global naming convention used to Name a person thru the whole World.

Regards

[marist89]

update employee set gender='W';

(At least 1/2 the records will be correct )

[nwillemse]

Don't you have that information in another system? Maybe the old system they use to use and you can update hopefully most of the info.

Otherwise, code some name recognition system (NRS :-) ) that uses some AI and makes a decision base on firstname/lastname :-)

--nick

[farrokhp]

Wow, Are you serious? Do we have such thing as " Global naming convention used to Name a person thru the whole World. " ?? where is it ? Where can I dind something like AI agent to handle this schema designers terrible job.

[CliffW]

Direct Mail houses (for example, MetroMail) use software that will make a guess as to a person's gender.

Cliff

[sambavan]

There you go the answer had already been provided. Use the softwares of that order or you could contact some datacenter who would be willing to work with you, then you can provide the name list to them and then they might be able to get you a possible gender matches to those names. But this would be more close and predictable, but wouldn't full fill all the genders. One of the other best sorce would be any library/university database to do the possible matches.

Would look more into this concept.


Waww!!!

Sam

[padmam]

Hi, 25th May 2001 20:08 hrs chennai

http://www.oracle.com/oramag/oracle/...ml?o60sql.html

Try Using dynamic SQL you can try to build a Package which can alter a table have a decode function for the column based on which you have to insert M or F.I dont know whether alter table command an be included in Dynamic SQL too check it.

There can be other turn arounds other than what i have put above but the way you tell to work on its own intelligently based on first name no chance to update.It can do based on substitute condition.

Cheers

Padmam

[pando]

SQL> alter table employee
add (gender VARCHAR2(1) NOT NULL);

wont work if you have 1 million records in the table anyway