PL SQL: How to Fix Errors

February 17, 2018

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.

PL SQL

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

```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.

BlockChain | Hadoop | No-SQL

ApplyBigAnalytics