Home » SQL & PL/SQL » SQL & PL/SQL » Procedure doent insert. (10g/win xp)
Procedure doent insert. [message #404955] Mon, 25 May 2009 13:46 Go to next message
liamadherne
Messages: 40
Registered: March 2008
Location: Philadelphia
Member
Hi,

I have two table C_DET and Locked,
The scenario is like whenever the cnt column in C_DET reaches the 10 value, the entire row will be deleted from the table and will be inserted into the Locked table.
If the locked table already contains that entry only the bdate column will be updated in the Locked table.

below is the procedure is wrote.

create or replace procedure br_proc as 
n c_det%rowtype;
y number(25):=0;
cursor c1 is select * from c_det;
begin
open c1;
loop
fetch c1 into n;
select 1 into y from locked where no=n.no;
if n.cnt >9 then
    if y=0 then 
      insert into locked values(n.no,n.doc);
      delete from c_det where no=n.no;
      commit;
    elsif y=1 then 
      update locked set bdate=n.doc where no=n.no;
      delete from c_det where no=n.no;
      commit;
      y:=0;
end if;
end if;
exit when c1%notfound;
end loop;
exception 
when no_data_found then
dbms_output.put_line('New record inserted');
close c1;
end;
/


The code only updates the bdate column in locked table, but does not insert the new row .

SQL> select * from c_det;

        no        CNT DOC
---------- ---------- ---------
         4         15 15-MAY-09
	

SQL> select * from locked;

        NO BDATE
---------- ---------
         1 07-JUN-86
         3 04-JUL-82



Any suggestion!

Liam.
Re: Procedure doent insert. [message #404956 is a reply to message #404955] Mon, 25 May 2009 14:46 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You do the insert "if y = 0".

But as far as I can make out the logic, y can never be 0.

It is either NULL or 1 or you maybe run into an "ORA-01427: single-row subquery returns more than one row" exception. But y can never be 0.
Re: Procedure doent insert. [message #404957 is a reply to message #404956] Mon, 25 May 2009 14:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Correction: It can only be 1, or you run into a "no data found" exception, or you run into a "single-row subquery returns more than one row" exception.

Re: Procedure doent insert. [message #404973 is a reply to message #404955] Mon, 25 May 2009 22:23 Go to previous messageGo to next message
liamadherne
Messages: 40
Registered: March 2008
Location: Philadelphia
Member
I tried modifying the procedure below.


create or replace procedure br_proc as 
n c_det%rowtype;
y number(25):=0;
cursor c1 is select * from c_det;
begin
open c1;
loop
fetch c1 into n;
select 1 into y from locked where no=n.no;
if n.cnt >9 then
[B]    if y=0 or y is null then[/B] 
      insert into locked values(n.no,n.doc);
      delete from c_det where no=n.no;
      commit;
    elsif y=1 then 
      update locked set bdate=n.doc where no=n.no;
      delete from c_det where no=n.no;
      commit;
      y:=0;
end if;
end if;
exit when c1%notfound;
end loop;
exception 
when no_data_found then
dbms_output.put_line('New record inserted');
close c1;
end;
/



But still the same,

PLEASE SUGGEST WHERE CAN I MAKE THE CHANGE TO GET THE PROCEDURE WORKING.

Thanks
Liam
Re: Procedure doent insert. [message #404979 is a reply to message #404955] Mon, 25 May 2009 23:56 Go to previous messageGo to next message
liamadherne
Messages: 40
Registered: March 2008
Location: Philadelphia
Member
Also, when I execute the procedure I get the following.

SQL> exec br_proc;
BEGIN bar_proc; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.BAR_PROC", line 9
ORA-06512: at line 1



Please suggest!
Re: Procedure doesn't insert. [message #404981 is a reply to message #404979] Tue, 26 May 2009 00:08 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Use another cursor instead of selecting.
Quote:
select 1 into y from locked where no=n.no;
(Or) put the above in begin end block and handle no_data_found there itself and assign null to y.

By
Vamsi
Re: Procedure doent insert. [message #404997 is a reply to message #404955] Tue, 26 May 2009 00:49 Go to previous messageGo to next message
liamadherne
Messages: 40
Registered: March 2008
Location: Philadelphia
Member
Hi

Thanks for your reply.

I can put the same in the begin, But I want to check everytime before the insert or update happens if the c_det.no occurence is there in locked table if it is there only update should take place and if the entry is not there new row should be inserted.

How can i go ahead, pls help.
Re: Procedure doent insert. [message #405010 is a reply to message #404997] Tue, 26 May 2009 01:14 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Read my post again.
I didn't ask you to move the select to some other place.
In between "fetch c1 into n;" and "if n.cnt >9 then" add a new begin and end block and handle the no_data_found.
(Or) Use a cursor for this select also.

By
Vamsi
Re: Procedure doent insert. [message #405029 is a reply to message #405010] Tue, 26 May 2009 02:20 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If the "no" column is the primary key or an unique index you could also just try to insert, catch the DUP_VAL_ON_INDEX and if it's raised do the update instead.
Re: Procedure doent insert. [message #405175 is a reply to message #404955] Tue, 26 May 2009 13:39 Go to previous messageGo to next message
liamadherne
Messages: 40
Registered: March 2008
Location: Philadelphia
Member
Hi

I made a bit modification in the procedure as below, the procedure works well with inserts but it updates only one row and comes out, because the update i have put in exception, where will i put the loop so that the exception part also gets executed till the time cursor reaches the end of row.


 create or replace procedure br_proc as
 n c_det%rowtype;
 x number(25);
 cursor c1 is select * from c_det;
 begin
 open c1;
 loop
 fetch c1 into n;
 if n.cnt >9 then
        insert into locked values(n.no,n.doc);
         commit;
          delete from calldet where no=n.no;
         commit;
 end if;
 exit when c1%notfound;
 end loop;
 exception
  when dup_val_on_index then
  update locked set bdate=n.doc where no=n.no;
       commit;
        delete from c_det where no=n.no;
       commit;
 close c1;
 end;
 /



Please suggest!
Re: Procedure doent insert. [message #405178 is a reply to message #405175] Tue, 26 May 2009 14:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Move the exception block inside the loop.

I would also get rid of as much commits as possible. And properly indent the code so that it is better readable.

create or replace procedure br_proc as
   n c_det%rowtype;
   x number(25);
   cursor c1 is select * from c_det;
 begin
   open c1;
   loop
     fetch c1 into n;
       if n.cnt >9 then
         begin
           insert into locked values(n.no,n.doc);
           delete from calldet where no=n.no;
         exception
           when dup_val_on_index then
             update locked set bdate=n.doc where no=n.no;
             delete from c_det where no=n.no;
         end;
       end if;
       exit when c1%notfound;
       commit;
     end loop;
   close c1;
end;
/


Also, since you only do something when n.cnt > 9, why not add that to a where clause and get rid of the IF statement?

create or replace procedure br_proc as
   n c_det%rowtype;
   x number(25);
   cursor c1 is select * from c_det where cnt > 9:
 begin
   open c1;
   loop
     fetch c1 into n;
       begin
         insert into locked values(n.no,n.doc);
         delete from calldet where no=n.no;
       exception
         when dup_val_on_index then
           update locked set bdate=n.doc where no=n.no;
           delete from c_det where no=n.no;
       end;
       exit when c1%notfound;
       commit;
     end loop;
   close c1;
end;
/


I would tend to get rid of the commit inside the loop completely, unless the procedure runs so long that people working with the tables run into locking problems.

[Updated on: Tue, 26 May 2009 14:05]

Report message to a moderator

Re: Procedure doent insert. [message #405179 is a reply to message #405175] Tue, 26 May 2009 14:11 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Why can't you use MERGE instead of fetching one by one and apply DML? You can delete the other table later.

By
Vamsi
Re: Procedure doent insert. [message #405638 is a reply to message #404955] Thu, 28 May 2009 14:21 Go to previous messageGo to next message
liamadherne
Messages: 40
Registered: March 2008
Location: Philadelphia
Member
Thanks a lot to both of you ThomasG and Vamsi.
I am thru now, you were of a great help.
Re: Procedure doent insert. [message #405797 is a reply to message #404979] Fri, 29 May 2009 08:56 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I am glad that your current issue has been resolved. But if you continue creating objects in SYS I am not sure what you will encounter next.
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.BAR_PROC", line 9
ORA-06512: at line 1

Don't do anything like create/modify/delete anything in sys unless and until you know what you are doing (in your development box) or instructed by oracle support.

Regards

Raj
Previous Topic: Tuning
Next Topic: Namespace attributes in sys context
Goto Forum:
  


Current Time: Fri Dec 09 11:34:53 CST 2016

Total time taken to generate the page: 0.12768 seconds