Home » SQL & PL/SQL » SQL & PL/SQL » First Unused Row
First Unused Row [message #36613] Thu, 06 December 2001 04:52 Go to next message
Drraagh
Messages: 2
Registered: December 2001
Junior Member
I am working on a PL/SQL program that will locate, and return the value of the first unused slot in a table. All entries have a unique numeric key between 1 and 999.

I was wondering if there was an easy way to do it other than by using exception no_data_found.

----------------------------------------------------------------------
Re: First Unused Row [message #36616 is a reply to message #36613] Thu, 06 December 2001 05:28 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
get select count(*) of query. if it is zero means no data.

----------------------------------------------------------------------
Re: First Unused Row [message #36618 is a reply to message #36616] Thu, 06 December 2001 05:49 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
declare
col1_match varchar2(10);
l_count number;
begin
for i in 1..999 loop
col1_match := '#'||to_char(i);
select count(*) into l_count
from table_name where col1=col1_match;
if l_count=0 then
dbms_output.put_line(col1_match||'not found');
end if;
end loop;
end;

----------------------------------------------------------------------
Re: First Unused Row [message #36620 is a reply to message #36616] Thu, 06 December 2001 07:10 Go to previous message
Drraagh
Messages: 2
Registered: December 2001
Junior Member
Thanks.

----------------------------------------------------------------------
Previous Topic: PL/SQL (ORA-1410) Deleting Several Hundred Thousand Record
Next Topic: Procedure and functions
Goto Forum:
  


Current Time: Thu Mar 28 04:27:11 CDT 2024