| Please help me with this proc [message #64565] |
Tue, 08 February 2005 11:23  |
VJ
Messages: 24
Registered: June 2002
|
Junior Member |
|
|
hello,
can any one help me with this proc please...
want to write a proc that will take 2 date cols as parameters
startdate, enddate and it should display the output as below ...
exec callproc('01-nov-2004','01-feb-2005');
then it should display
nov2004
dec2004
jan2005
feb2005
Thanks a ton in advance.. Its really urgent...
-VJ
|
|
|
|
| Re: Please help me with this proc [message #64567 is a reply to message #64565] |
Tue, 08 February 2005 20:54  |
raghukalyan
Messages: 44
Registered: May 2004
|
Member |
|
|
Hi VJ,
Here is your proc..
CREATE OR REPLACE PROCEDURE CallProc
(
p_StartDt Date,
p_EndDt Date
)
AS
v_Months NUMBER;
v_OutPut DATE;
BEGIN
v_Months := trunc(months_between(p_EndDt,p_StartDt));
DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_StartDt,'monYYYY'));
FOR i in 1..v_Months
LOOP
v_Output := add_months(p_StartDt,i) ;
DBMS_OUTPUT.PUT_LINE(to_char(v_Output,'monYYYY'));
END LOOP;
END CALLPROC;
/
The above proc was run in prompt ..
SCOTT>CREATE OR REPLACE PROCEDURE CallProc
2 (
3 p_StartDt Date,
4 p_EndDt Date
5 )
6 AS
7 v_Months NUMBER;
8 v_OutPut DATE;
9 BEGIN
10 v_Months := trunc(months_between(p_EndDt,p_StartDt));
11 DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_StartDt,'monYYYY'));
12 FOR i in 1..v_Months
13 LOOP
14 v_Output := add_months(p_StartDt,i) ;
15 DBMS_OUTPUT.PUT_LINE(to_char(v_Output,'monYYYY'));
16 END LOOP;
17 END CALLPROC;
18 /
Procedure created.
SCOTT>set serveroutput on
SCOTT>exec callproc('01-nov-2004','01-feb-2005');
nov2004
dec2004
jan2005
feb2005
PL/SQL procedure successfully completed.
Hope this solution solves ur requirement.Revert to me if you need any clarifications regarding the same.
Any other solutions are accepted..
Thanks and Regards,
GRK.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
