Passing Date Paramaters... Urgent!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Passing Date Paramaters... Urgent!

  1. #1
    Join Date
    Mar 2001
    Posts
    26
    Hi guys,

    How do you pass a date (with time) paramater to a procedure??

    I have a packages with a procedure that takes in two input paramater that are defined as DATE and third paramater as a refcursor. I execute the following:

    SQL> var a refcursor
    SQL> exec packages_pkg.p_proc('15-JUN-01', '15-JUN-01', :a)


    The above works... however, I need to pass the time as well. I tried every combination... nothing seems to work.

    Any help would greatly be appreciated.

    Thanks.
    - Gary

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    exec packages_pkg.p_proc(to_date('15-JUN-01:03:31','DD-MON-YY:HH:MI'), '15-JUN-01', :a)
    Oracle DBA and Developer

  3. #3
    Join Date
    Mar 2001
    Posts
    26
    This is absolutely mind-boggling. When I execute the following statement in SQL*PLUS, I get different results than what the front-end application gives me. The front end gives me correct results. Testing in SQL*PLUS gives me incorrect results. Is this the proper way to send dates(with time) to a procedure?

    SQL> var a refcursor
    SQL> exec package_pkg.p_proc(to_date('15-JUN-01:03:00','DD-MON-YY:HH:MI'), to_date('15-JUN-01:03:30','DD-MON-YY:HH:MI'), :a)

    Within the procedure I check both of these dates against dates in a specific table that are defined as DATE.

    Any help would great be appreciated.

    Thanks,
    - Gary






  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Can you post the PL/SQL code please.

    - Chris

  5. #5
    Join Date
    Mar 2001
    Posts
    26
    The code is rather complicated. Essentially, what it does is check the availability of crew members based on a specific start date and end date. For example, if crew member John Doe is shooting a video on June 26th at 3:00 to 3:30, the procedure should flag him unavaliable if I try to book him for a different event between 3:00 to 4:00. Any overlaping of time should make him unavailable. The procedure is broken up into two SELECT statements, one for available crew members, and one for unavailable crew members for the specific dates being passed to the procedure.

    -------------------------------------------------

    procedure p_AvailableCrew_Get
    (v_StartDateTime IN Video.StartDateTime%TYPE,
    v_EndDateTime IN Video.EndDateTime%TYPE,
    out_Inventory out cur_Inventory)

    is
    cv_Inventory cur_Inventory;
    err_num number;
    err_msg varchar2(200);
    UnavailableFlag number := 0;
    AvailableFlag number := 1;

    begin
    OPEN cv_Inventory FOR
    SELECT DISTINCT tu.UserID,
    tu.UserFirstName,
    tu.UserLastName,
    AvailableFlag
    FROM CSFBTVAdmin.CSFBTVUser tu,
    CSFBTVAdmin.UserRole ur
    WHERE tu.UserID NOT IN(SELECT UserID FROM CSFBTVAdmin.VideoCrew tc,
    CSFBTVAdmin.Video tv
    WHERE tc.VideoID = tv.VideoID
    AND ((StartDateTime > v_StartDateTime AND StartDateTime < v_EndDateTime)
    OR (EndDateTime > v_StartDateTime AND EndDateTime < v_EndDateTime)
    OR (StartDateTime <= v_StartDateTime AND EndDateTime >= v_EndDateTime)))
    AND tu.UserID = ur.UserID
    AND ((ur.RoleID = 3) OR (ur.RoleID = 4))
    AND ActiveFlag = 'Y'
    UNION
    SELECT DISTINCT tu.UserID,
    tu.UserFirstName,
    tu.UserLastName,
    UnavailableFlag
    FROM CSFBTVAdmin.CSFBTVUser tu,
    CSFBTVAdmin.UserRole ur,
    CSFBTVAdmin.VideoCrew tc,
    CSFBTVAdmin.Video tv
    WHERE tu.UserID = ur.UserID
    AND ((ur.RoleID = 3) OR (ur.RoleID = 4))
    and tu.UserID = tc.UserID
    AND tc.VideoID = tv.VideoID
    AND ((StartDateTime > v_StartDateTime AND StartDateTime < v_EndDateTime)
    OR (EndDateTime > v_StartDateTime AND EndDateTime < v_EndDateTime)
    OR (StartDateTime <= v_StartDateTime AND EndDateTime >= v_EndDateTime))
    AND ActiveFlag = 'Y';

    out_Inventory := cv_Inventory;
    EXCEPTION
    WHEN OTHERS THEN
    err_num := SQLCODE;
    err_msg := SUBSTR(SQLERRM,1,200);
    RAISE_APPLICATION_ERROR(-20005,'Get procedure fail '||err_msg);
    end p_AvailableCrew_Get;

    ------------------------------------------------

    Thanks,
    - Gary

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