Luke,
I forgot to ask you.
So, you have change every vachar2 for CHAR. And after have you
changed the database to use CHAR (NLS_LENGTH_SEMANTICS = CHAR) ?
Thanks
Luc
On 1/11/07, *Luke Davies* <luke.davies@xxxxxxxxxxx
<mailto:luke.davies@xxxxxxxxxxx>> wrote:
Luc
We did this same upgrade some time ago and what we did was alter
all the varchar2 fields from BYTE to CHAR which then did not
involve an increase in the size field (although, of course, may
actually increase the storage of the column).
After running csscan to identify truncated or lossy columns - we
then exported those tables and then after changing the character
set and modifying the columns, deleting the data (truncating)
(which involved turning off foreign keys) and then importing the
data back in.
All went quite smoothly in the end after a few practice runs!
HTH
Luke
Luc Demanche wrote:
Nigel,
If I understand correctly, I only have to change the existing
column size ? Should I multiple by 4 the size of my varchar2 ?
Even if the column is not mentionned in the report of csscan ?
After that, I recompile every storedprocs, views, etc manually.
Then, I set the NLS_LENGTH_SEMANTICS=CHAR, so I don't have to
worry about the size of my varchar2 anymore ?
Thank you
Luc
On 1/10/07, *Nigel Thomas* <nigel_cl_thomas@xxxxxxxxx
<mailto:nigel_cl_thomas@xxxxxxxxx>> wrote:
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
--
Luc Demanche
Oracle DBA
(514) 867-9977
The contents of this message and any attachments are confidential
and are
intended for the use of the persons to whom it is addressed.
If you are not the intended recipient, you should not copy,
forward, use or
alter the message in any way, nor disclose its contents to any
other person.
Please notify the sender immediately and delete the e-mail from
your system.
The sender is not responsible for any alterations that may have
occurred without
authorisation. Any files attached to this email will have been
checked by us
with virus detection software before transmission.
You should carry out your own virus checks before opening any
attachments, as we
do not accept any liability for loss or damage which may be caused
by viruses.
--
Luc Demanche
Oracle DBA
(514) 867-9977
