SQL*Plus Bug or My own bug :)

update:(5 hours later after Nicolas Gasparotto’s comment) the post below is a good explanation for invalid dba exception I got for my own actions. You need to read the post together with first two comments.

I have 2 excuses for my dodgy conclusion below:)
1- I am on diet and halved the calorie intake, so my brain is not fed enough.
2- The way it gives the same result on every different/client machine combination lead me wrong idea and to be honest, I wasnt focusing on the content of trigger because of a successful case.

At the end of the day I am %100 guilty because I act like a developer and blamed ORacle at first place:)

Moral of the story for me ; check before publishing, especially your NLS settings

I think I found a bug which is available for the sqlplus clients above 10.2.0.1 .

What I was doing was an insert on a table. Table had a trigger which inserts a non-default value for a column that wasn’t in the insert list.
Insert statement wasn’t working on my 11.1.0.6 client but works fine on TOAD or SQLPLUSW exe.

This is the test case I prepared for this issue; (exactly the same trigger on our production database)

Table is on 10.2.0.3 database (DB version doesnt matter the problem is client)

SQL> drop table test_c purge;

Table dropped.

SQL> create table test_c (test_count number,tr_date date);

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER TRG_TST
2 BEFORE INSERT OR UPDATE ON TEST_c
3 FOR EACH ROW
4 WHEN (
5 new.TR_DATE is NULL
6 )
7 BEGIN
8 :new.TR_DATE := '31-DEC-2049';
9 :new.TEST_COUNT := :new.TEST_COUNT + 1;
10 END;
11 /

Trigger created.

This is the behaviour for the insert on different client versions for sqlplus.exe (Windows) and sqlplus (HP-UX)

On 10.2.0.1 client the tests case works.


SQL> insert into test_c (TEST_COUNT) values (0);

1 row created.


On 10.2.0.2 client test case fails.
windows

SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'

Unix

SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'


On 10.2.0.4
windows

SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'

Unix

On 11.1.0.6
Windows
SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'

I don’t have a unix 11G client, but I think it will fail on it as well.

I think its time to raise an SR to Oracle Support.

Query Result Cache on Encrypted Tablespace on 11G

Question: Will query result cache work on Encrypted tablespaces.

I asked this question to Tom Kyte after his All About Encryption presentation at UKOUG 2008. His answer was ” I think it will work but you need to try”

After 4 months, here is the answer to my question;

 
SQL> ---Encrypted tablespace creation
SQL> CREATE TABLESPACE encryptedtst
  2  DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\ORACOS\encryptedtst_01.dbf' SIZE 128K
  3  AUTOEXTEND ON NEXT 64K
  4  ENCRYPTION USING 'AES256'
  5  DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> connect hr/hr;
Connected.

SQL> alter table employees enable row movement;

Table altered.

SQL> alter table employees
  2  move tablespace encryptedtst;

Table altered.

SQL> select manager_id,sum(salary) from employees group by manager_id;

MANAGER_ID SUM(SALARY)
---------- -----------
       100      155400
                 27000
       123       25900
       120       22100
	..	...

19 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    18 |   144 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |    18 |   144 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   856 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

SQL> select /*+ result_cache*/manager_id,sum(salary) from employees group by manager_id;

MANAGER_ID SUM(SALARY)
---------- -----------
       100      155400
                 27000
       123       25900
       120       22100
	..	...

19 rows selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 1u45jnmw74kd60brbww9gr7ss8 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   856 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id"

SQL> select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id;

MANAGER_ID SUM(SALARY)
---------- -----------
       100      155400
                 27000
       123       25900
       120       22100
	..	...

19 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 1u45jnmw74kd60brbww9gr7ss8 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   856 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id"

SQL>


SQL>--Check if it is really cached
SQL> select name from v$result_cache_objects;

NAME
-----------------------------------------------------------------------------------------------
HR.EMPLOYEES
select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id

As you see on the explain plan and v$result_cache_table, the result is cached

Conclusion is; Oracle did not forget to combine two new features together by the help of keeping objects of encrypted tablespaces, unencrypted in SGA for better performance