DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: SQL CASE statement with Multiple THEN's

  1. #11
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    I see the same issue with the context approach:

    Code:
    CREATE OR REPLACE CONTEXT app_ctx USING set_ctx;
    
    CREATE OR REPLACE PACKAGE set_ctx AS
    FUNCTION set_attr (p_attr1  IN  VARCHAR2,
                       p_attr2  IN  VARCHAR2)
      RETURN VARCHAR2;
    END set_ctx;
    /
    
    
    CREATE OR REPLACE PACKAGE BODY set_ctx IS
    
    FUNCTION set_attr (p_attr1  IN  VARCHAR2,
                        p_attr2  IN  VARCHAR2)
      RETURN VARCHAR2 IS
    BEGIN
      DBMS_SESSION.set_context('app_ctx', 'attr1', p_attr1);
      DBMS_SESSION.set_context('app_ctx', 'attr2', p_attr2);
      RETURN 'COMPLETE';
    END;
    
    END set_ctx;
    /
    SHOW ERRORS
    
    
    SELECT SYS_CONTEXT( 'app_ctx', 'attr1' ) AS get_1
    ,      SYS_CONTEXT( 'app_ctx', 'attr2' ) AS get_2
    FROM  (SELECT CASE
                    WHEN 1 = 1 THEN
                      set_ctx.set_attr ('definitely', 'not')
                    END
           FROM   dual
           ORDER BY 1);
    
    
    GET_1
    -----------------------------------------------
    GET_2
    -----------------------------------------------
    
    
    
    
    1 row selected.
    
    
    SELECT CASE
                    WHEN 1 = 1 THEN
                      set_ctx.set_attr ('definitely', 'not')
                    END
           FROM   dual
           ORDER BY 1;
           
    CASEWHEN1=1THENSET_CTX.SET_ATT
    --------------------------------
    COMPLETE
    
    1 row selected.
    
    SELECT SYS_CONTEXT( 'app_ctx', 'attr1' ) AS get_1
    ,      SYS_CONTEXT( 'app_ctx', 'attr2' ) AS get_2
    FROM dual;
    
    
    GET_1
    ----------------------------------------------
    GET_2
    ----------------------------------------------
    definitely
    not
    
    
    1 row selected.
    Am I going mad?

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  2. #12
    Join Date
    Jun 2005
    Location
    UK
    Posts
    11
    You're not going mad ( well almost, but not quite ). It's something to do with FAST DUAL, as mentioned on my previous post. If we use a pipelined function as a single row source instead, it works:-

    Code:
    SQL> SELECT package_name.get_a,
      2         package_name.get_b
      3  FROM  ( SELECT (CASE
      4                   WHEN 1 = 1 THEN
      5                     package_name.set_a_b (1, 2)
      6                   END)
      7           FROM   TABLE( one_row )
      8           ORDER BY 1);
    
         GET_A      GET_B
    ---------- ----------
             1          2
    Wonder what optimisation FAST DUAL has that makes it behave like this?

    Regards
    Adrian

  3. #13
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Quote Originally Posted by billiauk
    Interesting this. Same is true on Windows. At first I thought it could be merging, but I see that Padders included an ORDER BY, presumably for this very reason ( i.e. force no merge ).

    The only difference in 10g with this that I can see is the FAST DUAL optimisation. I can't see anything else in trace. It could be a bug of course. It doesn't appear to be anything to do with the PLSQL_OPTIMIZE_LEVEL either - setting this to 0 made no difference.
    Sorry, didn't see this before posting the thing about the context suffering the same fate.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  4. #14
    Join Date
    Jun 2005
    Location
    UK
    Posts
    11
    Actually, FAST DUAL might be a red herring. Forcing "normal" DUAL doesn't see to make a difference either.

    Code:
    SQL> SELECT package_name.get_a,
      2         package_name.get_b
      3  FROM  ( SELECT (CASE
      4                   WHEN 1 = 1 THEN
      5                     package_name.set_a_b (1, 2)
      6                   END)
      7          ,       dummy
      8           FROM   dual
      9           ORDER BY 1);
    
         GET_A      GET_B
    ---------- ----------
    
    
    SQL>
    SQL>
    SQL> select * from table( dbms_xplan.display_cursor );
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------
    SQL_ID  79rukd61zu5sr, child number 0
    -------------------------------------
    SELECT package_name.get_a,        package_name.get_b FROM  ( SELECT
    (CASE                  WHEN 1 = 1 THEN
    package_name.set_a_b (1, 2)                  END)         ,       dummy
             FROM   dual          ORDER BY 1)
    
    Plan hash value: 2367845099
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
    |   1 |  VIEW              |      |     1 |       |     3  (34)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    Regards
    Adrian

  5. #15
    Join Date
    Jun 2005
    Location
    UK
    Posts
    11
    Right, this is my last post on this one. Including a column in the outer projection seems to help. Without it, there is no projection at all. I still think this is a bug as I can't see what difference this could make as the plans are the same.

    Code:
    SQL> exec dbms_session.reset_package;
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT package_name.get_a,
      2         package_name.get_b,
      3         some_col
      4  FROM  ( SELECT (CASE
      5                   WHEN 1 = 1 THEN
      6                     package_name.set_a_b (1, 2)
      7                   END) AS some_col
      8           FROM   dual
      9           ORDER BY 1);
    
         GET_A      GET_B SOME_COL
    ---------- ---------- ---------------
             1          2 COMPLETE
    I like the object type method best ;o)

    Regards
    Adrian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width