Re: how to use of constant in a package?

Yes, you can make use of global constants in PL/SQL!

EXAMPLE:

Package definition for global constants (Just package header...no body is required):

CREATE OR REPLACE PACKAGE global_const_read AS

        
   STATUS_OLD         CONSTANT NUMBER  :=  0;
   STATUS_ACTIVE      CONSTANT NUMBER  :=  1;
   STATUS_PENDING     CONSTANT NUMBER  :=  2;

END global_const_read;

How to reference the global constants in another package:

SNIP
.  

.
SELECT order_id
FROM orders
WHERE status = global_const_read.STATUS_OLD OR

        status = global_const_read.STATUS_ACTIVE; .
.        

This allows you to take all hardcoding of literals out of your packages and put them in one place. This makes it much easier if any of your literals change values.

The only thing to remember is that obviously the global constants package (global_const_read in the above example) must be loaded into the database first, or you will get errors loading other packages which refer to the global constants.

Hope this helps.

Nathan

|  Nathan G. Secrist  - Oracle DBA                              |
-----------------------------------------------------------------

Ever stop to think, and forget to start again?

Janek Metsallik wrote:
>
> Hi Sandeep,
>
> > I have a stored package wherein I have defined all constants.
> > How can I make use of this constant in where clause of select
> > statement for my view. e.g I have a package declare_const and have a > constant, x defined in it
 

> > I want to use it as -
> > create view test as
> > select * from table_name
> > where column = declare_const.x
>
> You cannot use PL/SQL constants in SQL. You can add functions to your
> package to return these constants. Don't forget PRAGMA
> RESTRICT_REFERENCES(function_name,WNDS,RNDS,WNPS) (check the WNXX and
> RNXX constants from reference), othervise you still do not have
> possibility to use the functions in SQL.
>
> Regards,
> Jan
  Received on Tue Aug 19 1997 - 00:00:00 CDT