Home » SQL & PL/SQL » SQL & PL/SQL » Data Insertion Problem (Oracle 9i)
Data Insertion Problem [message #433046] Sun, 29 November 2009 08:31 Go to next message
evk_87
Messages: 8
Registered: September 2008
Junior Member
I have this table WORKERS, with following columns:

First_name ---> Varchar2
Last_name ---> Varchar2
Email ---> varchar2

Problem:

I have to insert the data as follows:-
first_name = rahul
last_name = dravid
email = first charachter of LAST_NAME concatenated with FIRST_NAME concatenated with @bcci.com

Rule:
Generate the email using first_name & last_name. DO NOT ENTER THE EMAIL DIRECTLY.

Please Help......It may have simple solution, but I guess i m not getting the solution.
Re: Data Insertion Problem [message #433047 is a reply to message #433046] Sun, 29 November 2009 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
CREATE VIEW ......


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Sun, 29 November 2009 09:10]

Report message to a moderator

Re: Data Insertion Problem [message #433051 is a reply to message #433046] Sun, 29 November 2009 09:47 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
How you are inserting the rows ?


sriram
Re: Data Insertion Problem [message #433052 is a reply to message #433046] Sun, 29 November 2009 09:53 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood the question correctly, you are looking for a database trigger. Here's an example of how to do that - this is a BEFORE INSERT trigger (which may be recreated in a matter of updating records as well), but I'll leave it to you.
SQL> create table workers
  2  (first_name varchar2(20),
  3   last_name  varchar2(20),
  4   email      varchar2(30)
  5  );

Table created.

SQL> create or replace trigger biwrk
  2    before insert on workers
  3    for each row
  4  begin
  5    :new.email := substr(:new.first_name, 1, 1) ||
  6                  :new.last_name ||
  7                  '@bcci.com';
  8  end;
  9  /

Trigger created.

SQL> insert into workers (first_name, last_name) values ('little', 'foot');

1 row created.

SQL> select * from workers;

FIRST_NAME           LAST_NAME            EMAIL
-------------------- -------------------- ------------------------------
little               foot                 lfoot@bcci.com

SQL>
Re: Data Insertion Problem [message #433058 is a reply to message #433052] Sun, 29 November 2009 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I disagree with Littlefoot and agree BlackSwan, what is needed is a view not a trigger to set a derived field.

If a field derived from other fields then you should use a view (or a virtual column from 11g). Fields must be independant.

Regards
Michel
Re: Data Insertion Problem [message #433062 is a reply to message #433058] Sun, 29 November 2009 11:25 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that you are right, sort of.

However, from my point of view, a trigger is what the OP is talking about, not a view. I didn't read it literally, but how to do "something" (not necessarily such a dummy assignment of creating an e-mail address as the first and the last name derivation) upon table insert.

Anyway, if the OP ever replies, we'll probably find out who was on the right track.
Re: Data Insertion Problem [message #433064 is a reply to message #433046] Sun, 29 November 2009 12:24 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
So as you can see, there are at least three solutions to your problem. That leaves you with the need to pick one that works best for you. Let me restate them for clarity.

1) if you are on 11g, use the new feature VIRTUAL COLUMN.

2) if you are not on 11g use one of these two solutions:

a) use a view. By this this we mean rename the table and replace it with a view that has an additional column for email.

b) use a table trigger. You saw an example of this in an earlier post.

Quote:
** Pay particular attention to the differences between solutions. Each has advantages and detractors.

The Virtaul Column feature was created specifically for this need and is thus the best choice but if you are not on 11g it is not available.

A trigger prevents use of parallel query (not really a big deal since 99% of all Oracle databases don't use parallel query to any great effect), and a trigger is not always fired which would leave your data corrupted (think bulk load situations).

A view is the preferred solution, but views are in fact an advanced technique. Believe it or not, most people using Oracle as still programming in the dark ages and don't like views. They want only to work with raw tables. If you find this hard to believe then just watch your DBA's facial expressions and note their hesitation as you tell him/her you want to replace a table with a view so you can have this derived column. Your DBA may even decide to use the trigger solution instead just to avoid the view even though most of us on ORAFaq can easily defend the superiority of the view solution. Point is, views should be part of your database design strategy one way of another, not an afterthought. But few understand this. I am a big view fan but I often butt heads over views mostly with people who have no vision and who think all databases are created equal.

Good luck, Kevin
Re: Data Insertion Problem [message #433066 is a reply to message #433052] Sun, 29 November 2009 12:31 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>before insert on workers
Worker Mary gets married & changes her last name so WORKER table get an UPDATE to reflex Mary's new last name.
Now email address is incorrect.
However if a VIEW is used, then automagically email remains correct
Re: Data Insertion Problem [message #433079 is a reply to message #433066] Sun, 29 November 2009 15:48 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Sun, 29 November 2009 18:31
>before insert on workers
Worker Mary gets married & changes her last name so WORKER table get an UPDATE to reflex Mary's new last name.
Now email address is incorrect.
However if a VIEW is used, then automagically email remains correct


Alternatively:

Worker Mary gets married & changes her last name so WORKER table get an UPDATE to reflex Mary's new last name.

However the email administrators decide, for the sake of everyones sanity, not to change her email address - it's in everyones address books, email lists etc. Plus Mary uses it to log onto all sorts of web sites and would be furious if she had to set up new accounts on everything.

If you were using a view it would now be wrong. If you're using a virtual coulmn or update trigger, it'll also be wrong.

Correct answer to this depends on what happens on a name change.
Re: Data Insertion Problem [message #433167 is a reply to message #433046] Mon, 30 November 2009 08:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
So the last two posts point out when it is a best to use a view and when it is best to have a real attribute with real data. The basic rule is:

Quote:
If data is derived using a rule that is ALWAYS true, then a view is your choice.

Quote:
If however the derivation rule is in fact only a convenience and not actually a non-violatable rule, then you should store the data in its own column.

EMAIL shows you that you need to know what the answer is. This means you must ask questions about the use of the derived element and poke at your users to confirm if there are any situations where the rule does not hold.

Have you asked these questions of those who gave you the rule?

Kevin
Re: Data Insertion Problem [message #433174 is a reply to message #433046] Mon, 30 November 2009 09:11 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
And I can think of another reason why the requirement is suspect.
First_name and last_name shouldn't be unique.
Email address has to be unique.

And if you're only using the first character of last name you're going to hit duplicate email addresses even sooner.

I'd be highly suspicious of any requirement to make email address derived and unalterable. I can see an argument for a derived default but nothing more.
Re: Data Insertion Problem [message #433178 is a reply to message #433046] Mon, 30 November 2009 09:25 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, that is my point exactly.

Dissassociate the actual request from the basic rules of derived data. Forget about the original request being for EMAIL and simply consider the general quesion "I HAVE SOME DERIVED DATA, WHAT IS THE BEST WAY TO HANDLE IT".

As for email, you are of course correct. The email scheme is flawed as was presented by the OP. But real email schemes are flawed as a normal course. Consider that my email address is KEVIN.MEADE@THEHARTFORD.COM. This is easily seen as derived using, "first.last@company". This works for me, but there are THREE (3) Dave Smiths at my company... wonder how their emails worked out... maybe I'll ask my buddy Dave.

Kevin
Re: Data Insertion Problem [message #433180 is a reply to message #433174] Mon, 30 November 2009 09:28 Go to previous message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
This is a pretty common homework question and actually appears (or used to appear) in the official Oracle SQL Fundamentals courseware, I would suggest that the solution is something rather simpler that doesn't actually go into the practicalities of such a task in the real world. I'm vaguely remembering that it was to do with substitution variables.
I was going to post the solution, then remembered that I think this is actually homework.

[Updated on: Mon, 30 November 2009 09:29]

Report message to a moderator

Previous Topic: procedure/function to search the field
Next Topic: maintain history of all updates for a table and all relational tables
Goto Forum:
  


Current Time: Mon Sep 26 16:20:08 CDT 2016

Total time taken to generate the page: 0.08780 seconds