Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
similar problem was here: stackoverflow.com/questions/8401785/… –  Franek Mar 9 '14 at 20:55
    
@Franek OP explicitly stated engine.type is varchar2(4) so this can't be char vs. varchar2 issue. –  user272735 Mar 10 '14 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 Mar 10 '14 at 7:08
    
Can you show the entire trigger script? Including the create or replace –  Threaten Mar 11 '14 at 23:04

1 Answer 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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