I'm doing Oracle 8i database search using Intermedia. I wrote a procedure for Search which should display the search results in the descending order of the score.

CREATE OR REPLACE PROCEDURE SP_ADV_KEYWORD_SEARCH(
ErrorStatus OUT Number,
VKeyword IN Varchar2,
TotalRecords OUT Number,
SearchResults OUT Types.cursortype)
AS
RCURSOR TYPES.CURSORTYPE;
LItemCount NUMBER;
COMMA NUMBER;
LNewSearchString Varchar2(4000);
LFinalSearchString Varchar2(4000);
lcount integer := 0;
mcount integer := 0;
vcatgid integer;
vclassid integer;
BEGIN
--replace all the spaces with ','
LNewSearchString := Replace(trim(VKeyword),' ',',');

--remove all unwanted spaces before and after
LNewSearchString := NVL(trim(VKeyword),' ');

--replace ',' with spaces
LNewSearchString := REPLACE(LNewSearchString,',',' ');

--if any special characters or any functions are available replace
--them with '\'
If upper(trim(LNewSearchString)) IN ('$','!','?','OR','||','RT','=',
'BT','&',',',
'ABOUT','=',';','*','>','-','~','WITHIN','AND','|','ACCUM') then
LNewSearchString := replace(LNewSearchString,
LNewSearchString,
'\'||LNewSearchString);
end if;
--replace all the spaces with commas
LNewSearchString := Replace(TRIM(LNewSearchString),' ',',');

If NVL(LNewSearchString,' ') <> ' ' then

LNewSearchString:= REPLACE(upper(LNewSearchString),',OR,',' OR ');
LNewSearchString:= REPLACE(upper(LNewSearchString),',AND,',' AND ');
LNewSearchString:= REPLACE(upper(LNewSearchString),',ABOUT,',' ABOUT
');
LNewSearchString:= REPLACE(upper(LNewSearchString),',NEAR,',' NEAR ');

LFinalSearchString:= ''''||LNewSearchString||'''';
--LFinalSearchString:=LNewSearchString;
--CheckComma(LNewSearchString,LFinalSearchString);
dbms_output.put_line(LFinalSearchString);

--check if matching records are available
select count(*) into LItemCount from
(select search_data.* from search_data where
contains(item_id, LFinalSearchString, 1) > 0 AND contains(item_title, LFinalSearchString, 1) > 0 AND contains(item_author, LFinalSearchString, 1) > 0 AND contains(item_content, LFinalSearchString,1)>0 AND contains(item_publisher, LFinalSearchString,1)>0 AND contains(item_remarks, LFinalSearchString,1) > 0 and item_status = 'Y');

--if matching records are available
If LItemCount > 0 Then
OPEN RCURSOR FOR select item_id, Item_title,
Item_author,category_name, usage_type,room,slot_no,
item_availability,Item_content from

(select item_id, item_title, item_author, category_name, usage_type, room, slot_no, item_availability, item_content, score(1) as the_score from search_data where
contains(item_id, LFinalSearchString, 1) > 0 AND contains(item_title, LFinalSearchString, 1) > 0 AND contains(item_author, LFinalSearchString, 1) > 0 AND contains(item_content, LFinalSearchString,1)>0 AND contains(item_publisher, LFinalSearchString,1)>0 AND contains(item_remarks, LFinalSearchString,1) > 0 and item_status = 'Y') order by score(1) desc;

ErrorStatus := 0; --Items available for the Keyword
TotalRecords := LItemCount;
SearchResults := RCURSOR;
else
ErrorStatus:=1010; -- No Items matching your search criteria
End If;
else
ErrorStatus:=1010; -- No Items matching your search criteria
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ErrorStatus := 1010; -- No Items matching your search criteria
WHEN OTHERS THEN
ErrorStatus:=3001;
END;
/

While executing it is displaying the following message at SQL prompt -
SQL> exec sp_adv_keyword_search(:r, 'jsp', :t, :sr);
BEGIN sp_adv_keyword_search(:r, 'jsp', :t, :sr); END;

*
ERROR at line 1:
ORA-29907: found duplicate labels in primary invocations
ORA-06512: at "LIB.SP_ADV_KEYWORD_SEARCH", line 52
ORA-06512: at line 1.

Can anybody tell me what is the problem and at the same time please give me a solution for th problem I'm facing. I'll be waiting for your earliest reply. This is very urgent.
Thanking you in advance.

hi, cannot reproduce your problem since i do not have the search_data table ddl, but i would :
rewrite:

select count(*) into LItemCount from
(select search_data.* from search_data where

to

select count(*) into LItemCount from search_data where ...)