Getting error "The Parser did not identify the text as a valid SQL statement" when using COLLATE BINARY_AI.
To reproduce the problem, follow the steps below:
Run the script below in Toad:
-- 1. Create the USERS table
CREATE TABLE users (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR2(100)
);
-- 2. Insert sample data
INSERT INTO users (name) VALUES ('José');
INSERT INTO users (name) VALUES ('Jose');
INSERT INTO users (name) VALUES ('JOSÉ');
INSERT INTO users (name) VALUES ('JOSE');
INSERT INTO users (name) VALUES ('María');
INSERT INTO users (name) VALUES ('maria');
INSERT INTO users (name) VALUES ('MARIO');
COMMIT;
-- 3. Select using COLLATE BINARY_AI (case and accent insensitive)
SELECT *
FROM users
WHERE name COLLATE BINARY_AI = 'jose';
-- 4. Order using COLLATE BINARY_AI
SELECT *
FROM users
ORDER BY name COLLATE BINARY_AI;
As you can see, both queries above work fine but when you try to optimize them with sql optimizer you get the error:
------------------
The Parser did not identify the text as a valid SQL statement.
SELECT *
FROM users
WHERE name COLLATE BINARY_AI = 'jose';
This type of SQL statement is not supported.
------------------
WORKAROUND
None
STATUS
Enhancement request TSA-41 has been submitted to Development for consideration in a future release of SQL Optimizer for Oracle
