simple procedure help needed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: simple procedure help needed

  1. #1
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87

    simple procedure help needed

    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
    Last edited by portal; 03-21-2005 at 07:22 PM.
    sat

  2. #2
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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.

  3. #3
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87
    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
    sat

  4. #4
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    can u identify the fields/columns that contains nulls and are giving
    u problems? u can apply nvl(fld1,0) to it

  5. #5
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    or put condition filter "where col1 is not null"

  6. #6
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87
    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;
    sat

  7. #7
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    u only need guys help
    did u know that ladies are the topnotch in exams

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