RE: Strange Behaviour (with Test Case)
Date: Wed, 5 Aug 2015 11:51:26 -0700
Message-ID: <BLU179-W926CE742BBCC8D3E368C56EB750_at_phx.gbl>
There goes my theory (that the client was the culprit) OK it is the server.
I will now guess that the problem will be cured by restarting the entire Exadata stack. Iggy
Date: Wed, 5 Aug 2015 19:30:30 +0800
Subject: Re: Strange Behaviour (with Test Case)
From: jolliffe_at_gmail.com
To: iggy_fernandez_at_hotmail.com
CC: oracle-l_at_freelists.org
Client is same in each case (SQL Developer) but also same in sqlplus. Below the info you requested (from sqlplus):
SQL> SET DEFINE OFF
SQL> SET LINESIZE 180
SQL> set echo on
SQL> set time on
19:27:44 SQL> set timing on
19:27:44 SQL> set feedback on
19:27:45 SQL>
19:27:47 SQL>
19:27:47 SQL>
19:27:47 SQL> DROP TABLE TEST_FACT;
DROP TABLE TEST_FACT
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.05
19:28:18 SQL> CREATE TABLE TEST_FACT(CHRTYPE VARCHAR2(3), RATE NUMBER);
Table created.
Elapsed: 00:00:00.02
19:28:18 SQL>
19:28:19 SQL> INSERT INTO TEST_FACT VALUES('R03', 1.3);
1 row created.
Elapsed: 00:00:00.02
19:28:19 SQL> INSERT INTO TEST_FACT VALUES('R03', 1.3);
1 row created.
Elapsed: 00:00:00.00
19:28:19 SQL> INSERT INTO TEST_FACT VALUES('LDU', 0.21);
1 row created.
Elapsed: 00:00:00.00
19:28:19 SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
19:28:19 SQL>
19:28:19 SQL> -- Insert the result into a new table and retrieve the result from that table
19:28:19 SQL> DROP TABLE TEST_FACT_RESULT;
DROP TABLE TEST_FACT_RESULT
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.00
19:28:19 SQL> CREATE TABLE TEST_FACT_RESULT(RATE NUMBER);
Table created.
Elapsed: 00:00:00.00
19:28:19 SQL>
19:28:19 SQL> INSERT INTO TEST_FACT_RESULT
19:28:19 2 SELECT SUM(
19:28:19 3 CASE
19:28:19 4 WHEN (CHRTYPE = 'R03' OR CHRTYPE = 'LDU')
19:28:19 5 THEN RATE /10
19:28:19 6 END )
19:28:19 7 FROM TEST_FACT;
1 row created.
Elapsed: 00:00:00.00
19:28:19 SQL>
19:28:19 SQL> SELECT RATE FROM TEST_FACT_RESULT;
RATE
----------
-8.70E+103
1 row selected.
Elapsed: 00:00:00.01
19:28:19 SQL>
19:28:19 SQL> -- Retrieve the result directly
19:28:19 SQL> SELECT SUM(
19:28:19 2 CASE
19:28:19 3 WHEN (CHRTYPE = 'R03' OR CHRTYPE = 'LDU')
19:28:19 4 THEN RATE /10
19:28:19 5 END )
19:28:19 6 FROM TEST_FACT;
SUM(CASEWHEN(CHRTYPE='R03'ORCHRTYPE='LDU')THENRATE/10END)
---------------------------------------------------------
-8.70E+103
1 row selected.
Elapsed: 00:00:00.01
On 5 August 2015 at 18:13, Iggy Fernandez <iggy_fernandez_at_hotmail.com> wrote:
I'm willing to bet that the problem is not with the database engine but with the client. The client has to determine the data type of the result and initialize an appropriate memory structure to receive the result.
I'd be interested in seeing the entire output of the following script from SQL*Plus (including the banner, exit message, and any error messages).
set echo onset time onset timing onset feedback on
DROP TABLE TEST_FACT;CREATE TABLE TEST_FACT(CHRTYPE VARCHAR2(3), RATE NUMBER);
INSERT INTO TEST_FACT VALUES('R03', 1.3);INSERT INTO TEST_FACT VALUES('R03', 1.3);INSERT INTO TEST_FACT VALUES('LDU', 0.21);COMMIT;
-- Insert the result into a new table and retrieve the result from that tableDROP TABLE TEST_FACT_RESULT;CREATE TABLE TEST_FACT_RESULT(RATE NUMBER);
INSERT INTO TEST_FACT_RESULTSELECT SUM( CASE WHEN (CHRTYPE = 'R03' OR CHRTYPE = 'LDU') THEN RATE /10 END ) FROM TEST_FACT; SELECT RATE FROM TEST_FACT_RESULT;Cheers,Iggy
-- Retrieve the result directlySELECT SUM( CASE WHEN (CHRTYPE = 'R03' OR CHRTYPE = 'LDU') THEN RATE /10 END ) FROM TEST_FACT;
--
Iggy Fernandez
Email: iggy_fernandez_at_hotmail.com
Cellphone: (925) 478 3161
Blog: Explaining the Explain Plan
Author of Beginning Oracle Database 12c Administration Editor of the NoCOUG Journal
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 05 2015 - 20:51:26 CEST