Gunther Oracle APEX und Datenbank Wiki

angelegt 09.2013 überarbeitet 01.2024

Über den Parameter „NLS_LENGTH_SEMANTICS“ wird das Verhalten der Datenbank beim Anlegen von VARCHAR2 Spalten definiert.

Der Default Wert ist „BYTE“ , das heißt beim Anlegen einer VARCHAR2 Spalte wird für die Längen Angabe der Spalte die Anzahl der Bytes verwendet.

Das heißt aus der DDL Angabe „varchar2(10)“ wird eine Spalte mit dem Datentyp VARCHAR2 und der Byte Längen Angabe („data_length“ in der dba_tab_columns ) von 10 erstellt.

Abfragen der erzeugten Längen nach Anlage der Tabelle mit:

 SELECT  col.TABLE_NAME
       , col.COLUMN_NAME
       , col.DATA_TYPE
       , col.CHAR_COL_DECL_LENGTH  -- Declaration length of the character type column
       , col.DATA_LENGTH           -- Length of the column (in bytes)
       , col.CHAR_LENGTH           -- Displays the length of the column in characters.
       , col.CHAR_USED             -- Indicates that the column uses BYTE length semantics (B) or CHAR length semantics (C),
  FROM user_tab_columns  col
     --show only tables no views!
   INNER JOIN user_tables tab
   ON (tab.TABLE_NAME = col.TABLE_NAME)
 WHERE col.data_type='VARCHAR2'

Der sqlplus „describe“ Befehl zeigt als Zusatz zum Datentyp nichts, damit ist diese Spalte mit Byte definiert.

Steht der Wert auf „CHAR“, wird die Varchar2 Spalte mit der Länge angelegt, die notwendig ist die Anzahl von Zeichen beim eingestellten Zeichensatz der DB zu speichern.

Das heißt aus der DDL Angabe „varchar2(10)“ wird eine Spalte mit dem Datentyp VARCHAR2 und der Byte Längen Angabe („data_length“ in der dba_tab_columns ) von 40 erstellt bei UTF16.

Der sqlplus „describe“ Befehl zeigt als Zusatz zum Datentyp nichts an, damit ist diese Spalte mit Byte definiert.

NLS_LENGTH_SEMANTICS kann global oder auf Session ebene gesetzt werden.

Einschränkung:

• Im SYS Schema wird die Einstellung NLS_LENGTH_SEMANTICS beim Anlegen einer Tabelle ignoriert
• Nicht dauerhaft verwenden! Besonders beim Einspielen von Oracle Optionen und Upgrade kann es zu ungewünschten Verhalten kommen.

Setzen auf CHAR auf Session Ebene:

SQL>ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
 
SQL>CREATE TABLE gt ( a varchar2(10));
 
SQL>DESC gt
 
 Name      NULL?    TYPE             
 --------  -------- -----------------
 A                  VARCHAR2(10)
 
SQL>SELECT data_type,data_length FROM user_tab_columns WHERE TABLE_NAME ='GT';
 
 
DATA_TYPE    DATA_LENGTH
-----------  -----------
VARCHAR2     40

Setzen auf BYTE auf Session Ebene:

SQL>ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
 
SQL>CREATE TABLE gt ( a varchar2(10));
 
SQL>DESC gt
 
 Name      NULL?    TYPE             
 --------  -------- -----------------
 A                  VARCHAR2(10)
 
SQL>SELECT data_type,data_length FROM user_tab_columns WHERE TABLE_NAME ='GT';
 
 
DATA_TYPE    DATA_LENGTH
-----------  -----------
VARCHAR2     10

Beim Import eines Datenbank Schemas aus einer WE8ISO Datenbank in die neue Unicode Umgebung wird folgender Fehler geworfen:

ORA-02374: conversion error loading table "GPITEST"."ILOCDB02"
ORA-12899: value too large for column STICHWORT (actual: 14, maximum: 12)
ORA-02372: data for row: STICHWORT : 'Lösungsworte'

Lösung: Schema wieder löschen, setzen von NLS_LENGTH_SEMANTICS auf CHAR, neu importieren

SET serveroutput ON
 
 
DECLARE
 cursor c_char_cols
 IS
 SELECT col.TABLE_NAME
       ,col.COLUMN_NAME
       ,col.DATA_TYPE
       , col.CHAR_COL_DECL_LENGTH  -- Declaration length of the character type column
       , col.DATA_LENGTH           -- Length of the column (in bytes)
       , col.CHAR_LENGTH           -- Displays the length of the column in characters.
       , col.CHAR_USED             -- Indicates that the column uses BYTE length semantics (B) or CHAR length semantics (C),
  FROM user_tab_columns  col
     --show only tables no views!
   INNER JOIN user_tables tab
   ON (tab.TABLE_NAME = col.TABLE_NAME)
 WHERE col.data_type='VARCHAR2' 
   AND col.CHAR_USED!='C' -- DATA_LENGTH can be usesd as if char_used=b then the orignal lenght is in DATA_LENGTH 
                          -- if  char_used=c then the DATA_LENGTH  can be longer (twice) then CHAR_LENGTH!
   AND col.TABLE_NAME NOT LIKE 'MV%'       -- materialized views
   AND col.TABLE_NAME NOT LIKE 'DR$IDX%'   -- Oracle text - search indexes
   AND col.COLUMN_NAME NOT LIKE 'ORA_ERR%'     -- not change  Constraint Error Tables 
  ;
 
 v_sql_template varchar2(2000):='alter table #TABLE_NAME# modify ( #COLUMN_NAME# varchar2( #CHAR_COL_DECL_LENGTH# CHAR))';  
 v_sql varchar2(2000);
 
BEGIN
 
 FOR rec IN c_char_cols
 loop 
   v_sql:=v_sql_template; 
   v_sql:=REPLACE(v_sql,'#TABLE_NAME#'  ,rec.TABLE_NAME);
   v_sql:=REPLACE(v_sql,'#COLUMN_NAME#' ,rec.COLUMN_NAME);
   v_sql:=REPLACE(v_sql,'#CHAR_COL_DECL_LENGTH#' ,rec.CHAR_COL_DECL_LENGTH);
   --dbms_output.put_line('-- try to execute command '||v_sql);
   BEGIN 
   -- not change pur flag columns
   IF rec.CHAR_COL_DECL_LENGTH > 1 THEN
    -- uncomment to execute
    -- EXECUTE IMMEDIATE v_sql;
    -- show only generated SQL for Git checkin
    dbms_output.put_line(v_sql);
    NULL;
   END IF; 
   exception 
    WHEN others THEN
      dbms_output.put_line('-- Error to execute command '||v_sql||' : '||SQLERRM );
   END;
 END loop;
 
END;
/
/

Doku:

• https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_TAB_COLUMNS.html
• https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/NLS_LENGTH_SEMANTICS.html