-
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...
-
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
-
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...
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|