| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help on using Package constants within a SQL Statement
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 )
/
On Tue, 09 Sep 1997 16:11:48 -0600, Nathan Secrist <nsecrist_at_evolving.com> wrote:
>Romeo,
>
>To refer to your literal:
>
>Select * from table_name
>where field1 = my_Literals.Timeframe_Immediate;
>
>
>That's all there is to it. Just make sure that
>you load your literal package into the database
>before you load the package that will refer to the
>literal, or you will get an error.
>
>Good luck.
>
>
>Romeo D'Souza wrote:
>>
>> Hi,
>>
>> I was wondering if anybody could give me some direction as to how I
>> could
>> use a literal that is declared within a package in my SQL statement.
>> Note the statement will be external to the package.
>>
>> e.g. the literals would be defined in a package something like this:
>>
>> CREATE OR REPLACE Package my_Literals AS
>> Timeframe_Immediate CONSTANT VARCHAR2(4) := 'IMMED';
>> END my_Literals;
>>
>> I now want to reference the CONSTANT Timeframe_Immediate in a SQL
>> statement
>> external to the package, something like:
>> SELECT my_Literals.Timeframe_Immediate FROM DUAL;
>>
>> Thanks in advance
>> Romeo D'Souza
>
>
>
>-----------------------------------------------------------------
>| Nathan G. Secrist - Oracle DBA |
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |