oracle date functions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: oracle date functions

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    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?

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    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;
    ...
    this space intentionally left blank

  3. #3
    Join Date
    Dec 2003
    Posts
    90
    that will work perfect. thank you.

Posting Permissions

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



Click Here to Expand Forum to Full Width