Oracle Database - case sensitive Columns/Components in PL/SQL Collections, Records and Objects

Introduction

Regarding table and column names, Oracle is by default case insensitive. This means the table column can be named address or ADDRESS or AdDrEsS... it does not matter - the database converts all those lower/mixed case combinations into the upper case.

An example:

CREATE TABLE case_sensitive_test (
    id number primary key not null,
    first_name varchar2(100),
    Last_name varchar2(100),
    AdDrEsS varchar2(1000)
);

The table definition shows that that last name and address columns are in the upper case regardless of our DDL script case.

SELECT statements are also by default case insensitive. All names are converted into an uppercase. For example:

On the other hand, if names are quoted with double quotes they are treated as case sensitive, for example:

CREATE TABLE case_sensitive_test (
    id number primary key not null,
    first_name varchar2(100),
    "Last name" varchar2(100),
    "AdDrEsS" varchar2(1000)
);

Also in this scenario a space character can be used... which is not the case without double quotes.

Later on, double quotes must be used in SELECT or DML statements... otherwise an "Invalid identifier" exception occurs because without double quotes oracle converts names in the upper case.

PL/SQL Collections

If a collection in PL/SQL is based on a table, view, select statement, record... which is using case sensitive columns/components, double quotes must be also used when addressing such components later on in the code.

It looks a little bit silly to use double quotes right after the dot... but... the working example shows it all:

DECLARE
    CURSOR c_data IS
        SELECT
            id,
            first_NAME,
            "Last name",
            "AdDrEsS"
        FROM
            case_sensitive_test
    ;

    TYPE t_data IS TABLE OF c_data%ROWTYPE;
    l_data t_data;

BEGIN
    OPEN c_data;
    FETCH c_data BULK COLLECT INTO l_data;
    CLOSE c_data;

    dbms_output.put_line( l_data(1).first_name );
    dbms_output.put_line( l_data(1).FIRST_NAME );
    dbms_output.put_line( l_data(1)."Last name" );
    dbms_output.put_line( l_data(1)."AdDrEsS" );
END;

Otherwise, without double quotes a "component must be declared" exception occurs, because the database is treating the component name it in the uppercase:

PL/SQL Records

Same story. If the component in the record is defined with double quotes (case sensitive) it must be addressed in the same way later on in the code. Otherwise an exception occurs.

An example:

DECLARE

    TYPE r_data IS RECORD (
        id number,
        first_name varchar2(100),
        "Last name" varchar2(100),
        "AdDrEsS" varchar2(1000)
    );

    l_data r_data;

BEGIN
    dbms_output.put_line( l_data.first_name );
    dbms_output.put_line( l_data.FIRST_NAME );
    dbms_output.put_line( l_data."Last name" );
    dbms_output.put_line( l_data."AdDrEsS" );
END;


Object types and PL/SQL

Same story. If the component in the object type is defined with double quotes (case sensitive) and the variable/collection is based on this object type it must be addressed in the same way later on in the code. Otherwise an exception occurs.

An example:

CREATE OR REPLACE TYPE t_case_sensitive_test AS OBJECT (
    id number,
    first_name varchar2(100),
    "Last name" varchar2(100),
    "AdDrEsS" varchar2(1000)
);


DECLARE

    l_data t_case_sensitive_test;

BEGIN
    dbms_output.put_line( l_data.first_name );
    dbms_output.put_line( l_data.FIRST_NAME );
    dbms_output.put_line( l_data."Last name" );
    dbms_output.put_line( l_data."AdDrEsS" );
END;