Re: Update nls_length_semantics!

"Hans" <sorry_at_nospam.com> a écrit dans le message de news:cok353$71t$1_at_green.tninet.se...
> Hi Michel!
>
> Thanks for your help. Ok I see you can change the fieldsize but I still
> think we will end up with problems if we don't change the actual
> nls_length_semantics because we have like 1000000 lines of pl/sql code which
> must be updated since variables may be too small (are still in bytes instead
> of characters).
>
> I found this article
> http://www.oracle.com/technology/tech/globalization/pdf/mwp.pdf
>
> and on page 9 line 8 it states
> "Like with column length adjustments, if the original copy of a database
> migrated through Export and Import utilities is not used after the
> migration, altering the length semantics can be done before the export step
> (but after taking a backup). This allows the Import utility to automatically
> create tables with the correct column lengths and load data without
> truncation errors in a single run. Otherwise, tables have to be pre-created
> in the new database."
>
> If I understand this right it should be possible to take down the database
> and set the nls_length_semantics to char. Start the instance and export the
> data and then import the tables again (removing the old ones). If anyone has
> done this please give me your experiences (did it work or not, any extra
> steps we need to do etc). If it is possible I don't want to re-create the
> tables before I import data because there is always a risk that I forget
> some customized triggers, index etc that are not part of our base script.
>
> Regards
> /Hans
>
>

What i showed is you can change each field length semantics in your tables dynamically. You don't have to export/shutdown/change nls_length_semantics/restart/import/... (well, you can change nls_length_semantics for future tables, it does not hurt.) Then, if your PL/SQL variables are declared with %TYPE and not with predefined length  they'll get the correct length at the next execution as Oracle recompiles the PL/SQL dynamically at the first execution after the alter table. If this not the case (predifined length variables), you have to modify the PL/SQL code as, afaik, PL/SQL variable length is not related to nls_length_semantics.

Regards
Michel Cadot Received on Wed Dec 01 2004 - 03:50:36 CST