| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle implicit data conversion
I usually create a package that all the triggers call to add rows to the
audit table. In the interface to the package I have one set of calls
that takes a date as a parameter. In that interface I explicitly
convert the date to a character. Eg
Audit_pkg.Audi_info(v_table_name in varchar2, v_field_name in varchar2, v_operation in varchar2, old_value in varchar2,new_value in varchar2);
Audit_pkg.Audi_info(v_table_name in varchar2, v_field_name in varchar2, v_operation in varchar2, old_value in date,new_value in date);
Audit_pkg.Audi_info(v_table_name in varchar2, v_field_name in varchar2, v_operation in varchar2, old_value in number,new_value in number);
Audit_pkg.Audi_info(v_table_name in varchar2, v_field_name in varchar2, v_operation in varchar2, old_value in timestamp,new_value in timestamp);
I have the Audit_pkg.Audi_info with date, number, and timestamp argument types do the conversion and then turn around and call Audit_pkg.Audi_info with varchar2 argument types.
Jim
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Boyd
Sent: Tuesday, November 01, 2005 1:29 PM
To: Oracle-L_at_freelists.org
Subject: Oracle implicit data conversion
Hi All,
I have an audit trigger on a table, which an update on the table will trigger inserting the field_name, old_value and new_value into the audit table. The type of one of the fields in audited table is date and the type of old_value and new_value is varchar2(4000). When I update the date field, Oracle does implicit data conversion to insert the date value to old_value and new_value fields in the audit table. The formate of date field is 'MM/DD/YYYY HH:MI:SS AM' (all nsl parameters are set to U.S. default). The format of old_value and new_value was 'DD/MM/YYYY HH:MI:SS AM'. However the format of old_value and new_value becomes to '01-NOV-05' today suddenly. It seems no body made any changes into the database. Does any body have an idea why? I know the best practice is to use explicit conversion. But in the mean time, is there any thing I can set for Oracle implicit data conversion?
Thanks for any input in advance.
Dave
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 01 2005 - 15:38:11 CST
 |
 |