Home » SQL & PL/SQL » SQL & PL/SQL » Lock the records While deleting (oracle 10g)
Lock the records While deleting [message #425403] Fri, 09 October 2009 00:59 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi friends ,

Need one help

I have a some big tables . I want to delete records from them .
Theere are parent-child relationship in some of the tables.

I want to lock the records ,while deleting those from a table.


I have craetaed a package
CREATE OR REPLACE PACKAGE DB_UTILITY IS

PROCEDURE  del_tab(v_tab_name     varchar2,
                   v_col_name     varchar2);
END DB_UTILITY; 
/
create or replace PACKAGE BODY DB_UTILITY IS
PROCEDURE  del_tab(v_tab_name     varchar2,
                   v_col_name     varchar2)  IS
                 
sql_stmnt varchar2(100);
v_condition varchar2(100);
BEGIN
  v_condition:='select item from DRS_ITEM_PURGE_TEMP';
 -- execute immediate 'delete from ' ||v_tab_name||
 --  'where ' || v_col_name || ' in (11,12) ';
sql_stmnt:='delete from ' ||v_tab_name||
  ' where ' || v_col_name || ' in ( '|| v_condition|| ')' ;
dbms_output.put_line (sql_stmnt);

 commit;
END del_tab;
    
END DB_UTILITY;
/
show errors

Created a table del_tab which ll keep the information of deleted tables
create table del_tab (tab_name varchar2 (40), col_name varchar2 (40));

The following procedure ll perform the delete Task but it doesnot lock the records.
cursor table_list_cur
select * from del_tab;
Begin 
  for r_table_list_cur in cur_table_list loop
  
  BEGIN 
  open c1;
  close c1;
  DB_UTILITY.del_tab(r_table_list_cur.tab_name ,r_table_list_cur.col_name);
  END;
  end loop;
 --exception
    
end;
Re: Lock the records While deleting [message #425404 is a reply to message #425403] Fri, 09 October 2009 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle locks the rows you delete, you have nothing to do.

Regards
Michel
Re: Lock the records While deleting [message #425405 is a reply to message #425404] Fri, 09 October 2009 01:20 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
Thanks Michel
Re: Lock the records While deleting [message #425406 is a reply to message #425403] Fri, 09 October 2009 01:24 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
Hi all,

What is that DRS_ITEM_PURGE_TEMP, is it another table name...?

And in this statement

sql_stmnt:='delete from ' ||v_tab_name||
  ' where ' || v_col_name || ' in ( '|| v_condition|| ')' ;


In where condition if using IN, then the values should be like ('1','2') this rite,
the condition you given will not satisfy i think.

Correct me if am wrong

thanks
sen
Re: Lock the records While deleting [message #425407 is a reply to message #425403] Fri, 09 October 2009 01:31 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
my delete synatax is

delete from tab whete col_name in
(select item from DRS_ITEM_PURGE_TEMP);

Re: Lock the records While deleting [message #425411 is a reply to message #425403] Fri, 09 October 2009 01:47 Go to previous message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
Add this statement
  EXECUTE IMMEDIATE sql_stmnt ;

I hope this will work

Previous Topic: Change code from oracle to sqlserver 2008
Next Topic: BUlk Delete
Goto Forum:
  


Current Time: Fri Dec 02 14:31:15 CST 2016

Total time taken to generate the page: 0.30840 seconds