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. The story I want to share with you is...
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.
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.
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 |
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;
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);
```
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 preventive measure while writing your PL/SQL procedure.
Also read:
Comments
Post a Comment
Thanks for your message. We will get back you.