Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    Bangalore
    Posts
    2

    Question Unanswered: Can anybody give a solution for this ??

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37
    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 ...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •