Re: how to use of constant in a package?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/11
Message-ID: <33f12ec1.6752559@newshost>#1/1

On Fri, 08 Aug 1997 14:16:40 +0100, Sandeep Patwardhan <patwardhan_at_logica.com> wrote:

>Hi,
> 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
>
>I would really be thankful if anyone could please solve this problem
>Thanks,
>Sandeep
>mail:patwardhan_at_logica.com

You must wrap the package constant in a function call. PL/SQL functions may be called from SQL but not variables/procedures.

The package would look something like this:

create or replace package declare_const
as

    x number;  

    function get_x return number;
    pragma restrict_references( get_x, wnds, rnds, wnps );  

    pragma restrict_references( declare_const, wnds, rnds, wnps, rnps ); end;
/  

create or replace package body declare_const as  

function get_x return number as begin return x; end;  

end;
/

And I recommend creating the view using DUAL as below. This will make it so the pl/sql function returning a constant gets called ONCE per query as opposed to ONCE per ROW per query.  

create view testing_view
as
select * from user_tab_columns
where column_id = (select declare_const.get_x from dual ) /  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 11 1997 - 00:00:00 CDT