I am using cursor_sharing='FORCE'
This can be set by SYSTEM user executing following statement:
alter system set cursor_sharing='FORCE' scope=both;
For few queries I want to use the hint CURSOR_SHARING_EXACT, but it is failing in CASE statements.
For example, Query1 works, but same query with hint CURSOR_SHARING_EXACT (Query2) fails.
Please suggest why there is a missing expression
Note: I cannot put the CURSOR_SHARING_EXACT on the outer SELECT where it works.
Query1:
SELECT CASE
WHEN (select 1 from dual where 1 = 2) IS NOT NULL
THEN 'Y' ELSE 'N' END temp FROM dual;
Output of Query1: N
Query2:
SELECT CASE
WHEN (select /*+ CURSOR_SHARING_EXACT */ 1 from dual where 1 = 2) IS NOT NULL
THEN 'Y' ELSE 'N' END temp FROM dual;
Output(Error) of Query2:
Error starting at line 1 in command: SELECT CASE WHEN (select /*+ CURSOR_SHARING_EXACT */ 1 from dual where 1 = 2) IS NOT NULL THEN 'Y' ELSE 'N' END temp FROM dual Error at Command Line:2 Column:7 Error report: SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:
1) I put the HINT
on the parent query and now it's ok, try to do so:
SELECT /*+ CURSOR_SHARING_EXACT */
CASE
WHEN (SELECT /*+ CURSOR_SHARING_EXACT */ 1 from dual where 1 = 2) IS NOT NULL
THEN 'Y'
ELSE 'N'
END temp
FROM dual;
2) OR as a temporary solution maybe you can use decode
instaed of case
:
SELECT DECODE ((select /*+ CURSOR_SHARING_EXACT */ 1 from dual where 1 = 2), NULL, 'N', 'Y') FROM dual;
3) Also another Alternative solution is:
WITH demo AS (
SELECT /*+ CURSOR_SHARING_EXACT */ <col1> from <table_name>
where <some_conditions>)
SELECT CASE
WHEN d.col1 IS NOT NULL
THEN 'Y'
ELSE 'N'
END temp
FROM dual, demo d;