Home » SQL & PL/SQL » SQL & PL/SQL » How to DELETE The data FROM a PL/SQL table
How to DELETE The data FROM a PL/SQL table [message #222731] Tue, 06 March 2007 04:19 Go to next message
mallikarjun_naidu
Messages: 3
Registered: March 2007
Location: Hyderabad
Junior Member

Hello Frnds,
Can any one help me on how to delete the row from a pl/sql table.
i used the option of <table_name>.delete <index_value>. Using this the values in the row are deleting , but the row remains there. how can i trim that row, so next time when perform the select statement if won't appear for me.
Re: How to DELETE The data FROM a PL/SQL table [message #222735 is a reply to message #222731] Tue, 06 March 2007 04:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, DELETE works for me:
SQL> set serveroutput on size 100000
SQL> declare
  2    type ty_tab is table of number index by pls_integer;
  3    
  4    t_tab  ty_tab;
  5  begin
  6    t_Tab(1) := 1;
  7    t_Tab(2) := 2;
  8    t_tab(3) := 3;
  9    
 10    if t_tab.exists(2) then
 11      dbms_output.put_line('Test 1. Entry 2 exists');
 12    else
 13      dbms_output.put_line('Test 1. Entry 2 does not exist');  
 14    end if;
 15    
 16    t_tab.delete(2);
 17  
 18    if t_tab.exists(2) then
 19      dbms_output.put_line('Test 2. Entry 2 exists');
 20    else
 21      dbms_output.put_line('Test 2. Entry 2 does not exist');  
 22    end if;  
 23  end;
 24  /
Test 1. Entry 2 exists
Test 2. Entry 2 does not exist


How about you give us some more information about what you're doing, what collection type you're using, and what the problem you're getting is.
Re: How to DELETE The data FROM a PL/SQL table [message #222760 is a reply to message #222735] Tue, 06 March 2007 05:45 Go to previous messageGo to next message
mallikarjun_naidu
Messages: 3
Registered: March 2007
Location: Hyderabad
Junior Member

But i need to use the select statement on the table ty_tab.

caz i need to return a cursor which gives the values from the table. In that case what are the possible ways for me.
Re: How to DELETE The data FROM a PL/SQL table [message #222775 is a reply to message #222760] Tue, 06 March 2007 06:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How about you come up with a nice simple test case and show us what the problem is. There are several ways of selecting from Collections. (it doesn't sound like you're using a pl/sql table, but I might be misunderstanding you), and I've got no idea which one you're using.
Re: How to DELETE The data FROM a PL/SQL table [message #222793 is a reply to message #222775] Tue, 06 March 2007 06:51 Go to previous messageGo to next message
mallikarjun_naidu
Messages: 3
Registered: March 2007
Location: Hyderabad
Junior Member

Hello Mr.JRow,
Sorr, i Don't know your name. First of all let me say thanx for the sample code that you sent, and spending your time for me.

About the Select Yes its working , actually i missed giving the Exists statement in a part of my code thats why i got the exception of no data found. Now its working

Once again Thnx for your code.
Re: How to DELETE The data FROM a PL/SQL table [message #222801 is a reply to message #222731] Tue, 06 March 2007 07:37 Go to previous messageGo to next message
subratd
Messages: 12
Registered: March 2007
Junior Member
The only way to actually empty a PL/SQL table of all rows is to perform an aggregate assignment with a table that is empty i.e
declare another pl/sql table of the same structure as ur initial pl/sql table but dont assign any values to the new table.

Then simply assign the new table to the first pl/sql table.

empty_tab := need_to_empty_table.

This is the closest you can come to dropping a PL/SQL table
Re: How to DELETE The data FROM a PL/SQL table [message #222803 is a reply to message #222801] Tue, 06 March 2007 07:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
subratd wrote on Tue, 06 March 2007 14:37
The only way to actually empty a PL/SQL table of all rows is to perform an aggregate assignment with a table that is empty

The ONLY way???

How about deleting all rows in it? How about just a table.delete?

Using an empty table is the ancient way!
Re: How to DELETE The data FROM a PL/SQL table [message #222805 is a reply to message #222803] Tue, 06 March 2007 07:53 Go to previous messageGo to next message
subratd
Messages: 12
Registered: March 2007
Junior Member
Correct, its an ancient way. We can go for table.delete for achieving the same. Since i encountered this in one of my maintenance project, it just struck my mind.
Re: How to DELETE The data FROM a PL/SQL table [message #222940 is a reply to message #222805] Wed, 07 March 2007 00:35 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry to be nitpicking, but instead of 'we can go for table.delete to achieve the same' I think we SHOULD use it. No more nonsense declaration of empty tables. Keep your code clean.
Previous Topic: PLS-00103: Encountered the symbol "CREATE"
Next Topic: Cursor Memory Reserve
Goto Forum:
  


Current Time: Thu Dec 08 12:47:27 CST 2016

Total time taken to generate the page: 0.07366 seconds