How to prevent some common errors or exceptions while writing PL/SQL procedures in your project.
- The number one and primary one is assigning variables non-numeric to numeric.....This is one kind of area where you need to look in while writing PL/SQL procedure.
- PL/SQL are nothing but an invitation for trouble. They are all centered on data types and implicit conversion.
What's implicit conversion?
Let's say you have number held in a varchar2 data type variable, v_value. You try assigning n_value, a number data type variable, that value with the following line of code:n_value := v_value;
That should work, right?Yes, it should, but when it doesn't, because you don't actually have a numeric literal stored in variable v_value, the implicit data type conversion will raise an "unexpected" exception in your program unit.
Another most common issue is assigning DATE field to numeric field while writing PL/SQL procedure. Usually, it will not work, and it will through a conversion error.
You want pass a date value to a function that will return the time in seconds since midnight, January 1, 1980. The function requires the date be passed as a varchar2 parameter in the form DD-MON-YY.
```sql d_value date := sysdate; n_value number; ```
```sql BEGIN n_value := date_to_long(d_value); ```
Not exactly. If the current NLS_DATE_FORMAT for the session is DD-MON-YY (the default), it will work, but not if it is YYYYMMDD HH24MISS, as I set mine every time I log in to SQL*Plus.
The above two kinds of errors you can avoid as preventive measure while writing your PL/SQL procedure.