SQL & PL/SQL

Hello,

I am building a stored procedure to search for records in a table. The column in question is a varchar2(150) called short_description.

What I need is a sp that I can pass a string to, and it will return a resultset(refCursor) with the words split out so that I can build individual 'and' statements in my 'where' clause for each word.

For example, this search string 'here are words' would have this result:

and short_description like '%here%'
and short_description like '%are%'
and short_description like '%words%'

I am working on the sp that will return the refCursor:

CREATE OR REPLACE  PROCEDURE "SYSADM"."SP_TEST" (input_string_in 
    varchar2, out_cursor out types.cursorType)
AS
 
BEGIN
  open out_cursor for
  SELECT substr(input_string_in, 1, instr(input_string_in, ' ')-1) as "1st chop", 
         substr(input_string_in, instr(input_string_in, ' ')+1) as "Remnant"
  FROM dual;

END;

when I call it like this:

variable cursor_out refCursor;
set serveroutput on
set autoprint on
execute SP_TEST('here are words',:cursor_out);

I get this output:

1st chop   Remnant
--------   ----------
here       are words

1 row selected.

what I need help with is the following:

1. how do i find the length of input_string_in and the 'remnant' so that I can build a looping structure to get each of the words?

2. once I have one of the words, how do I add it to out_cursor?

3. in the search sp, how do I loop through the cursor that I got from this splitting proc to build the 'and' statements for the where clause?

Thanks for your help.

James.