-
oracle date functions
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?
-
Does this help?
Code:
...
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;
...
-
that will work perfect. thank you.