Home » SQL & PL/SQL » SQL & PL/SQL » Using rowid to insert (oracle 10g,win xp)
Using rowid to insert [message #321253] Mon, 19 May 2008 11:55 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
[CODE]create or replace procedure pr_return_del
  as
    v_NewRowid ROWID;
    type tabemptable IS TABLE OF emp_test%rowtype
    INDEX BY BINARY_INTEGER ;
    empTable     tabemptable ;
    begin
     DELETE FROM emp_test
      WHERE empno=7902
      RETURNING empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect INTO  empTable;
  for i in empTable.first ..empTable.last  loop
 insert into emp_bkp
 values
 (empTable(i).empno,empTable(i).ename,empTable(i).job,
 empTable(i).mgr,empTable(i).hiredate,empTable(i).sal,
 empTable(i).comm,empTable(i).deptno);
 end loop;
 DBMS_OUTPUT.PUT_LINE('records inserted');
  END;[/CODE]


Hi All

The functionality mentioned above,is a bit lengthy,

can i return the rowids into a table type variable and somehow insert the values using the rowids??

Re: Using rowid to insert [message #321257 is a reply to message #321253] Mon, 19 May 2008 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which rowid you want to get? The ones you just deleted, you will not be able to select them, it will not work.

Regards
Michel
Re: Using rowid to insert [message #321262 is a reply to message #321253] Mon, 19 May 2008 12:15 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Thanks for the note.

The requirement is such that i need to insert the rows that are deleted into a backup table.
A trigger would have been fine but cannot use it in production.

So rather than mentioning each column in the returning into clause,is there any other way out?

I initially thought that if i can get the row reference and insert through that,but i guess those rowids can't be refered as they will be deleted.

Lastly will this delete operation be slow compared to normal delete?
Re: Using rowid to insert [message #321264 is a reply to message #321262] Mon, 19 May 2008 12:39 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
swas_fly wrote on Mon, 19 May 2008 13:15
Thanks for the note.

The requirement is such that i need to insert the rows that are deleted into a backup table.




If you need to save rows deleted, why not just use a flag column for DELETED in the same table?
Re: Using rowid to insert [message #321266 is a reply to message #321262] Mon, 19 May 2008 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
declare
  cursor curs is select * from emp_test where empno=7902 for update;
begin
  open curs;
  insert into emp_bkp select * from emp_test where empno=7902;
  delete emp_test where empno=7902;
  close curs;
end;
/

This will protect you against deletes and updates but not against new inserted row.
The only way to completly protect is to lock the table.

Regards
Michel
Re: Using rowid to insert [message #321269 is a reply to message #321264] Mon, 19 May 2008 12:47 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
unfortunately we need to reduce the unwanted rows from the main table and for archieving purpose,so that in case of any need the users can refer back to the backup table,hence we need to delete and then insert.
Re: Using rowid to insert [message #321271 is a reply to message #321269] Mon, 19 May 2008 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no difference between delete/insert and insert/delete.
I don't understand your issue.

Regards
Michel
Re: Using rowid to insert [message #321273 is a reply to message #321266] Mon, 19 May 2008 12:55 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
create or replace procedure pr_return_del
  as
    v_NewRowid ROWID;
    type tabemptable IS TABLE OF emp_test%rowtype
    INDEX BY BINARY_INTEGER ;
    empTable     tabemptable ;
    begin
     DELETE FROM emp_test
      WHERE empno=7902
      RETURNING empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect INTO  empTable;
  for i in empTable.first ..empTable.last  loop
 insert into emp_bkp
 values
 (empTable(i).empno,empTable(i).ename,empTable(i).job,
 empTable(i).mgr,empTable(i).hiredate,empTable(i).sal,
 empTable(i).comm,empTable(i).deptno);
 end loop;
 DBMS_OUTPUT.PUT_LINE('records inserted');
  END;


your code-------------

declare
  cursor curs is select * from emp_test where empno=7902 for update;
begin
  open curs;
  insert into emp_bkp select * from emp_test where empno=7902;
  delete emp_test where empno=7902;
  close curs;
end;


Well each delete will result in a delete for around 4000 rows and those records needed to be there in the bkup table.

However there can be a situation where multiple users will be accesing the tables through the application
and if i use the cursor rather than the returining into clause,will it be faster compared to the cursor?

I thought the returning clause will decrease the time frame between the plsql and sql engine?


So according to your code simultaneous updates and deletes will not occur?
Re: Using rowid to insert [message #321275 is a reply to message #321273] Mon, 19 May 2008 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
However there can be a situation where multiple users will be accesing the tables through the application

This is why I said:
Michel
This will protect you against deletes and updates but not against new inserted row.
The only way to completly protect is to lock the table.


Quote:
if i use the cursor rather than the returining into clause,will it be faster compared to the cursor?

Locking and using pure SQL will be faster than PL/SQL code.

Quote:
So according to your code simultaneous updates and deletes will not occur?

Yes, the cursor "for update" is there to lock the rows.

Regards
Michel
Re: Using rowid to insert [message #321277 is a reply to message #321271] Mon, 19 May 2008 13:20 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I too agree that there will be no difference in delete/insert and insert/delete????????

but the id on the basis of which the delete will happen,will be passed from the front end.

Now after the deletion is complete there is a message displayed in the front end that this many rows are deleted,hence if i insert first and then delete then the time taken for delete will rise as the message will only be displayed after the delete

the front end screen contains a text boz and a delete button,I hope i am clear now that why i wanted to delete first and then insert?

Re: Using rowid to insert [message #321278 is a reply to message #321275] Mon, 19 May 2008 13:34 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Michel
This will protect you against deletes and updates but not against new inserted row.
The only way to completly protect is to lock the table.

Quote:
Does this mean that the user will not be able to delete and update other rows.or same set of rows in the cursor.
I should not lock the table completely because users might be creating new lists and inserting into table
Re: Using rowid to insert [message #321280 is a reply to message #321278] Mon, 19 May 2008 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
swas_fly wrote on Mon, 19 May 2008 20:34
Michel
This will protect you against deletes and updates but not against new inserted row.
The only way to completly protect is to lock the table.

Quote:
Does this mean that the user will not be able to delete and update other rows.or same set of rows in the cursor.
I should not lock the table completely because users might be creating new lists and inserting into table


I meant updates or deletes the rows you want to delete, not other ones.
And I was talking about inserting rows that satisfy the condition (where empno...).
These only apply to the solution I posted which avoid PL/SQL coding.

If you are a better chance to not have a collision, then you can enter a serializable mode and then just "insert select" and "delete" but you will get an error if someone touch a row that satifies your condition. This is a bet. Only you know the likelyhood there is a collision.

Regards
Michel

[Updated on: Mon, 19 May 2008 13:43]

Report message to a moderator

Re: Using rowid to insert [message #321283 is a reply to message #321280] Mon, 19 May 2008 13:56 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Thanks Michel

I completely understand the point you are making,but

if i am clear in my previous point that i make on why i am stressing on delete/insert rather than insert/delete?

Then is there a solution for that?
Re: Using rowid to insert [message #321284 is a reply to message #321283] Mon, 19 May 2008 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if i am clear in my previous point that i make on why i am stressing on delete/insert rather than insert/delete?

I still don't understand this point, I think you want both work on the same rows and so on the same count. It does not matter you get the count from the insert or the delete.

Regards
Michel
Re: Using rowid to insert [message #321329 is a reply to message #321253] Mon, 19 May 2008 21:36 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Quote:
I too agree that there will be no difference in delete/insert and insert/delete????????

but the id on the basis of which the delete will happen,will be passed from the front end.

Now after the deletion is complete there is a message displayed in the front end that this many rows are deleted,hence if i insert first and then delete then the time taken for delete will rise as the message will only be displayed after the delete

the front end screen contains a text boz and a delete button,I hope i am clear now that why i wanted to delete first and then insert?




Now if i take the count of the inserts and display the message as this many rows are deleted,then although that will can be a case but this will be a defect in production code.
Re: Using rowid to insert [message #321333 is a reply to message #321253] Mon, 19 May 2008 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> i am stressing on delete/insert rather than insert/delete
If you delete first, from where do you get values for subsequent insert?
Re: Using rowid to insert [message #321357 is a reply to message #321329] Tue, 20 May 2008 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now if i take the count of the inserts and display the message as this many rows are deleted,then although that will can be a case but this will be a defect in production code

Still don't understand, if there is a "defect", there is an exception and so there is no return value.
Post a scenario in which case it does not work.

Regards
Michel
Re: Using rowid to insert [message #321443 is a reply to message #321333] Tue, 20 May 2008 05:08 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
do you want to use the same id for insertion... which has been
deleted already...
Re: Using rowid to insert [message #321451 is a reply to message #321253] Tue, 20 May 2008 05:30 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Hi,
This is my understanding

SwasFly Code :
1.Delete rows , returning them to binary indexd table
2.Pass message to front end the deleted rows count
i.e count(*) in binary indxd table
3.Insert binary indxd table data to bkup tbl.

Michael COde:
1.Cursor lock specific record.
2.Insert this record values to bkup tbl.
3.delete this record from actual table.

I hope the previous code can be effective if you need to pass message to front end.Michael's pls give your comment if you have a different perspective.


Re: Using rowid to insert [message #321465 is a reply to message #321451] Tue, 20 May 2008 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As this is a PL/SQL procedure, you didn't pass anything BEFORE the procedure ends.
So it does not matter you get the count at any step.

Regards
Michel
Re: Using rowid to insert [message #321489 is a reply to message #321253] Tue, 20 May 2008 08:00 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi Michel

Trying my level best to make you explain. Smile

Here is the senario of the production procedure

PROCEDURE p_Deletion
(p_List_Id IN VARCHAR2 DEFAULT NULL,
p_CalledBy IN VARCHAR2 DEFAULT 'V')
IS

p_Before_del_cnt NUMBER;
p_After_del_cnt NUMBER;
p_del_cnt NUMBER;
type tabemptable IS TABLE OF emp_test%rowtype
INDEX BY BINARY_INTEGER ;
empTable     tabemptable ;
BEGIN


SELECT COUNT(empno)
INTO p_Before_del_cnt
FROM emp_test;

 DELETE FROM emp_test
  WHERE empno=p_list_id
 RETURNING empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect INTO  empTable;

commit;
  for i in empTable.first ..empTable.last  loop
 insert into emp_bkp
 values
 (empTable(i).empno,empTable(i).ename,empTable(i).job,
 empTable(i).mgr,empTable(i).hiredate,empTable(i).sal,
 empTable(i).comm,empTable(i).deptno);

commit;
 end loop;

SELECT COUNT(empno)
INTO p_After_del_cnt
FROM emp_test;

p_del_cnt := p_Before_del_cnt - p_After_del_cnt;

lenIn_Ace2_Listgen_Screens_pkg.p_Load_Page(
'Lenin_Ace2_Listgen_Screens_pkg.p_Display_Message?p_MsgType=Delete Requests&p_MsgText='
||p_del_cnt
||' Record(s) Deleted for  ID '
||p_List_Id
||' by '
||p_Current_User
||'&p_Url=Lenin_Ace2_Listgen_Screens_pkg.P_DeleteList');
END p_Deletion;



Here i am stressing on the point that

Quote:
Now if i take the count of the inserts and display the message as this many rows are deleted,then although that will can be a case but this will be a defect in production code


BY defect i don't mean error,i mean displaying a message of this many records deleted by taking the counts of insert is not proper thing to do.

Which is what i can understand from your point of view,i understand your point when you expalined on the cursor but
if you see the procedure,u can find the message to de displayed after delete,so if i do the insert and then delete and display a message of delete,this would mean that the user will feel that it took say 15 mins for delete but actually it is 5 mins for insert and 10 mins for delete Surprised

Hope i can make my point clear

[Updated on: Tue, 20 May 2008 10:24] by Moderator

Report message to a moderator

Re: Using rowid to insert [message #321491 is a reply to message #321489] Tue, 20 May 2008 08:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why is it not the 'proper' thing to do.
If two numbers are the same, it doesn't matter which one of them you use.
Re: Using rowid to insert [message #321492 is a reply to message #321253] Tue, 20 May 2008 08:08 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
How can that be advisible for a final version of production level code,i guess that is the last thing to do
Re: Using rowid to insert [message #321495 is a reply to message #321489] Tue, 20 May 2008 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know what you want to return with your "p_del_cnt" but it is NOT the number of rows you deleted (unless you are working alone).

Quote:
this would mean that the user will feel that it took say 15 mins for delete but actually it is 5 mins for insert and 10 mins for delete

But the user will only get the result AFTER the procedure returns, in any case, so after the 15 minutes.

Regards
Michel
Re: Using rowid to insert [message #321511 is a reply to message #321495] Tue, 20 May 2008 09:43 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi Michel

I was really insane in a few queries that i had asked.I had an impression that the user will be able to get the message after the delete,but you are right in the point that the message will only be displayed only after the pl/sql procedure is completed successfully.

But i am a bit confused on

Quote:
I don't know what you want to return with your "p_del_cnt" but it is NOT the number of rows you deleted (unless you are working alone).



yes you are right on the fact that it will not be one user loged n to the application,there will be a min of 2-3 users logged in and performing the delete operation,
now why this variable won't give the delete counts?
as i am taking thethe difference and indeed it is showing me when i am executing the application.

I might be missing a big catch here.Kindly let me know.

Thnaks
Re: Using rowid to insert [message #321521 is a reply to message #321511] Tue, 20 May 2008 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The reason is simple.
Your variable will only contain the difference between the 2 counts but as you are not alone many can happen in your database in addition to what you did, some rows may have been added, others or the same ones may have been deleted.

The only correct way to get the number of deleted rows is in SQL%ROWCOUNT after the delete statement.

Regards
Michel

[Updated on: Tue, 20 May 2008 10:25]

Report message to a moderator

Re: Using rowid to insert [message #321538 is a reply to message #321521] Tue, 20 May 2008 12:13 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Quote:
I meant updates or deletes the rows you want to delete, not other ones.
And I was talking about inserting rows that satisfy the condition (where empno...).
These only apply to the solution I posted which avoid PL/SQL coding.

If you are a better chance to not have a collision, then you can enter a serializable mode and then just "insert select" and "delete" but you will get an error if someone touch a row that satifies your condition. This is a bet. Only you know the likelyhood there is a collision.



In this case if locks are applied,then the user who encounters a lock,for him the system will hang!! am i right?
If that is the case then it will be a problem?
Re: Using rowid to insert [message #321539 is a reply to message #321538] Tue, 20 May 2008 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then the user who encounters a lock,for him the system will hang

This is the purpose of lock.

Quote:
If that is the case then it will be a problem?

No, locks are rdbms life unless you use ms/access and work with only one process.

Regards
Michel
Re: Using rowid to insert [message #321550 is a reply to message #321539] Tue, 20 May 2008 13:12 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
then if the process hangs then won't it be advisible to give an error messsage to the user,something like wait for some time before trying again
Re: Using rowid to insert [message #321560 is a reply to message #321550] Tue, 20 May 2008 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The process halts until the holder ends the transaction. It is rdbms life. You always and all the time have it and you don't even notice it with another kind of locks which are latches.

It is the same thing when 2 people want to pass a door at the same time, you don't kill one of them, one is just waiting for the other one to pass through.

Regards
Michel
Re: Using rowid to insert [message #321566 is a reply to message #321560] Tue, 20 May 2008 13:55 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
create or replace procedure pr_return_del_cursor(e_no number)
    as
   
  cursor emp_cur is
  
  select * from emp_test where empno=e_no for update wait 60;
  v_emp emp_cur%rowtype;
  
      begin
      
      open emp_cur;
      loop
      fetch emp_cur into v_emp ;
      exit when emp_cur%notfound;
      insert into emp_bkp
      (empno,ename)
      values
      (v_emp.empno,v_emp.ename);
      
      delete from  emp_test where current of emp_cur;
      
     
  end loop;
   commit;
  close emp_cur;
  END;


Please find the program here,when i run this fom two different sessions i get the error

resource busy acquire no wait


now when the same thing will happen to the user
it will just hang or throw the same message from the application??


1. what is the difference if i do not use where current of?
2. what is the difference when i specify the column names after the for update clause and if i do n ot specify as in this case?
Re: Using rowid to insert [message #321568 is a reply to message #321566] Tue, 20 May 2008 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't loop on the cursor, just use "insert select" and "delete". I already post the whole stuff long ago in this topic.

And don't commit in your procedure. Only the caller knows if it wants to commit or not, not the procedure.

2. None. It is only useful when there are more that one table in the query.

Regards
Michel
Re: Using rowid to insert [message #321578 is a reply to message #321568] Tue, 20 May 2008 14:37 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
create or replace procedure pr_return_del_cursor(e_no number)
as
cursor emp_cur is
  select * from emp_test where empno=e_no for update;
  v_emp emp_cur%rowtype;
  lock_applied exception;
  begin
    open emp_cur;
       insert into emp_bkp select * from emp_test where empno=7902;
       
       delete from  emp_test where empno=7902;
        --mpno=v_emp.empno;
  close emp_cur;
   
  exception
    
  when others then
   raise lock_applied;
    
  dbms_output.put_line('row locks are on');
  END;


Hi I did this but now there is no commit,and when ever i try to truncate the emp_bkp table i get this error

SQL>  truncate table emp_bkp;
 truncate table emp_bkp
                *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


even after i have executed the procedure?why this is the case?

will this user defined exception work?
Re: Using rowid to insert [message #321579 is a reply to message #321568] Tue, 20 May 2008 14:38 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
if i specify a nowait clause will it help?
Re: Using rowid to insert [message #321584 is a reply to message #321578] Tue, 20 May 2008 15:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Hi I did this but now there is no commit,and when ever i try to truncate the emp_bkp table i get this error

Of course, locks are always there. As I said caller knows when to end the transaction but it must end it.

Quote:
when others then
raise lock_applied;

Don't do that. You hide the real error.
Use:
when others then 
if emp_cur%open then close emp_cur; end if;
raise;

Regards
Michel
Re: Using rowid to insert [message #321697 is a reply to message #321584] Wed, 21 May 2008 01:38 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
create or replace procedure pr_return_del_cursor(p_list_id varchar2)
as
cursor list_cur is
  select * from ace2_list_output_bkp where ALO_LIST_ID = p_list_id for update;
  
  lock_applied exception;
  begin
    open list_cur;
       insert into ace2_list_output_bkp_test 
	   select * from ace2_list_output_bkp where ALO_LIST_ID = p_list_id;
       
      delete from  ace2_list_output_bkp where ALO_LIST_ID = p_list_id;
        --mpno=v_emp.empno;
  close list_cur;
   
  exception
    
   when others then 
    if list_cur%isopen then 
	close list_cur; end if;
    raise;
    
  dbms_output.put_line('row locks are on');
  END;


Quote:

when others then
if emp_cur%open then close emp_cur; end if;
raise;


what will this do?
What i can understand that when the cursor will be open for a transaction and the corresponding rows will be locked,then if another user tries to do the same operation satisfying the select statement then it will come to this exception section.
am i right?

then here if the cursor is open it will close it,then
what it will raise?

I want to display a used defined message to the user2 if user 1 has locked the same set of rows in cursor?

Hi Michel

Please find this code,if i specify a nowait in the select for update then it will throw an exception immediately.Am i right?
Re: Using rowid to insert [message #321702 is a reply to message #321697] Wed, 21 May 2008 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what will this do?

It is a good practice to release the resource you took before leaving (unless the purpose is to return the resource itself, of course).

Quote:
what it will raise?

It will raise the error you made you enter the exception section.

Quote:
I want to display a used defined message

Why a user defined message?
Quote:
to the user2 if user 1 has locked the same set of rows in cursor

Just wait for the rows to be released.
Quote:
if i specify a nowait in the select for update then it will throw an exception immediately.

Not really immediatly, after it tries to lock the rows and find at least one is already locked.

Regards
Michel



Re: Using rowid to insert [message #321707 is a reply to message #321584] Wed, 21 May 2008 02:01 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member

Elapsed: 00:00:01.22
SQL>  truncate table ace2_list_output_bkp_test;
 truncate table ace2_list_output_bkp_test
                *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified



Quote:
Of course, locks are always there. As I said caller knows when to end the transaction but it must end it.



Is is the oracle proc caller that will end the locks?

But there must be a time when it will do otherwise how can we do operations on those tables that are having locks.

But strangely why it is not allowing me to truncate this table

Re: Using rowid to insert [message #321713 is a reply to message #321707] Wed, 21 May 2008 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is is the oracle proc caller that will end the locks?

The proc caller (or rather the end caller) is the master of the transaction, this is it that ends the transaction and so release the locks.

Quote:
But there must be a time when it will do otherwise how can we do operations on those tables that are having locks.

This is life n concurrent users environment, you have to wait the others finished.

Quote:
But strangely why it is not allowing me to truncate this table

Because truncating requires an exclusive lock on the table which cannot be granted when others are working on the table.

Regards
Michel
Re: Using rowid to insert [message #321746 is a reply to message #321713] Wed, 21 May 2008 04:00 Go to previous messageGo to previous message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
create or replace procedure pr_return_del_cursor(p_list_id varchar2)
as
cursor list_cur is
  select * from ace2_list_output_bkp where ALO_LIST_ID = p_list_id for update;
  
  lock_applied exception;
  begin
    open list_cur;
       insert into ace2_list_output_bkp_test 
	   select * from ace2_list_output_bkp where ALO_LIST_ID = p_list_id;
       
      delete from  ace2_list_output_bkp where ALO_LIST_ID = p_list_id;
        --mpno=v_emp.empno;
  close list_cur;
   
  exception
    when no_data_found then
    dbms_output.put_line('this is already deleted');

   when others then 
    if list_cur%isopen then 
	close list_cur; end if;
    raise;
    
  dbms_output.put_line('row locks are on');
  END;



Quote:
Why a user defined message?


because say the user is trying this from the application when there will be locks applied,for him the session hangs and
the end users will be delayed in their work wondering why this is so,
hence a message will be appropriate letting them know and to wait and try after sometime.


but say i am executing this procedure once
then it is done successfully but again when i do it with same values it doesnot throw error or exception as it again shows
proc successfully completed.

It should at least throw no_data_found exception????

But i have noticed one thing,when i put a commit on the session where locks are applied then i am able to truncate the table,so can't i put commit in the procedure after closing the cursor?


Previous Topic: view creation error
Next Topic: Just a little help for the question
Goto Forum:
  


Current Time: Fri Dec 09 13:41:40 CST 2016

Total time taken to generate the page: 0.16204 seconds