This question touches on a few different parts of Oracle that I'm not particularly familiar with so bear with me:
I am trying to redesign some of the procedures and functions within the database I manage. One of the most annoying things is the use of integers to express the status of rows.
For example rows that need to be processed get the number 3, rows that have been processed get the number 0 and rows that are in the middle of processing get number 1. This is a simplified example.
What I was hoping to do was code those integers into constants so when the procedures are written the words would be self-documenting...
I've tried to use packages and functions to manage these constants. Everything comes out a bit messy. The cleanest I've found is:
CREATE OR REPLACE FUNCTION PROCESSED RETURN NUMBER AS BEGIN RETURN 0; END; /
This allows you to type the sql below which looks relatively neat.
SELECT rows FROM table WHERE status = PROCESSED;
The problem I've found is that these columns are indexed to return quickly but the functions mean the indexes aren't used.
With that background my question is: How should constants be managed in Oracle effectively? What solution has the best trade off for visual simplicity, logical organisation and database performance.