Best Of
Re: ORA-01801: date format is too long for internal buffer
Yes, date Typ=12 Len=7: 119,152,121,0,54,49,54 is corrupted. Type 12 date internal format is:
Byte 1: Century (stored in excess-100 notation, meaning 100 is added to the actual century value).
Byte 2: Year-of-century (also stored in excess-100 notation).
Byte 3: Month.
Byte 4: Day.
Byte 5: Hour (stored in excess-1 notation, meaning 1 is added to the actual hour value, so 1 AM is stored as 2).
Byte 6: Minute (stored in excess-1 notation).
Byte 7: Second (stored in excess-1 notation).
So we have:
SQL> select (119 - 100) * 100 + (152 - 100) yyyy,
2 121 mm,
3 0 dd,
4 54 - 1 hh24,
5 49 - 1 mi,
6 54 - 1 ss
7 from dual
8 /
YYYY MM DD HH24 MI SS
---------- ---------- ---------- ---------- ---------- ----------
1952 121 0 53 48 53
SQL>
As you can see both month and day are bad.
The other date Typ=12 Len=7: 120,102,6,10,1,1,1 is good:
SQL> select (120 - 100) * 100 + (102 - 100) yyyy,
2 6 mm,
3 10 dd,
4 1 - 1 hh24,
5 1 - 1 mi,
6 1 - 1 ss
7 from dual
8 /
YYYY MM DD HH24 MI SS
---------- ---------- ---------- ---------- ---------- ----------
2002 6 10 0 0 0
SQL>
What tool is used to populate this table?
SY.
Re: ORA-01801: date format is too long for internal buffer
If the code that is causing this error is this call to update a date column by using the trim function on the date column, the simple and clear answer is to eliminate this update as completely unneeded.
The TRIM function should not be used on columns that are date datatype. This function is only appropriate for string or character data. Dates are not stored as strings, they are stored in a numeric date format.
Dates are not strings, they cannot have leading or trailing spaces that should be trimmed.
Using TRIM on a date value does not do anything except use a series of implicit conversions to make the date value into a string appropriate as input to the trim function and then make the string output of the trim function into a date so that the column can be updated.
The input to trim is a string… if you pass a date to it there will be an implicit conversion to a string.
Lets look at an example:
select trim(date_column) from my_table
Since a date is not a string, the date value has to be implicitly converted to a string for trim to use it as an input.
Explicitly showing this conversion, the statement is actually:
select trim(to_char(date_column)) from my_table
The output from the trim function is a string. If you are trying to update a date column with the output of the trim function there will be an implicit conversion to a date.
update my_table set date_column = trim(date_column)
is actually:
update my_table set date_column = to_date(trim(to_char(date_column)))
since each of these conversions is implicit, the format model is not specified and the nls_date_format for the session will be used.
if your session nls_date_format is 'yyyy-mm-dd hh:mi:ss pm', then this seemingly simple statement is actually:
update my_table set date_column = to_date(trim(to_char(date_column, 'yyyy-mm-dd hh:mi:ss pm')),'yyyy-mm-dd hh:mi:ss pm')
Check your session nls_date_format to see if it is some form of nonsense:
select value
from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT'
…the ORA-01847 error you are getting about the day of the month being in the wrong range would only happen in cases where the value that should be the day of the month is inappropriate…like this:
select to_date('2025-02-99', 'yyyy-mm-dd')
from dual
to_date will look at the 99 and try to make it a day of the month and you will get the ORA-01847 error….
In your case, the exact same format will be used to do the inner implicit conversion with to_char as the outer explicit conversion with to_date… so the net effect of calling trim on a date column should always be nothing.
With regards to the other error you are seeing: ORA-01801: date format is too long for internal buffer, this is a case where the date format itself is just too large… you can add extra text to the date format model if you wish… just not too much extra text:
This will work:
select
to_char(
sysdate,
'yyyy/mm/dd "frogs and cats" hh:mi' ) as strange_date
from dual
—2025/09/06 frogs and cats 10:58
select
to_char(
sysdate,
'mm/dd "frogs and cats dancing in the rain with all manner of kitchen appliances" hh:mi' ) as strange_date
from dual
—ORA-01801: date format is too long for internal buffer
Remove the unneeded trim call on a column that is already a date… and check your session nls_date_format for nonsense.
Regards
Anthony Harper
Re: how can i install oracle client 19 on RHEL 9?
Below are the basic steps for performing an install of Oracle Client 19c Gold Image (64-bit) on OL8 / RHEL9, complete with the latest quarterly patches (JUL2025 "19.28.0.0.0" at the time of this writing):
=====================
= Performed as root user =
=====================
1 - Install Prerequisite RPMs:
Oracle Linux 9 <<>> # dnf install oracle-database-preinstall-19c libnsl2 libnsl2-devel
Red Hat Enterprise Linux 9 <<>> # dnf install bc binutils compat-openssl11 elfutils-libelf fontconfig glibc glibc-devel ksh libaio libasan liblsan libX11 libXau libXi libXrender libXtst libxcrypt-compat libgcc libibverbs libnsl libnsl2 libnsl2-devel librdmacm libstdc++ libxcb libvirt-libs make policycoreutils policycoreutils-python-utils smartmontools sysstat
Alternatively, the RPM can be installed with older YUM utility if not comfortable with the new DNF utility for RPM installations:
Oracle Linux 9 <<>> # yum install oracle-database-preinstall-19c libnsl2 libnsl2-devel
Red Hat Enterprise Linux 9 <<>> # yum install bc binutils compat-openssl11 elfutils-libelf fontconfig glibc glibc-devel ksh libaio libasan liblsan libX11 libXau libXi libXrender libXtst libxcrypt-compat libgcc libibverbs libnsl libnsl2 libnsl2-devel librdmacm libstdc++ libxcb libvirt-libs make policycoreutils policycoreutils-python-utils smartmontools sysstat
2 - Ensure "/dev/shm" & "/tmp" are mounted without "noexec":
Check if "/dev/shm" & "/tmp" are mounted with "noexec":
mount | grep tmp | grep noexec
If need to remount without "noexec":
mount -o remount,exec /dev/shm
mount -o remount,exec /tmp
======================
= Performed as oracle user =
======================
1 - Download & Extract Oracle 19c Client Gold Image (64-bit) into the directory you want as the Oracle Home:
Oracle Database 19c Download for Linux x86-64
https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html
LINUX.X64_193000_client_home.zip
$ cd $ORACLE_HOME
$ unzip <parent dir>/LINUX.X64_193000_client_home.zip -x OPatch*
2 - Update the OPatch Utility in the extracted Oracle Home:
Patch 6880880 version OPatch 19.0.0.0.0
https://updates.oracle.com/download/6880880.html
p6880880_190000_Linux-x86-64.zip
$ cd $ORACLE_HOME
$ rm -rf OPatch
$ unzip <parent dir>/p6880880_190000_Linux-x86-64.zip
3 - Download & Extract the DBRU:
Patch 37960098: DATABASE RELEASE UPDATE 19.28.0.0.0
https://updates.oracle.com/download/37960098.html
p37960098_190000_Linux-x86-64.zip
4 - Execute Oracle 19c Client (64-bit) Installer:
$ cd $ORACLE_HOME
$ export CV_ASSUME_DISTID=RHEL7
$ ./runInstaller -applyRU <extracted patch 37960098>
--
Requirements for Installing Oracle Database/Client 19c (19.22 or higher) on OL9 or RHEL9 64-bit (x86-64) (Doc ID 2982833.1)
Re: how can i install oracle client 19 on RHEL 9?
Below are the basic steps for performing an install of Oracle Client 19c Ship Home (64-bit) on OL8 / RHEL9, complete with the latest quarterly patches (JUL2025 "19.28.0.0.0" at the time of this writing):
=====================
= Performed as root user =
=====================
1 - Install Prerequisite RPMs:
Oracle Linux 9 <<>> # dnf install oracle-database-preinstall-19c libnsl2 libnsl2-devel
Red Hat Enterprise Linux 9 <<>> # dnf install bc binutils compat-openssl11 elfutils-libelf fontconfig glibc glibc-devel ksh libaio libasan liblsan libX11 libXau libXi libXrender libXtst libxcrypt-compat libgcc libibverbs libnsl libnsl2 libnsl2-devel librdmacm libstdc++ libxcb libvirt-libs make policycoreutils policycoreutils-python-utils smartmontools sysstat
Alternatively, the RPM can be installed with older YUM utility if not comfortable with the new DNF utility for RPM installations:
Oracle Linux 9 <<>> # yum install oracle-database-preinstall-19c libnsl2 libnsl2-devel
Red Hat Enterprise Linux 9 <<>> # yum install bc binutils compat-openssl11 elfutils-libelf fontconfig glibc glibc-devel ksh libaio libasan liblsan libX11 libXau libXi libXrender libXtst libxcrypt-compat libgcc libibverbs libnsl libnsl2 libnsl2-devel librdmacm libstdc++ libxcb libvirt-libs make policycoreutils policycoreutils-python-utils smartmontools sysstat
2 - Ensure "/dev/shm" & "/tmp" are mounted without "noexec":
Check if "/dev/shm" & "/tmp" are mounted with "noexec":
mount | grep tmp | grep noexec
If need to remount without "noexec":
mount -o remount,exec /dev/shm
mount -o remount,exec /tmp
======================
= Performed as oracle user =
======================
1 - Download & Extract Oracle 19c Client Shiphome (64-bit) into a Staging Area:
Patch 36347606: TRACKING BUG FOR UPLOAD OF 19.22 CLIENT FOR SILENT INSTALL ON OL9
https://updates.oracle.com/download/36347606.html
p36347606_1922000_Linux-x86-64.zip
$ cd <Staging Area>
$ unzip <parent dir>/p36347606_1922000_Linux-x86-64.zip
$ unzip client.zip
2 - Execute Oracle 19c Client (64-bit) Installer:
$ cd <Staging Area>/client
$ ./runInstaller
3 - Update the OPatch Utility in the Oracle Home:
Patch 6880880 version OPatch 19.0.0.0.0
https://updates.oracle.com/download/6880880.html
p6880880_190000_Linux-x86-64.zip
$ cd $ORACLE_HOME
$ rm -rf OPatch
$ unzip <parent dir>/p6880880_190000_Linux-x86-64.zip
4 - Download & apply the DBRU:
Patch 37960098: DATABASE RELEASE UPDATE 19.28.0.0.0
https://updates.oracle.com/download/37960098.html
p37960098_190000_Linux-x86-64.zip
$ cd <extracted patch 37960098>
$ORACLE_HOME/OPatch/opatch apply
--
Requirements for Installing Oracle Database/Client 19c (19.22 or higher) on OL9 or RHEL9 64-bit (x86-64) (Doc ID 2982833.1)
Re: how can i install oracle client 19 on RHEL 9?
@User_JDD7S "LINUX.X64_193000_client_home.zip" is the Gold Image installer. This is the same installation method as installing Oracle Database 19c.
"LINUX.X64_193000_client.zip" is the Ship Home installer. This is the old traditional installer, where you extract the installer to a staging area, and you select the installation type and installation location during the install.
Re: ORA-01843 "Not a Valid Month" Creating a table in Oracle 19
SQL> CREATE TABLE MMT_EQUIVALENCY_RULE
2 (
3 EQUIVALENCY_RULE_ID NUMBER(18,0) NOT NULL,
4 UPDATE_DT TIMESTAMP(6) DEFAULT '1970-01-01 00:00:00.000000' NULL
5 )
6 /
UPDATE_DT TIMESTAMP(6) DEFAULT '1970-01-01 00:00:00.000000' NULL
*
ERROR at line 4:
ORA-01843: not a valid month
SQL> CREATE TABLE MMT_EQUIVALENCY_RULE
2 (
3 EQUIVALENCY_RULE_ID NUMBER(18,0) NOT NULL,
4 UPDATE_DT TIMESTAMP(6) DEFAULT TIMESTAMP '1970-01-01 00:00:00.000000' NULL
5 )
6 /
Table created.
SQL>
SY.
Re: Warning: long redo log write elapsed times detected, the LG* process tracefiles have more details
It is a bug. For more info, refer to the link below:
https://community.oracle.com/mosc/discussion/4590293/since-ru-19-28-cdump-twice-a-day-without-incident
How to highlight a field text in RTF template for BI Publisher report?
Hello Team,
We have a requirement to highlight middle of the Field A value (10 digits) if the Field B is Yes.
Example Field A value is "ABFDFD1212121SD"
and Field B is "Yes"/ "No"
If the Field B is Yes then highlight the Field A = only the middle 10 digits - "AB FDFD121212 1SD"
else
no need to highlight and it should display as in XML.
Thanks,
Prem
Re: ODA X11 new deployment
Hi Kriz,
Thanks for the feedback.
The fail over has been enabled by default.
Thanks so much for the support.
Thanks,
James.
Re: USERMAINT component
Hi User_D1N90,
Once you drag and drop the field, it will typically be placed based on the order dictated by the system. Once you're done dragging/dropping the field(s), save the definition, close and re-open it. It will appear in the order you can expect it to appear. In my case I see it as the 4th L0 property.
Thanks,
Mike