Search code examples
sqloracle-databasedatabase-administration

Oracle - Hint CURSOR_SHARING_EXACT is not working in 'Select Case' statements


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:


Solution

  • 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;