0

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.

4
  • similar problem was here: stackoverflow.com/questions/8401785/…
    – rtbf
    Commented Mar 9, 2014 at 20:55
  • @Franek OP explicitly stated engine.type is varchar2(4) so this can't be char vs. varchar2 issue.
    – user272735
    Commented Mar 10, 2014 at 5:19
  • I assume that you have an id field. Please show us the output of the following query: SELECT DUMP(engine.type, 1016),engine.type FROM engine where engine.id=12345; The expression ´where id=12345´ should be replaced by an appropriate expression. Maybe type is an sql or pl/sql keyword so replace engine.type by engine."TYPE" in your pl/sql code to avoid ambiguities and run your code again.
    – miracle173
    Commented Mar 10, 2014 at 7:08
  • Can you show the entire trigger script? Including the create or replace
    – Threaten
    Commented Mar 11, 2014 at 23:04

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.