oracle date functions

[luciffer]

I'm make a stored procedure that performs search functions. The procedure has the following parameters:

pi_search_text VARCHAR2 (text to search for)
pi_search_period NUMBER (0-4)

The table I am searching on looks like this

OMS_PARAGRAPHS
---------------------
ID NUMBER(12)
NAME VARCHAR(256)
TEXT (CLOB)
DATEUPDATED (DATE) default = SYSDATE

I have a an index called omsprgrphs_search_ctx_idx on dummy coulmn(NameTextIndex) with a multi column datastore as attribute that allows me to search on the name and text columns simultaneously. My problem is the the search period, the number 0-4 represents a time frame:

0 = get records updated in last 1 day
1 = get records updated in last 1 week
2 = get records updated in last 1 month
3 = get records updated in last 6 months
4 = get records updated in last 1 year

I'm not sure how to incorporate the search period into my sql statement.

Select op.id, op.name, op.text
From oms_paragraphs op
Where contains (op.NameTextIndex, pi_search_text, 1) > 0;

Any ideas?

[gandolf989]

Does this help?

...
   v_since_date DATE;
   CURSOR c_data IS
     Select op.id, op.name, op.text
       From oms_paragraphs op
     Where DATEUPDATED >= v_since_date
         and contains (op.NameTextIndex, pi_search_text, 1) > 0;
BEGIN
   CASE pi_search_period
      WHEN 0 THEN
         v_since_date := TRUNC(SYSDATE) - 1;
      WHEN 1 THEN
         v_since_date := TRUNC(SYSDATE) - 7;
      WHEN 2 THEN
         v_since_date := TRUNC(SYSDATE) - 30;
      WHEN 3 THEN
         v_since_date := TRUNC(SYSDATE) - 180;
      WHEN 4 THEN
         v_since_date := TRUNC(SYSDATE) - 365;
      ELSE
--      Raise exception
   END CASE;
   FOR v_row IN c_data
   LOOP
      ...
   END LOOP;
...

[luciffer]

that will work perfect. thank you.