Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem with Trunc(Dates)

637614Jul 29 2009 — edited Jul 29 2009
Hey guys,
I need your help with a trunc statement.

I have a table SHTTRAN with 3 columns:

SHTTRAN_ID SHTTRAN_SEQ_NO SHTTRAN_REQUEST_DATE
901325 2 7/20/2008 6:11:28 PM
900678 4 7/20/2008

The request date column has few records with timestamp and few without timestamp as shown above.
I have a procedure within a package that deletes one record from this table as follows:

exec deletepackage.p_delete_szptpsr('901325', 2, '20-JUL-2008');
exec deletepackage.p_delete_szptpsr('900678', 4, '20-JUL-2008');

I have a truc(shttran_request_date) inside the procedure to remove the timestamp as shown below:

CREATE OR REPLACE PACKAGE BODY "DELETEPACKAGE" IS
PROCEDURE p_delete_szptpsr (p_shttran_id IN saturn.shttran.shttran_id%TYPE,
p_shttran_seq_no IN saturn.shttran.shttran_seq_no%TYPE,
p_shttran_request_date IN saturn.shttran.shttran_request_date%TYPE) IS

BEGIN

DELETE FROM saturn.shttran
WHERE shttran.shttran_id = p_shttran_id
and shttran.shttran_seq_no = p_shttran_seq_no
and trunc(shttran_request_date) = p_shttran_request_date;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Deleted the Request No (' || p_shttran_seq_no || ') of the Student (' || p_shttran_id ||') for the requested date of (' || p_shttran_request_date ||')');

END p_delete_procedure;
END DELETEPACKAGE;
/

Though I execute the procedure it is not deleting the record with the timestamp. Can someone help me with this?

Thank you so much in advance.
This post has been answered by Frank Kulash on Jul 29 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 26 2009
Added on Jul 29 2009
10 comments
761 views