Resolution of mismatch between Oracle text and format strings

Symptom: c/S program originally run normally, once reload system, reload Oracle client, insert date format data, appear problem error. Solution:

Oracle Time Format Discussions

Many Oracle new scholars on the internet have been annoyed by the time format of Oracle, often after designing a table with time fields, which failed to insert data into the table. Recall that when you first started learning Oracle, also on this time format is not touch the mind, although it is a small problem, the masters are disdain to discuss, but for beginners, this is a modest, not find the way, may be a few days are not, in fact, is a layer of window paper, no difficulty. Here to do a simple summary, because the level is limited, please correct the master.

1 Oracle data storage for TIME formats

The data in the Oracle database is stored in Oracle-specific format, accounting for 7 bytes, regardless of the time format displayed in the query, which byte represents what, I am not sure, please add. The storage time consists of minutes, minutes and seconds, the minimum precision is second, and the time unit is not stored under seconds. This is why you should be aware of this when you are connecting to an Oracle database in some foreground support for millisecond-level programs, such as PB client programs. The time format that is displayed when the query is determined by the session environment, or by user-defined, independent of the database.

2 Oracle Time Display form

Typically, after the client has established a connection to the database, Oracle gives the default time format data to be displayed in relation to the character set being used. Usually displays the month and the year, but does not show time and seconds. For example, when using the Us7ascii character set (or another English character set), the default time format is displayed as: 28-jan-2003, When using the ZHS16GBK character set (or other Chinese character set), the time format defaults to: 2 March-January-28. When you insert data into a table, if you do not use a conversion function, the format of the time field must conform to the time format of the session environment, or it cannot be inserted. To view the time format of the current session, you can use the following SQL statement to:sql> the select Sysdate from dual; (only if you can insert data by the results of this statement)

3 Inserting time-formatted data into an Oracle table

Inserting time Format data into an Oracle table is a relatively cumbersome task, either by writing your time values strictly in the time format of the current session or by using the transform function to customize the format of the time data. There are two time-dependent conversion functions: To_char and To_date. To_char (time value, time format) function converts a time value to a string, usually used in a query; To_date (string, time format) converts a format string to a time value, usually in the insert Statements are used in the. (Oh, the program did not do so, error) in the date format, the various date format components are not discussed in detail here, please check the Oracle Help document the use of these two functions, or check the Pl/sql programming books, said very detailed.

4 How to modify date formats in a session

Modify the date format in the current session there are three ways to do it, I have only done the first two methods of testing, modify the Glogin.sql file method I have not tried, do not know whether the real can do. Here are one by one explanations.

(1) Modify the date format of the current session in Sql*plus sql> alter sessions set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss '; Modify the time format of the current session to this format: 2003-01-28 15:23:38, four-digit year-two-bit-two-day, space, 24-hour system of hours: minutes: seconds.

This modification method is valid only for the current session. Note that the current session is not the current Sql*plus window. That is, if you modify this and use the Connect command to connect to the database with another user or to a different database, the date format becomes invalid and reverts to the default date format.

(2) To modify the registry (Windows system only)

Add a string to the registry HKEY_LOCAL_MACHINESOFTWAREORACLEHOME0 primary key (8i version, 9i also can), String name Nls_date_format, string value for the time format you want to define, such as: Yyyy-mm-dd Hh24:mi:ss, and then restart Sql*plus. This modification is valid for the Sql*plus window, which is the default time format, regardless of how many sql*plus windows you open. Modifying the server-side registry is invalid, and only modifying the client's registry is valid.

Is there a similar approach under UNIX systems, I don't know, I don't use UNIX or Linux systems.

(3) Modify the Glogin.sql file in the $oracle_homesqlplusadmin directory this method I have not tried, do not dare to talk nonsense, lest mislead everyone. Please understand this method of master to teach.

Priority of the format for the period 5th

If in a specific environment, the registry is modified and the current session is modified using the ALTER SESSIONS command, which method of modification is valid? The alter session command is valid and has the highest precedence. That is, regardless of the current client environment, the registry changes to what it is, as long as you use the ALTER session command to modify the time format, then in your modified time format.

Therefore, both methods are available if you use Sql*plus interactively or insert time format data directly. If you are using SQL files for bulk inserts or timed execution, it is a good idea to use the alter session command to modify sessions before using the time data to make sure that the SQL statement is running in a context independent of the environment. Similarly, if you need to use the time format data in the foreground client program, it is best to modify the session before using it, or use the To_char or To_date function for conversion (the conversion function is strongly recommended) to ensure that the program runs in relation to the environment.

Summary:

Data about time types in Oracle databases defines a number of functions, especially the calculation of time data, which is convenient and, of course, convenient if you are familiar with Oracle's time functions. I see some people here sometimes. Use string-type fields when storing data of type time. I strongly disagree with this approach, whether it's from the overhead of the system, from the calculation of the time data, or from one of the sub data in the data, such as the months in the time, the weeks in the time, etc. is not as convenient as storing data directly as a type of time. As long as you're familiar with Oracle's time-related functions, I'm sure you'll like the Oracle time type data, very flexible, powerful, and almost everything you can think about time--it's up to you to master the time functions of Oracle. Above in Windows 2000 + Oracle version 805 test passed, wrong, please correct me.

Trackback:http://tb.blog.csdn.net/trackback.aspx? postid=1497362