Oracle 12.2 COLLATE BINARY_CI

1st db went online a week ago

sofar so good

Issues

  • PL/SQL  compile

    0/0 PL/SQL: Compilation unit analysis terminated
    0/0 PLS-00761: Programmeinheitsortierung darf nur USING_NLS_COMP sein
    SQL> alter session set default_collation=USING_NLS_COMP;

  •  Triggers

An error in trigger PLS-00049: bad bind variable after converting tables to COLLATE BINARY_CI; SR will be created

CREATE TABLE “CI”.”TESTTAB”
( “ID” VARCHAR2(10) COLLATE BINARY_CI,
“TEXT” VARCHAR2(10) COLLATE BINARY_CI,
CONSTRAINT “TESTTAB_PK” PRIMARY KEY (“ID”)
USING INDEX (CREATE INDEX “CI”.”ID_IND” ON “CI”.”TESTTAB” (“ID”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE “USERS” ) ENABLE
) DEFAULT COLLATION BINARY_CI SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE “USERS” ;

SQL> CREATE OR REPLACE TRIGGER CI.testtab_trg
2 BEFORE INSERT OR UPDATE
3 ON CI.TESTTAB
4 REFERENCING NEW AS New OLD AS Old
5 FOR EACH ROW
6 BEGIN
7
8 If :new.text is null
9 then
10 :new.text := ‘ ‘;
11 end if;
12
13
14 EXCEPTION
15 WHEN OTHERS THEN
16 — Consider logging the error and then re-raise
17 RAISE;
18 END testtab_trg;
19 /

Warnung: Trigger wurde mit Kompilierungsfehlern erstellt.

SQL> show error
Fehler bei TRIGGER CI.TESTTAB_TRG:

LINE/COL ERROR
——– —————————————————————-
3/4 PLS-00049: Ung³ltige Bindevariable ‘NEW.TEXT’
5/4 PLS-00049: Ung³ltige Bindevariable ‘NEW.TEXT’

same code is valid for

CREATE TABLE “CS”.”TESTTAB”
( “ID” VARCHAR2(10),
“TEXT” VARCHAR2(10),
CONSTRAINT “TESTTAB” PRIMARY KEY (“ID”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE “USERS” ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE “USERS” ;

SQL> CREATE OR REPLACE TRIGGER CS.testtab_trg
2 BEFORE INSERT OR UPDATE
3 ON CS.TESTTAB
4 REFERENCING NEW AS New OLD AS Old
5 FOR EACH ROW
6 BEGIN
7
8 If :new.text is null
9 then
10 :new.text := ‘ ‘;
11 end if;
12
13
14 EXCEPTION
15 WHEN OTHERS THEN
16 — Consider logging the error and then re-raise
17 RAISE;
18 END testtab_trg;
19 /
SQL> show error
Keine Fehler.
SQL>

 

 

  • DBMS_SQLTUNE does not see existing indexes

 

  • PK constraint wo unique index on char and number column

as seen in https://richardfoote.wordpress.com/2008/06/04/primary-keys-and-non-unique-indexes-whats-really-happening/

CREATE TABLE TESTTAB
(
ID VARCHAR2(10 BYTE),
TEXT VARCHAR2(10 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;

CREATE INDEX ID_IND ON TESTTAB
(NLSSORT(“ID”,’nls_sort=”BINARY_CI”’))
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

ALTER TABLE TESTTAB ADD (
CONSTRAINT TESTTAB_PK
PRIMARY KEY
(ID)
USING INDEX ID_IND
ENABLE VALIDATE);

  • list-partitioned table

SQL> CREATE TABLE t1 (
2 id NUMBER,
3 country_code VARCHAR2(3),
4 record_type VARCHAR2(5),
5 descriptions VARCHAR2(50),
6 CONSTRAINT t1_pk PRIMARY KEY (id)
7 )
8 PARTITION BY LIST (country_code, record_type)
9 (
10 PARTITION part_gbr_abc VALUES ((‘GBR’,’A’), (‘GBR’,’B’), (‘GBR’,’C’))
11 PARTITION part_ire_ab VALUES ((‘IRE’,’A’), (‘IRE’,’B’)),
12 PARTITION part_usa_a VALUES ((‘USA’,’A’)),
13 PARTITION part_others VALUES (DEFAULT)
14 );
PARTITION BY LIST (country_code, record_type)
*
FEHLER in Zeile 8:
ORA-03001: Funktion nicht implementiert
ORA-00722: Feature “Collation-sensitivity for Partitioning Keys”

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s