PL SQL: How to Fix Errors
PL/SQL is procedural language, and the PL/SQL procedures you can call from any high-level language. This is depending on your project requirement.
How to prevent some common errors or exceptions while writing PL/SQL procedures in your project.
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.
![]() |
PL SQL |
- 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 is 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 to 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.
Sample PL/SQL
```sql
d_value date := sysdate;
n_value number;
```
```sql
BEGIN
n_value := date_to_long(d_value);
```
Sample PL/SQL
- Oracle's default date format is DD-MON-YY, so it will work fine, right?
- 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 a preventive measure while writing your PL/SQL procedure.
Comments
Post a Comment
Thanks for your message. We will get back you.