Issue with a where clause by using Oracle

oracleplsqltrigger

I'm currently facing a problem while I'm adding a trigger to the database that I use at home.

I'm using : Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production

The problem that occurs is that, when I add my trigger it compiles, but impossible to access to the content of a VARCHAR2 variable, even if it contains something.

    typeEngine engine.type%TYPE = '74E';--engine.type is a VARCHAR2(4)

SELECT engine.capacity INTO capacityMax
FROM engine
WHERE engine.type = typeEngine;

That returns me a NO DATA FOUND Exception, but the data is in the database.

Thanking you all in advance if someone has the solution to solve that issue.

Best Answer

It may probably no longer be a problem, but in the code it seems you forgot that the assignment operator is :=, not =, so typeEngine would be null and the query returns no rows.

Related Solutions

Oracle dead with unknown reason

http://support.oracle.com

There are at least 3 notes about these errors.

"The issue has been fixed in 11.1.0.7 and 11.2."

Oracle: Creating Case Insensitive Database with NLS_COMP and NLS_SORT

Even if you manage to create a database with nls_comp=LINGUISTIC, nls_sort=BINARY_CI (from scratch (Custom Database or manually), not seed or template) , the indexes will be still created without these settings and you have to take specify them manually.

SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');

PARAMETER       VALUE
--------------- ---------------
NLS_COMP        LINGUISTIC
NLS_SORT        BINARY_CI

SQL> select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');

PARAMETER       VALUE
--------------- ---------------
NLS_SORT        BINARY_CI
NLS_COMP        LINGUISTIC

SQL> create table t1(id varchar2(1), something char(2000));

Table created.

SQL> insert into t1 select 'a', 'a' from dual connect by level <= 10000;

10000 rows created.

SQL> insert into t1 values ('b', 'b');

1 row created.

SQL> commit;

Commit complete.

SQL> create index t1_i1 on t1(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto for columns id size 2');

PL/SQL procedure successfully completed.

SQL> select /*+ index(t1 t1_i1) */ count(*) from t1 where id = 'B';

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  34cypvvhnwczh, child number 0
-------------------------------------
select /*+ index(t1 t1_i1) */ count(*) from t1 where id = 'B'

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   907 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     2 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  5001 | 10002 |   907   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NLSSORT("ID",'nls_sort=''BINARY_CI''')=HEXTORAW('6200'))

Index can not be used, hint does not help, and the estimated cardinality is just simply CEIL (NUM_ROWS / NUM_DISTINCT). This time with the proper index:

SQL> create index t1_i2 on t1(NLSSORT("ID",'nls_sort=''BINARY_CI'''));

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto for columns id size 2, SYS_NC00003$ size 2');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where id = 'B';

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9pvkbfkycz6q4, child number 0
-------------------------------------
select count(*) from t1 where id = 'B'

Plan hash value: 3225262789

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_I2 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."SYS_NC00003$"=HEXTORAW('6200'))

Best Answer

Related Solutions

Oracle dead with unknown reason

Oracle: Creating Case Insensitive Database with NLS_COMP and NLS_SORT

Related Question