simple procedure help needed

[portal]

Folks,
I inherited this procedure which updates column TLC with our weeks generated from function calculate_week. I'm pretty dumb in PL/SQL so please help in making this procedure work as this is updating Null values into column. Our create_date column is of Timestamp with local timezone data type.-Oracle 9i rel2 on Nt

CREATE OR REPLACE PROCEDURE TEST_WEEK
AS
V_CREATE_DATE NUMBER;
V_SEQ_ID NUMBER;
CURSOR getcrsrww IS
SELECT CALCULATE_WEEK(TO_CHAR(CREATE_DATE,'DD-MON-YY HH:MI:SSXFFAM')) INTO V_CREATE_DATE FROM BIN_NOT
WHERE TIN_SEQ_ID =V_SEQ_ID;
BEGIN
UPDATE BIN_NOT SET TLC = V_CREATE_DATE;
END TEST_WEEK;

Thanks

[kris123]

Hun, why is the cursor not open? Is it possible to access data
without opening it? Can u please list the body of the function
CALCULATE_WEEK.

[portal]

This function when executed yeilds the results correctly as per our rqmnt. Only thro procedure its giving a problem with Nulls.
create or replace function CALCULATE_WEEK
(P_DATE IN BIN_BOT.CREATE_DATE%TYPE
)
RETURN NUMBER
IS
last_day_in_the_week BIN_BOT.CREATE_DATE%TYPE;
last_day_in_the_first_week BIN_BOT.CREATE_DATE%TYPE;
number_of_weeks_in_between number;
begin
last_day_in_the_week := next_day(p_date-1,'saturday');
last_day_in_the_first_week := next_day(trunc(last_day_in_the_week,'yyyy')-1,'saturday');
number_of_weeks_in_between := ( to_number(to_char(last_day_in_the_week,'ddd'))
-
to_number(to_char(last_day_in_the_first_week,'ddd'))) / 7;
return ( to_number( to_char(last_day_in_the_first_week,'yyyy') ) * 100
+ number_of_weeks_in_between + 1 );
end;

Thanks

[kris123]

can u identify the fields/columns that contains nulls and are giving
u problems? u can apply nvl(fld1,0) to it

[kris123]

or put condition filter "where col1 is not null"

[portal]

After this modification also its updating null values, please advice me guys


CREATE OR REPLACE PROCEDURE TEST_WEEK
AS
V_CREATE_DATE NUMBER;
V_SEQ_ID NUMBER;
CURSOR getcrsrww IS
SELECT CALCULATE_WEEK(TO_CHAR(CREATE_DATE,'DD-MON-YY HH:MI:SSXFFAM')) INTO V_CREATE_DATE FROM BIN_NOT
WHERE TIN_SEQ_ID =V_SEQ_ID;
BEGIN
OPEN getcrsrww;
fetch getcrsrww into V_CREATE_DATE;
UPDATE BIN_NOT SET TLC = V_CREATE_DATE;
close getcrsrww;
END TEST_WEEK;

[kris123]

u only need guys help
did u know that ladies are the topnotch in exams