·  PLSQL – Syntax – Variables – VARCHAR2

 

- Info:

  - VARCHAR2 is used to store variable length strings.

  - VARCHAR2 variable can hold from 1 to 32767 bytes.

  - VARCHAR2 database column can hold from 1 to 4000 bytes.

  - Length can be defined either in BYTEs or CHARacters.

  - For VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value.

    For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable.

  - When you specify the length in characters, the upper limit is still 32767 bytes.

    So for double and multy byte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character sets.

  - When you do not use the CHAR or BYTE qualifiers, the default is determined by the setting of the

    NLS_LENGTH_SEMANTICS initialization parameter.

  - When a PL/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when

    the procedure is recompiled after being invalidated.

  - You cannot insert VARCHAR2 values longer than 4000 bytes into a VARCHAR2 database column.

  - You can insert any VARCHAR2 value into a LONG database column because the maximum width of a LONG database column is 2**31 bytes.

    However, you cannot retrieve a value longer than 32767 bytes from a LONG column into a VARCHAR2 variable.

 

- Procedure:

  - Create "PL/SQL Application" Test.sql.

 

    Test.sql      

    DECLARE

      text1 VARCHAR2(10 BYTE); -- It can hold up to 10 bytes.

      text2 VARCHAR2(10 CHAR); -- It can hold up to 10 characters.

      text3 VARCHAR2(10);      -- NLS_LENGTH_SEMANTICS defines if default is BYTE or CHAR.

        

    BEGIN 

      text1 := 'Hello ';  

      text2 := 'World ';  

      text3 := 'Greetings ';

    

      DBMS_OUTPUT.PUT_LINE(text1 || text2 || text3);

          

    END;