Hello,
I've been struggling with "strange" behavior with handling "extreme " timestamp values.
Our applications in .NET execute stored procedures with timestamp parameter and in some cases pass "31/12/9999 23:59:59.9999999" - it is value of Datetime.MaxValue function
After upgrade to 19.25 we get "ORA-01841: (full) year must be between -4713 and +9999, and not be 0" for this value.
Lets look on my examples
Following statement returns same value for oracle 19.25 and < 19.25 versions
select to_timestamp('12/31/9999 23:59:59.9999999','MM/DD/YYYY HH24:MI:SS.FF') from dual;31-DEC-99 11.59.59.999999900 PM
Lets create simple procedure
CREATE OR REPLACE PROCEDURE testmh( p_data in timestamp )
IS
vdata timestamp(9);
BEGIN
dbms_output.put_line('a');
END;
and execute like
execute testmh(to_timestamp('12/31/9999 23:59:59.9999999','MM/DD/YYYY HH24:MI:SS.FF'));on 19.25 I get
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
on < 19.25
PL/SQL procedure successfully completed.
Another test - create the function
CREATE OR REPLACE FUNCTION testmhf (
p_data TIMESTAMP )
RETURN TIMESTAMP
IS
BEGIN
RETURN p_data;
END testmhf;
and execute
select testmhf(to_timestamp('12/31/9999 23:59:59.9999999','MM/DD/YYYY HH24:MI:SS.FF')) from dual;for <19.25 result is
01-JAN-00 12.00.00.000000000 AM
for 19.25 result is
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Workaround for this problem is create the subtype and use that as type for procedure parameter
CREATE OR REPLACE PACKAGE XY
AS
SUBTYPE timestamp_t IS TIMESTAMP(7);
END;
/
CREATE OR REPLACE PROCEDURE testmh_t( p_data in XY.timestamp_t )
IS
BEGIN
insert into zone(zone) values(p_data);
END;
/
execute testmh_t(to_timestamp('12/31/9999 23:59:59.9999999','MM/DD/YYYY HH24:MI:SS.FF'));
Do you know any other workaround or why passing this "extreme" value is causing that this value is rounding up?
M.
I'm not sure how this worked on earlier patches of 19c. The behaviour (albeit with a different error message) was present in 12.2:
select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c EE High Perf Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
CREATE OR REPLACE PROCEDURE testmh( p_data in timestamp )
IS
BEGIN
dbms_output.put_line('a' || p_data );
END;
/
execute testmh(to_timestamp('12/31/9998 23:59:59.9999999','MM/DD/YYYY HH24:MI:SS.FF'));
a01/01/9999 00:00:00.000000
execute testmh(to_timestamp('12/31/9999 23:59:59.9999999','MM/DD/YYYY HH24:MI:SS.FF'));
ORA-01877: string is too long for internal bufferI see similar on 21c and 23ai too. This is because the parameter p_data is a timestamp(6)
select argument_name, data_type, data_precision
from user_arguments
where object_name = 'TESTMH';
ARGUMENT_NAME DATA_TYPE DATA_PRECISION
P_DATA TIMESTAMP 6
Your timestamp has seven fractional digits, so gets rounded up when converted to six.
select cast (
to_timestamp('12/31/9998 23:59:59.9999999','MM/DD/YYYY HH24:MI:SS.FF')
as timestamp(6)
) from dual;
CAST(TO_TIMESTAMP('12/31/999823:59:59.9999999','MM/DD/YYYYHH24:MI:SS.FF')ASTIMESTAMP(6))
01/01/9999 00:00:00.000000000 To overcome this you need to make the argument a timestamp(7) or more. A subtype is one way, %type anchoring it to a column with enough precision is another:
create table t ( c1 timestamp(7) );
CREATE OR REPLACE PROCEDURE testmh( p_data in t.c1%type )
IS
BEGIN
dbms_output.put_line('a' || p_data );
END;
/
execute testmh(to_timestamp('12/31/9998 23:59:59.9999999','MM/DD/YYYY HH24:MI:SS.FF'));
a31/12/9998 23:59:59.9999999
execute testmh(to_timestamp('12/31/9999 23:59:59.9999999','MM/DD/YYYY HH24:MI:SS.FF'));
a31/12/9999 23:59:59.9999999