Re: Using packaged constants in queries

This'll be the last note for me for the night...yawn.

Anyway.

A couple questions:

1. Why would you bother storing and retrieving constants in packages?
2. To get something out of a package, you don't SELECT from it, you have to EXECUTE it.
3. You could write a PL/SQL function that you could call within SQL like any native Oracle function, but why?
4. If there's some reason you can't or don't want to just put these constants into your code (like, they change), why not just put them in an Oracle table and SELECT them? If they DO change, that's all the more reason you wouldn't want to store and retrieve them via some (from what I know of what you're trying to accomplish overall, which is little) PL/SQL code that you'd have to maintain whenever your constants changed.

Anyhow to sum up, you can't call a package with a select, it's an executable block of code. You can reference a function you write in PL/SQL in a select statement and that will make it execute. To be really unnecessarily evil and Byzantine, you COULD have the function fire an execute call to a package or procedure... I COULD walk to San Francisco from here in Montana, as well.

Is there some reason you have to store constants in this way that I am too dense to understand? A table really is what's meant for storing data; packages, procedures, functions, and triggers are really meant to store logic that modifies, stores, retrieves, or deletes data in tables, or interceding between some other process and the tables below; I don't believe it was intended to actually store data inside the program logic.

What's wrong with creating table myconstants, with a primary key column storing the name of the constant, and a NUMBER column to store its value, and just doing

select constant_value from myconstants where constant_name = 'value_of_G_eng'

(which is 32 feet per second per second, hence the eng suffix; I don't have the energy to put it in metric form.)

or to get fancier (bearing in mind again I have no idea what you are trying to do with all this)

have a table myconstants, with columns

constant_name                varchar2(30)
system_of_measurement  char(1)
constant_value                 number
unit_of_measurement      varchar2(30)

and create a composite Primary Key on constant_name, system_of_measurement, so you could store G, the acceleration of gravity, and get it out with:

select constant_value, unit_of_measurement from myconstants
where constant_name = 'G'
and
system_of_measurement = 'E';

and you'd get back

32 feet per second per second

Does this help at all?

I mean alternatively you could just select whatever you want from poor DUAL by hardcoding it in the query,

select 'whatiwantoget' from dual;

will give you back

whatiwanttoget

All this PL/SQL stuff baffles me as far as why you would go to all that trouble.

RSH.
or what am I missing here?
"Alexander Miroshnikov" <alexander_miroshnikov_at_hotmail.com> wrote in message news:6e2c7cae5af1ab22f05aa11f8f30fada.73752_at_mygate.mailgate.org...
> Dear colleagues,
>
> I would like to take the constant declared in my package and use it in
> my query.
>
> Please have a look at my test below -
>
> -- test package
> create or replace package my_package as
> my_const constant integer := 1;
> end;
> /
>
> -- test query
> select my_package.my_const from dual
> /
>
> -- result
> select my_package.my_const from dual
> *
> ERROR at line 1:
> ORA-00904: invalid column name
>
> Could you tell me why Oracle is unhappy and whether there is any way to
> use packaged constants in queries?
>
> Many Thanks,
>
> Alex
>
> Oracle 8.1.7 for Solaris.
>
>
> --
> Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Received on Thu Feb 28 2002 - 06:39:42 CST