Need 2 solutions, parameter or no parameter.

[MrHelpMe]

Oh experts,

I am finally on my way with Oracle. I've tossed everything I know about SQL Server and strictly focusing on Oracle. So I need help with the following. I have created a package with a procedure and I need to know how to find all data for the prior week starting from Sunday to Saturday. So today is Thursday April 20th 2006. I would need to capture all data From Sun Apr 9, 2006 - Sat Apr 15th 2006. I think you get the idea. I have the following Stored procedure in place. However, it becomes more complicated because the date field stored is an epoc date(1025273900). Damn Remedy. I know I have to do some conversion before I can capture last week's data but my conversion doesn't account for daylights savings time.
Lastly, what if I wanted to add a parameter called NumberofWeeks and let the user decide how many weeks back they want to go to collect the data. I.e. 2 weeks would mean get all data from Sun April 2, 2006 - Sat April 15th 2006. Your help is greatly appreciated. Sorry for all the questions.

CREATE OR REPLACE PACKAGE GroupsPackage
AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE Test(results_cursor IN OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE
PACKAGE BODY GroupsPackage
AS
PROCEDURE Test(results_cursor IN OUT CURSOR_TYPE)
IS
BEGIN
OPEN results_cursor FOR
SELECT Entry_id, create_date, submitter
FROM Shr_people
WHERE Create_Date Between ?
ORDER BY Entry_Id;
END;
END;
/

 

[avjoshi]

Try this. I made a few changes to your code because I personally prefer to use functions whenever there is a return value.

I haven't had a chance to test the package myself. May be, the resident experts will have better solution for you

CREATE OR REPLACE PACKAGE GroupsPackage 
AS
  TYPE CURSOR_TYPE IS REF CURSOR;
  FUNCTION Test(Number_of_weeks Number)
  return results_cursor; 
END; 
/
CREATE OR REPLACE
PACKAGE BODY GroupsPackage 
AS
  Function Test(Number_of_weeks Number)
  return results_cursor;
  IS
  Number_of_days Number := 0;
  Begin_date Date;
  End_date Date;
  BEGIN
    Number_of_days := ( Number_of_weeks * 7 ) -6;
    Begin_date := NEXT_DAY(sysdate - Number_of_days, 'SUNDAY');
    End_date := NEXT_DAT(Begin_date, 'SATURDAY);
    OPEN results_cursor FOR
       SELECT Entry_id, create_date, submitter
       FROM Shr_people
       WHERE Create_Date Between Begin_date and End_date
       ORDER BY Entry_Id;
  END;
END;
/

Anand

 

[MrHelpMe]

Well Anand,

I tried to copy and paste the code with no luck. I'm still not sure on the function part. Doesn't the function need to be create and deployed first before it can be used. Sorry excuse my ignorance but just trying to understand what is happening. Thanks again for replying Anand.

 

[MrHelpMe]

Hello again experts,

Has anyone had any luck on this. Please your help is appreciated. Thanks again.

 

[LKBrwnDBA]

To get data from Sun-Sat use TRUNC() function:

CREATE PRO...etc...
   SELECT ... FROM ...
    WHERE create_date
  BETWEEN TRUNC(SYSDATE,'DAY') AND TRUNC(SYSDATE+7,'DAY')-1
...etc...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Ooops, justt realized it is for PREVIOUS weeks:

CREATE PRO...etc...
   SELECT ... FROM ...
    WHERE create_date
  BETWEEN TRUNC(SYSDATE-7*NumberofWeeks,'DAY') 
      AND TRUNC(SYSDATE,'DAY')-1
...etc...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MrHelpMe]

Thanks LKBrwnDBA,

Wow that is great!!!!! What if I didn't want to add a parameter and just wanted to find out last weeks data without the parameter prompt. Also, I know I am pushing it, but do you know of anything for epoch conversion. The create date in your select needs to be converted. Thanks again LKBrwnDBA. You've been helping me a lot.

 

[MrHelpMe]

Sorry again,

I am having one of those days. I figured it out. Just remove the parameter and all is good. I should have looked at it before I wrote the email.
Anyways, if you have anything for epoch I would definitely be appreciated.

 

[LKBrwnDBA]

Try these for epoch:

http://search.cpan.org/~pijll/DateTime-Format-Epoch-0.09/

http://www.perlservices.net/en/programs/epoch_converter/epoch_converter.html

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MrHelpMe]

Thanks again LKBrwnDBA. Your help is always appreciated.