Luc
Q1/Q3: If you set the parameter NLS_LENGTH_SEMANTICS=CHAR then any ambiguous *new* column/variable definitions will be made long enough for characters rather than bytes. That seems a lot easier than changing every single table and package definition.
But existing columns won't be affected, as I recall. You would need to MODIFY table/column definitions according (and then recompile affected views, dependent packages etc).
You should beware of the possible implications on index key sizes (as the
VARCHAR2 / NVARCHAR2 declarations in PL/SQL will be fixed as the packages are recompiled (mostly this will happen implicitly because of your DDL on the underlying tables - but you should check that any packages that aren't automatically recompiled are manually recompiled.
Q2: of course Oracle limits SQL types (NOT in PL/SQL) to 4000 bytes - so up to 1000 * 4 byte chars (of course, most of your characters will occupy just 1 or 2 bytes - your exact mileage will vary depending on language). You can use CLOB instead, and many string functions work directly against 'small' CLOBS - but remember that CLOBs also have some drawbacks (LOB space management etc), so only convert if you are sure you have to.
HTH
Regards Nigel
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 10 2007 - 09:26:36 CST
