Home » SQL & PL/SQL » SQL & PL/SQL » Records not getting inserted
Records not getting inserted [message #230127] Wed, 11 April 2007 00:02 Go to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi All,

I have emp table and dept table.Column values empno,ename from emp table and dname from dept table should be inserted into another table emp2.If the row already exists in the emp2 then it needs to be updated. The emp2 table has columns empno,ename,dname.
This should be done only using cursors.

I have tried some thing this but it is not working as expected.PLease solve this.

DECLARE
	CURSOR c1 IS
        SELECT e.empno,e.ename,d.dname FROM EMP e,DEPT d WHERE e.deptno = d.deptno;        
  CURSOR c2 IS 
        SELECT empno FROM EMP2;                 
BEGIN           
      FOR c2_rec IN c2 LOOP
        FOR c1_rec IN c1 LOOP		  	      			    
        		 IF (c2_rec.empno != c1_rec.empno) OR (c2_rec.empno IS NULL) THEN
        		    INSERT INTO EMP2 VALUES(c1_rec.empno,c1_rec.ename,c1_rec.dname);
				ELSE
                            UPDATE EMP2 SET ename =c1_rec.ename, dname=c1_rec.dname; 		    	
        		 END IF;		
          END LOOP;	    	  	    	
        END LOOP;
        COMMIT;
END;


Please correct this code.

Thanks
Srini..

[Updated on: Wed, 11 April 2007 00:04]

Report message to a moderator

Re: Records not getting inserted [message #230129 is a reply to message #230127] Wed, 11 April 2007 00:18 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
HI anacedent

Very Bad Joke.....
Re: Records not getting inserted [message #230131 is a reply to message #230127] Wed, 11 April 2007 00:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
srinivasocp,
saying code is "not working as expected" is less than useful.
How would I know what code is doing & what is expected?
With free advice, you get what you paid for it.
Re: Records not getting inserted [message #230135 is a reply to message #230127] Wed, 11 April 2007 00:49 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
This should be done only using cursors.

Why? Is it a kind of a PL/SQL exercise? What is wrong with MERGE? Just a single SQL command, no need to use PL/SQL at all.

If you would write down the values in inner loop, you would realize there is too much of them. Then simple removal of cursor c2 and treating the existence of empno in emp2 inside the c1 loop should work. Either check it by SELECT FOR UPDATE from emp2 or directly UPDATE emp2 and check SQL%ROWCOUNT if any row was affected (if not, INSERT).
Re: Records not getting inserted [message #230191 is a reply to message #230131] Wed, 11 April 2007 03:09 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi anacedent,

Quote:
saying code is "not working as expected" is less than useful.


Means the program i wrote is not updating or inserting into the emp2 table.That is what i mean by 'NOT WORKING AS EXPECTED'.If it is working excatly as expected why will i post the issue here.
I have done my home work and come out with a code which needs to be corrected a bit.If you can help me,do it or else mind your work.

Hi flyboy,
Thanks for your advice. Using merge i know hw to do ,but the interviwer asked me to do only with a cursor.So if can make some changes to the code i have provided it will be useful for me.

Thanks a lot
Srini..
Re: Records not getting inserted [message #230258 is a reply to message #230127] Wed, 11 April 2007 05:53 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,
I suppose you have read the second paragraph, where I described where I think the problem lies. In other words, cursor c2 shall be removed and the inner part shall be reworked as I described (I would prefer UPDATE followed by test on SQL%ROWCOUNT).
By the way, your UPDATE statement has no WHERE clause, so it updates all rows.
Re: Records not getting inserted [message #230261 is a reply to message #230191] Wed, 11 April 2007 06:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
srinivasocp wrote on Wed, 11 April 2007 10:09
If it is working excatly as expected why will i post the issue here.
Anacedent is pointing you to the lack of details. Have you read the sticky (tips and tricks)? Be as precise as you can be.
srinivasocp wrote on Wed, 11 April 2007 10:09
I have done my home work and come out with a code which needs to be corrected a bit.
More than a bit if I see it correctly.
srinivasocp wrote on Wed, 11 April 2007 10:09
If you can help me,do it or else mind your work.
Read the sticky again, the part about being polite is interesting. No one here is getting paid to help you out... Wink

Back on topic: flyboy has given you a lot of useful information to work with. Loop through emp, update emp2. If there are no rows affected, insert into emp2.

MHE
Re: Records not getting inserted [message #230412 is a reply to message #230261] Wed, 11 April 2007 20:58 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi Maaher,


Quote:
If you can help me,do it or else mind your work.


This guy anacedent is always offending me.It's like someone knows the answer they can post it,but not offend other users in a sarcastic way like Mr.anacedent.That Quote is meant only for him. I always request very politely,evident from my previous mails.What is the point in offending others.How much ever i can resolve the issue i have done it,i just rrequested to add a bit of code to make the program work as expected.You please stop supporting anacedent(as he is a senior member) and help him learn some manners and not to write any sarcastic or offending mails.I don't want to see any politics creeping into this Forum.


Regards
Srini...
Re: Records not getting inserted [message #230413 is a reply to message #230258] Wed, 11 April 2007 21:07 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi Flyboy,

Thanks a lot for your advice.I will work it out.

Regards
Srini.
Re: Records not getting inserted [message #230448 is a reply to message #230412] Thu, 12 April 2007 01:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think anacedent made a good point when he said your remark 'but it is not working as expected' was not useful. Maybe he could have said it in another way, but that's (more or less) up to him.
You did not tell us what exactly happened and what not, leaving us to guess what the outcome was you saw, vs the outcome you expected.
If you read the sticky, you will see the following points:
The Almighty Sticky says:
# Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
# Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
# Provide your expected result set.

None of these were provided by you.

[Updated on: Thu, 12 April 2007 01:07]

Report message to a moderator

Re: Records not getting inserted [message #230520 is a reply to message #230448] Thu, 12 April 2007 03:04 Go to previous messageGo to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Thanks Frank.I have not provided the scripts for emp and dept table as the table structres of that 2 tables are universally known, and for emp2 table i specified the column names.

Quote:
emp2 table has columns empno,ename,dname.


What is expected is very clearly stated in my post as,

Quote:
Column values empno,ename from emp table and dname from dept table should be inserted into another table emp2.If the row already exists in the emp2 then it needs to be updated.


This is not happening and so is my quote.
Quote:
I have tried some thing like this but it is not working as expected.


So i said the results are not as expected.

If some people can't even interpret this then even god can't help them.For such a small help anacedent is offending me.I hate Sarcastic messages.

Regards
Srini...
Re: Records not getting inserted [message #230527 is a reply to message #230520] Thu, 12 April 2007 03:10 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ok, so your remark was clear?
Quote:
Column values empno,ename from emp table and dname from dept table should be inserted into another table emp2.If the row already exists in the emp2 then it needs to be updated.

Did the insert not work?
Did the check whether the row already existed not work?
Did it crash?
Did it return some message?
Did not all columns get inserted?

Apart from that, it was clear what did not work as expected...

Remember that nobody knows what exactly you are doing. You have been struggling for a long time with your problem, prior to asking here, but your complete problem is new to us..

[Updated on: Thu, 12 April 2007 03:11]

Report message to a moderator

Previous Topic: pl/sql tables versus global temporary tables
Next Topic: objects permissions????
Goto Forum:
  


Current Time: Thu Dec 08 22:12:56 CST 2016

Total time taken to generate the page: 0.08983 seconds