Table of Contents
What is exception in Oracle PL SQL?
An exception is a PL/SQL error that is raised during program execution, either implicitly by TimesTen or explicitly by your program. Handle an exception by trapping it with a handler or propagating it to the calling environment.
How do you resolve divisor equal to zero in Oracle?
There are many ways to handle this error in Oracle.
- The first and foremost way is to enforce the business logic and try to ensure that the field doesn’t contain a 0 in the first place.
- Use the DECODE function DECODE(FIELD2,0,0,((FIELD1/FIELD2)*100))
- User ZERO_DIVIDE to handle a zero divisor error.
What is NVL command in SQL?
NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2 . If expr1 is not null, then NVL returns expr1 .
How can you retrieve the error code and error message of any Oracle server exception?
Retrieving the Error Code and Error Message: SQLCODE and SQLERRM. In an exception handler, you can use the built-in functions SQLCODE and SQLERRM to find out which error occurred and to get the associated error message. For internal exceptions, SQLCODE returns the number of the Oracle error.
What does ora-01403 mean in Oracle?
The ORA-01403 error (“no data found”) is representative of this type of Oracle problem, and while it involves a bit more coding savvy than other errors, the fix tends to be much more long term. The ORA-01403 error derives from an SQL query meant to return all data requested, but no data was found.
How to handle the no_data_found error in PL/SQL?
In your PL/SQL code, the SELECT .. INTO statement returns no rows, thus it raises NO_DATA_FOUND error. It never goes to the next line, i.e. your IF-ELSE construct. If you want to continue the operation, then you need to handle the EXCEPTION gracefully.
How to handle case of record not found or no data found?
Although you have put a WHERE condition, a better way would be to handle case of record not found or ‘No Data Found’ error. I would write above code with wrapping the SELECT statement with it’s own BEGIN/EXCEPTION/END block.
Can nologging trigger an ora-01403?
There are some occasions where NOLOGGING clauses can also trigger an ORA-01403. In some SQL operations, if you encounter a redo log record using a NOLOGGING clause, the record will be skipped and changes will be applied further along.