本文将详细介绍如何解决ORA-01843与NLS_DATE_FORMAT问题,需要的朋友可以参考下
在Oracle SQL的where子句中传入字符类型参数'19-11月-08',使得可以直接和日期类型比较,或者转换一下同日期类型比较。
如果传入的字符格式和NSL_DATE_FORMAT一致,那么可以不转换直接用,否则会报ORA-01861错误。如果转换得不正确,则可能会报ORA-01843或其他错误。
例如:
Trc代码
SQL>
代码如下:
elect count(*) from dba_objects where created>to_date('2008-12-01');
select count(*) from dba_objects where created>to_date('2008-12-01')
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配
Trc代码
SQL>
代码如下:
select count(*) from dba_objects where created>to_date('19-11月-08','mm-dd-
yyyy');
select count(*) from dba_objects where created>to_date('19-11月-08','mm-dd-yyyy'
)
第 1 行出现错误:
ORA-01843: 无效的月份
不能识别的字符串和格式转换不对的字符串测试时会出现这样的错误。
使用to_date转换的格式字符串主要为'DD-MON-RR'/'DD-MON-RRRR'或'YYYY-MM-DD'/'YY-MM-DD'。
如果能识别,正确的结果应是这样。
SQL>
代码如下:
select count(*) from dba_objects where created>'19-11月-08';
COUNT(*)
----------
4199
这个格式和会话的NLS_DATE_FORMAT参数值相关。
代码如下:
select SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') DL from dual
DF DL
-------------------- --------------------
DD-MON-RR SIMPLIFIED CHINESE
我们在会话级别修改一下这个参数的值。
SQL> alter session set nls_date_format='YYYY-MM-DD';
会话已更改。
SQL>
代码如下:
select count(*) from dba_objects where created>'19-11月-08';
select count(*) from dba_objects where created>'19-11月-08'
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配
这样,原来正确的操作就不对了。使用符合NLS_DATE_FORMAT格式的字符串则可以了。
SQL>
代码如下:
select count(*) from dba_objects where created>'2008-12-01';
这个参数和应用的环境也有关系,有的应用会自动修改这个参数值。因此,测试工作最好放到sqlplus中进行。
数据库级别上这个参数的值是固定不变的,如下所示:
Trc代码
SQL>
代码如下:
select * from v$nls_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ¥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY ¥
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
已选择19行。
NLS_DATE_LANGUAGE是"SIMPLIFIED CHINESE",即简体中文。所以月份值是中文如"11月"。

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

To monitor the health and performance of MySQL servers, you should pay attention to system health, performance metrics and query execution. 1) Monitor system health: Use top, htop or SHOWGLOBALSTATUS commands to view CPU, memory, disk I/O and network activities. 2) Track performance indicators: monitor key indicators such as query number per second, average query time and cache hit rate. 3) Ensure query execution optimization: Enable slow query logs, record and optimize queries whose execution time exceeds the set threshold.

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

Dreamweaver Mac version
Visual web development tools
