Java7
PL/SQL
ORA-06502: PL/SQL: numeric or value error (How to safeguard your stored procedures from requirement led changes?)
Jun 30th
Today we faced a particular interesting problem, we found that Oracle was throwing “ORA-06502: PL/SQL: numeric or value error”, when we did a Google quick check, it seemed that an attempt was made in the DBMS to assign a value larger than the declared size of the holder. i.e. trying to assign a 20 characters String to a 10 character varchar.
ORA-06502: PL/SQL: numeric or value error
What happened was, there was a particular table and a few fields in it with more or less fixed datatype and size, then one fine day owing to changing requirements, one of the fields had its size lengthened. I.e. from varchar2(20) to varchar2(30). The change impacted an adjacent stored procedure that was still expecting varchar2(20) size. The error “ORA-06502: PL/SQL: numeric or value error” was subsequently thrown.
Stored procedure declaring variable “pegasus_NPP” as VARCHAR(20):
Table the above stored procedure was drawing data from:
Note that the data length for the column is VARCHAR(30).
To effectively eliminate this issue is not to re-declare the variable in the stored procedure as VARCHAR(30), yes it solves the above gap but does not safeguard you from future changes.
The solution
Declare your variables as TYPEs based on the column definitions in your tables.
Use:
dept_name dept.dept_name%TYPE;Instead of:
dept_name VARCHAR2(50);Cheers!
Reference
How to minimize the coupling/dependency between physical DDL changes and PL/SQL changes?
What does “%Type” mean in Oracle sql?
History
July 5, 2011 Added link What does “%Type” mean in Oracle sql?
Business logic at the data layer?
Jun 8th
Forget all the things Oracle told you about PL/SQL, or not. Forget the piss poor attempts of the PSP “PL/SQL Server Pages” Oracle had taught you, or not. Perhaps good to mention that there is even a book out there teaching you to build an application out of SQL, PL/SQL and PSP – Learning Oracle PL/SQL by Bill Pribyl with Steven Feuerstein.
Lets take a step back and remember that the database is a flat file, or many flat files arguably, and Oracle, is a Database Management System (DBMS) vendor, a leading one. Oracle is a successful provider of the DBMS system that complies (not strictly) with SQL92, so that you may manipulate data in a set and mathematical manner abstracted with English like 4th generation language.
Many years ago, DBMS vendors started building a intermediary layer out of their successful DBMS products, such as T/SQL for Microsoft’s SQL Server, and PL/SQL for Oracle’s PL/SQL. While most of these sound too good to be true, business logic at the data layer is essentially not correct.
Oracle is arguably the best DBMS you may have for your money’s all worthed, Oracle spends a humongous amount of money to keep up with the algorithms that really differentiates itself from other DBMS vendors, and with the new kid in the neighborhood - Oracle Exadata being a monster machine primarily due to the existence of SSD (Solid State Drives), speed and execution is still not all the reason to put your business logic at the data layer.
What about the middle tier layer (business layer)? it performs business logic, it accesses a database for you, accesses another system, transactions, security, logging, auditing, and much much more.
If speed is the reason, you should be developing in Perl for pattern matching and character manipulation. If business logic is the evident, there is Business Rules Engine, BPMS, and so on. (BPM is a big business blind to companies still wiring if/else into codes)
Shoot me, for i know not no PL/SQL, but am i likely to be the solution architect of an system that uses PL/SQL? No, definitely.
To quote Shakespeare’s original “To be or not to be” from Hamlet, this, you decide. : )
But don’t be dis-encouraged and become less attached from your job because you realized this amounts to all the things you hear about the better side of software because there is never going to be a “straight” company or an organization that is going to incorporate all the good design patterns, all the right use of technologies. And i always believe this that, it isn’t a problem until you have a solution. To set things straight, it is about giving solutions, putting plans into action, not walking away from it because you didn’t like it.
