Need help with PL/SQL procedure 1

[lcapece]

Although I have SQL/DBA experience in Oracle8, I am not versed in PL/SQL; pehaps someone can help:
I need to create function to build a "word list index" by extracting all unique words from a varchar2 field. (I realize Context/Intermedia will do this, however I can not install it at this time).
Basically I have a table (PRODUCT_ALL) as example:

MODEL_NUMBER: A234244 (primary key)
ITEM_DESC: SERIAL MOUSE 3-BUTTON WITH MOUSE PAD

I want to write out to a table called WORD_LIST:

TABLE_NAME: PRODUCT_ALL
KEY: A23444
WORD: SERIAL
~~~
TABLE_NAME: PRODUCT_ALL
KEY: A23444
WORD: MOUSE
~~~
TABLE_NAME: PRODUCT_ALL
KEY: 3-BUTTON
WORD: SERIAL
~~~
TABLE_NAME: PRODUCT_ALL
KEY: 3-BUTTON
WORD: WITH

...and so on, just unique words (MOUSE appears once)
Obviously, this needs to loop the entire table to collect every row.
Any help in coding this procedure is greatly appreciated.

Thanks
Louis Capece

 

[MikeJones]

First of all you will need to write a function that given a string and a start poistion will return the first word it finds. This will need to use instr to find the space. You can then loop through the string in plsql getting a word each time.

Then you could

Loop
get a row from the table
if no more exit
loop
get next word(start postion) -- I'd have this as a function I think it would be easier to code
if not already in table then insert
if no more words exit
end loop
end loop

If you want more detail on any of this then ask and I can elaberate more.

Mike.

 

[Nebuchednezzar]

The following should do pretty much what you are looking for...However, you're desired results look a little off from what I would imagine you are trying to do.
It looks like you are taking one description, and in some cases you are using the model as the key value, and in others, you are using a word from the description as the key value.
This code will always have the model as the key value. You will end up with values in your table like...
MODEL WORD
-------------------- --------
A234244 SERIAL
A234244 MOUSE
A234244 3-BUTTON
A234244 WITH
A234244 PAD


Here you go...


DECLARE
v_bgn_pos number;
v_end_pos number;
v_last_blank number;
v_total_length number;
v_word varchar2(30);
v_count number;
cursor c_model is
select model, item_desc from product_all;

BEGIN

--Loop through each record in the product_all table.

FOR v_model in c_model
LOOP

select length(item_desc)
into v_total_length
from product_all
where model = v_model.model;

select instr(item_desc, ' ', -1)
into v_last_blank
from product_all
where model = v_model.model;

v_bgn_pos := 1;
v_end_pos := instr(v_model.item_desc, ' ', v_bgn_pos) -1;
v_last_blank := v_last_blank + 2;

--Internal loop.
--Loops to go through each word in the item description for the current model.

WHILE v_bgn_pos < v_last_blank
LOOP
v_word := substr(v_model.item_desc, v_bgn_pos, v_end_pos);

--See if the entry already exists in the word_list table.
--If it does, do nothing. Else, insert the record.

select count(*) into v_count
from word_list
where model = v_model.model
and word = v_word;

IF v_count = 0 then
insert into word_list
values (v_model.model, v_word);
ELSE
null;
END IF;

--Set the variables to the next beginning and ending position
--for the search within the description before repeating
--the inner loop.

v_bgn_pos := v_bgn_pos + v_end_pos + 1;

--the setting of the v_end_pos variable here is a little tricky.
--The decode is used to determine if this is the last word
--(if no space can be found from the instr function, it will return a
--value of 0. Therefore, if 0 is returned, substitute the value of
--the number of characters of the last word in the string.
--(if the length is 30, the beggining postion is 27, then it will
--return 30-27+1 = 4...meaning, it will read positions 27, 28, 29, and 30.

select decode(instr(v_model.item_desc, ' ', v_bgn_pos),
0, v_total_length - v_bgn_pos + 1,
instr(v_model.item_desc, ' ', v_bgn_pos) -v_bgn_pos)
into v_end_pos
from dual;

commit;
END LOOP;
END LOOP;
END;
/

 

[lcapece]

Nebuchednezzar ,
Thanks VERY MUCH!
This procedure works great.

I am now able the seach any embedded word within the description of a product in under 5 seconds. The main table has about 480K rows and the index table has 5.2M rows.

I would suggest this procedue to anyone who needs high performace text searches, but does not have acess to a context or intermedia text server.