It so happens that the product we are developing works with multiple relational databases. Now these are MS SQL, Postgres and Oracle. There were launches under a lot of things from MySQL to the deceased, probably Firebird and exotic Sybase with DB2, but that's not the story.
If with MS SQL and Postgres everything is more and less understandable and familiar, then with Oracle every time we are in for some surprises. An astute reader will immediately notice that "our hands are crooked" and we "simply do not know how to cook it", but if, dear reader wants to know how varchar (or rather varchar2
) in God-like Oracle differs from its brethren, then please under cat.
Like all modern systems, we store data in Unicode format (currently UTF-8). Why might this be important for relational databases?
Well, for example, if you have a mix of unicode and non-unicode data types in your database, then some drivers cannot do this. For example, JTDS - JDBC driver for MS SQL server can work either in Unicode mode or in Ansi. Accordingly, if you decide to "save" and create a non-unicode column (varchar / char), then you will get a unicode-> ansi conversion at the level of data insertion into the table and, most likely, achieve the opposite effect (at least slowdown on data insertion, otherwise and on the search).
So the story. Our application server checks the maximum allowable length of fields before inserting them (here it is necessary to stipulate that the check is performed not according to the database data, but according to our internal metadata), but despite this, sometimes under Oracle we "catch" an error likeORA-12899: value too large for column.
? , , Oracle.
. , varchar2
:)
, ,
alter table address modify street varchar2(150);
150 - ( -)? - :) .
alter table address modify street varchar2(150 char);
.. char
-byte
. ( ) - .
, UTF-8, , 4 ( 1 ANSI, 2 4 ).
Unicode !? , , , " ". .. , : legacy, , Unicode' " ", , backup 86 imp - .
? tool, , create table
char
:)
:
, , , .
SELECT value FROM NLSDATABASEPARAMETERS WHERE parameter='NLSLENGTHSEMANTICS';
, , " ":
SELECT TABLE_NAME, COLUMN_NAME, DATA_LENGTH, CHAR_USED
FROM USER_TAB_COLUMNS
WHERE DATA_TYPE = 'VARCHAR2' AND CHAR_USED = 'B'
ORDER BY TABLE_NAME, COLUMN_NAME
P.S. , , (, 100% ansi ), Unciode … ...
P.P.S. Regexp " " varchar2\(\s*\d+\s*\)
P.P.P.S. StackOverflow
PPPPS Here is what Oracle thinks about changing the parameter value NLSLENGTHSEMANTICS
to something more reasonable "Oracle strongly recommends that you do NOT set the NLS LENGTH SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows. " https://docs.oracle.com/cd/E24693 01 / server.11203 / e24448 / initparams149.htm