View Poll Results: What do you think of this thread. Is it helpfull?

Voters
1. You may not vote on this poll
  • Yes

    1 100.00%
  • No

    0 0%
  • Not Sure

    0 0%
Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    kuala lumpur, malaysia
    Posts
    2

    Question Unanswered: sql searching library catalog

    Hi All,

    Does anyone has an idea on how to search a library catalog (using stored procedure, of course) according to the following rules:

    1) Suppose we have a list of library catalog containing titles below :

    - All about java
    - Java for dummies
    - The java kingdom
    - A man from java

    2) When a user keyed-in the search text, the PL/SQL should be able to
    search for the first occurence of the search text.

    3) For example, if the search text is "java" , the stored procedure will
    return books with title:
    - Java for dummies
    - The java kingdom ( the word at the beginning of the title such as
    "the", "a" and "an" is ignored)

    4) If the search text is "man", the following title is returned, ignoring
    the word "A" :
    - A man from java

    Does anyone has an idea on how to solve this problem?

    Thanks in advance.

    P/S : Below is the DDL for the package.

    -----------------------------------------------------------------------------
    CREATE OR REPLACE PACKAGE "BAKRI"."PKG_SEARCH" AS
    TYPE CUSTOM_REF_CURSOR IS REF CURSOR;


    PROCEDURE By_Title(arg_Title varchar2,
    arg_cursor IN OUT CUSTOM_REF_CURSOR);
    END;

    CREATE OR REPLACE PACKAGE BODY "BAKRI"."PKG_SEARCH" AS


    -- START OF PROCEDURE BY_TITLE

    PROCEDURE By_Title(arg_Title varchar2,
    arg_cursor IN OUT CUSTOM_REF_CURSOR) is

    v_title varchar2(250);

    BEGIN

    v_title := UPPER(arg_Title);

    OPEN arg_cursor FOR
    SELECT LIBRARY_MATERIAL.MAT_CODE,
    LIBRARY_MATERIAL.MAT_TITLE,
    LIBRARY_MATERIAL.MAT_PLACE,
    LIBRARY_MATERIAL.MAT_PUBLISHER,
    LIBRARY_MATERIAL.MAT_YEAR,
    AUTHOR.AUTHOR_LASTNAME,
    AUTHOR.AUTHOR_FIRSTNAME
    FROM LIBRARY_MATERIAL,AUTHOR
    WHERE UPPER(LIBRARY_MATERIAL.MAT_TITLE)
    LIKE UPPER('%' || arg_Title || '%')
    AND AUTHOR.MAT_CODE = LIBRARY_MATERIAL.MAT_CODE
    AND AUTHOR.AUTHOR_TYPE = 'Main Entry'
    ORDER BY LIBRARY_MATERIAL.MAT_TITLE;


    END By_Title;

    -- END OF PROCEDURE BY_TITLE
    END pkg_search;
    -----------------------------------------------------------------------------

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    hmmm, i do not see any good possibilities. You might try to substring the first word and if that is a word like 'the','a' or somthing like that get the second word to compare it to the search string. Is not fast !

    Another and better solution is alter the LIBRARY_MATERIAL table and add a column which can contain the search strings for that title. This can contain the word JAVA, MAN etc in a certain format. Then just query this column with the search string and show the title.

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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