Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: using constants

Re: using constants

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 29 Nov 2007 11:39:37 -0800
Message-ID: <474f1579$1@news.victoria.tc.ca>


Marten Lehmann (lehmannmapson_at_cnm.de) wrote:
: Hello,

: currently, we are using strings like "pending" or "finished" in some
: tables to specify the state of certain jobs stored in these tables.
: These values are self-explaining. These columns are indexed, of course.

: But I guess, lookups of jobs with certain states would be much faster,
: if the states would be stored is integer values, e.g. pending = 1,
: finished = 2 and so on. However, these values wouldn't be
: self-explaining any more.

: As long as I'm working with Java, I could defined constants within a
: class, so instead of integer values, I could use constants like
: State.PENDING or State.FINISHED.

: Is it possible to create such constants directly in Oracle, so I can use
: them within SQL-queries like

: select * from jobs where state = State.PENDING

create package states_pkg as

        function pending return number ;
end states_pkg;

create package body states_pkg as

        function pending return number is begin return 1; end pending; end states_pkg;

        select * from jobs where state = States_pkg.PENDING

I wouldn't assume it is faster.

I like to do this, even with readable string values, because a function can be checked at compile time whereas strings are not

        select * from jobs where state = 'Pending' -- whoops! Received on Thu Nov 29 2007 - 13:39:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US