Skip to Main Content
  • Questions
  • Returning a named constant in a SQL query

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jon.

Asked: June 23, 2003 - 3:33 pm UTC

Last updated: January 07, 2021 - 8:40 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Is it possible to return a named constant in a SQL query? In other words, let's say we want to return the equivalent of:

SELECT 'Business rule violation: '||to_char(bsnss_rule)
FROM violation_table;

But, we would like to pick up 'Business rule violation: ' from a package called PKG_Constants in which is declared a constant variable called, let's say businessRuleViolation. We like to keep our constants in a centrally located place where they can be easily changed without any additional programming changes. For example, if somebody objected to the wording of 'Business rule violation: ', we could just change PKG_Constants.businessRuleViolation to 'Business rule exception: '. This method seems to work well except in this case. Any suggestions?

and we said...

Inside of PLSQL -- since PLSQL variables are just binds, this:

declare
cursor c is select constant_pkg.bus_rule_violation || to_char(bsnss_rule)
from violation_table;
begin
for x in ( select constant_pkg.bus_rule_violation || to_char(bsnss_rule)
from violation_table )
loop
.....


open ref_cursor_to_be_returned_to_client for
select constant_pkg.bus_rule_violation || to_char(bsnss_rule)
from violation_table;

open ref_cursor_dynamic for
'select :x|| to_char(bsnss_rule)
from violation_table' using constant_pkg.bus_rule_violation ;

would all work fine. The issue would be if the SQL is in the client code (like VB, java, C). then what you can do is:


create or replace context my_ctx using my_pkg
/

create or replace package my_pkg
as
bus_rule_violation constant varchar2(255) default := 'xxxxxxxx';
...

procedure init;
end;
/

create or replace package body my_pkg
as

procedure init
is
begin
dbms_session.set_context( 'my_ctx', 'bus_rule_violation', bus_rul_violation);
....
end;

end;
/


then queries OUTSIDE of PLSQL would use

select sys_context( 'my_ctx', 'bus_rule_violation' ) || ......


after calling my_pkg.init once per session to fill them in.


Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How about compile time verification?

Denis, June 24, 2003 - 4:17 am UTC

Surely it's "cool" to use dbms_session and use sys_context for this purpose but..
1. It doesn't fully solves the problem because there's no compile time constant's name verification. And that's IMHO the main thing.
2. It's much simpler to write a function e.g. get_my_param(p_name varchar2) return varchar2 and use it without calling anything for a session

Is there any way to use constants and not to use literals?
To be?

Denis


Tom Kyte
June 24, 2003 - 7:49 am UTC

i wasn't trying to be "cool", I was trying to solve the problem but anyway

1) huh? what does that mean?

2) simplier, but check out the performance. I was trying to give you high performance, something that won't kill your system.


In PLSQL, i showed you how to use the constants.

OUTSIDE OF PLSQL, no, you cannot use plsql constants -- they do not "exist", you have to use sql constructs. And again, I showed you how.

You can use a logon trigger to "init" the package automagically I suppose as well

Thanks

Jon, June 24, 2003 - 8:19 am UTC

I knew that there were solutions in PL/SQL. I was hoping to be able to do it in native SQL, but you confirmed that isn't possible. I didn't think so, but you have many answers I am unaware of, so I thought it was worth asking the question. Thanks.

I think this is what he means...

Dan Kefford, June 24, 2003 - 10:11 am UTC

Original poster:

1. It doesn't fully solves the problem because there's no compile time
constant's name verification. And that's IMHO the main thing.

Tom:

1) huh? what does that mean?


I think he means that if you reference a constant (for the time being, assume so in a PL/SQL block), say C_FOO, and C_FOO is not declared, you would get a _compile-time_ error. Indeed, Java, C/C++, and other programmers use named constants to avoid typographical errors that would otherwise go unnoticed by the compiler.

You can always use PLSQL function

msc, June 25, 2003 - 7:47 am UTC

As a work-around you can always use a function. But be aware of performance issues - you do not want to use it with large result set (function will be executed for each returned row, even if deterministic). And returned type is varchar2(4000) not varchar2(255). But you get hard binding.


create or replace package MY_CONST is
function bus_rule_violation return varchar2 deterministic;
end;
/

create or replace package body MY_CONST is
-- constants
c_bus_rule_violation constant varchar2(255) := 'Business rule violation: ';

function bus_rule_violation return varchar2 is
begin
return c_bus_rule_violation;
end;
end;
/

select my_const.bus_rule_violation from dual;




Then what is the best practice?

Alexander, December 28, 2004 - 11:48 am UTC

Very interesting, thanks
I want to eliminate as many constants from both my SQL and my PL/SQL as possible

For PL/SQL, I have created a package

CREATE OR REPLACE PACKAGE CONST_STATES
AS
Waiting CONSTANT NUMBER := 1;
Ready CONSTANT NUMBER := 2;
-- more stuff here
END CONST_STATES;
/

That allowed me to get rid of all the 1's and 2's in all my packages, but not from SELECTs and not from VIEWs
What is the best practice, how would you rewrite

SELECT some columns FROM some table
WHERE status = 1 /* waiting */

Thanks in advance

Tom Kyte
December 28, 2004 - 11:58 am UTC

views are just like packages -- they are the way to "hide" things.

Look at the oracle data dictionary views, they are littered with decodes to turns 1, 2, 3, ... into something meaningful.


The selects in your code (plsql code) can certainly use the constants:


begin
for x in ( select * from t where status = const_states.waiting )
loop
.....


so, in views, use literals (they are your packages).

A reader, June 03, 2020 - 7:35 am UTC

Hi Tom,

If Oracle made it so that one could directly reference package constants from SQL (outside of PL/SQL), do you believe that would be a good thing?

I'm asking because if you don't, I'm probably still missing something about how to code this type of thing cleanly.

Thanks!
Al
Chris Saxon
June 03, 2020 - 2:17 pm UTC

There are many requests for this on the Database Ideas Forum, notably

https://community.oracle.com/ideas/3320

Where it's been filed as an enhancement request.

Though as Stew Ashton notes in the comments, scalar SQL macros - coming in 20c - allows you to do something similar.

https://community.oracle.com/ideas/3320#comment-1060601

Al, June 05, 2020 - 12:30 pm UTC

Thanks, Chris, for your reponse!

I believe we're talking about two different things. My comment is about package constants. Idea 3320 is about package variables. IMHO they are two different beasts, both use case-wise and implementation-wise. Personally, I have not had a need for package variable access in SQL. That would seem to defeat the purpose of data encapsulation through the package.

Package constants on the other hand are not data. They are code and can be resolved at compile time. None of the runtime implementation concerns mentioned in 3320 would apply. Yet they would yield a high code maintainability benefit in that you could define all your constants in one place instead of multiple.

Please note this difference is also highlighted in this comment:
https://community.oracle.com/ideas/3320#comment-1001052

I read up briefly about the 20c SQL Macros and understand they are purely a performance optimization. I.e. you still need write a getter wrapper to access your package constant just the way you can do it with a function today, but it will perform better. Code maintainability won't be better though, having to maintain function signatures in both package header and body for each constant.

Al
Chris Saxon
June 05, 2020 - 2:22 pm UTC

Well the example in the idea shows both constants AND variables, so it covers both ;)

The related ER is most definitely about package constants.

they are purely a performance optimization

There's a lot more to SQL Macros than that ;)

You're right you need to write a getter though.

No need for one getter per constant

Stew Ashton, June 05, 2020 - 5:34 pm UTC

In reply to this statement: "Code maintainability won't be better though, having to maintain function signatures in both package header and body for each constant."

You can have as many or as few getters as you want, and put them wherever you want. Example (not using SQL macros yet!):

create or replace function pkg_constant (p_const_name in varchar2) return number
authid definer as
  type tt_nums is table of number index by varchar2(512);
  lt_nums constant tt_nums := tt_nums(
    'utl_raw.big_endian' => utl_raw.big_endian,
    'utl_raw.little_endian' => utl_raw.little_endian,
    'utl_raw.machine_endian' => utl_raw.machine_endian,
    'apex_190100.wwv_flow.c_max_size_vc2' => APEX_190100.WWV_FLOW.c_max_size_vc2
  );
begin
  return lt_nums(lower(trim(p_const_name)));
end pkg_constant;
/
select pkg_constant('utl_raw.big_endian') big_endian
from dual;

BIG_ENDIAN
----------
         1
Since Oracle manages dependencies, if a package containing one of the constants is recompiled, then this function is invalidated and the cursor that refers to it will be invalidated as well, so the query will be reparsed.

You could conceivably have one getter function per SQL data type for constants (as a SQL macro) and another one per SQL data type for variables (as a classic function). New constant = add one line in the getter function for the constant's data type.

Not what you want of course but a bit closer than what you were saying.

Regards,
Stew

Correction on use of SQL macros

Stew Ashton, June 06, 2020 - 9:10 am UTC

In my previous review, I mentioned something about "one getter function per SQL data type". I now think that is wrong: a SQL macro always returns a string to be substituted into the SQL statement.

So in theory there could be one getter function for every package constant in the database - not that I think it's a good idea!

I would point out that the getter function should explicitly convert any number or datetime constants to strings. All relevant NLS settings should be passed to the getter function, otherwise it will not be deterministic! (This means my example code above should be rewritten.)

Regards,
Stew

Al, June 07, 2020 - 3:27 pm UTC

@Stew

WRT "Code maintainability won't be better though, having to maintain function signatures in both package header and body for each constant.":

In code maintainability I was assuming that you would want to include compile time reference safety. I.e. if you mistype the name of the constant someplace where you reference it, the compiler will complain rather than a run time inconsistency will occur. If you have one getter function per SQL data type, you will pass the name of the constant as a string and the compiler will have no way of verifying whether it's a valid constant name. If you have one getter per constant, the compiler will tell you if you misspelled anything.

That same compile time reference safety I would expect if SQL was allowed to access package constants directly.

Al
Chris Saxon
June 08, 2020 - 10:25 am UTC

You can reference the package constant in the SQL macro, so you have compile-time safety for the macro:

create or replace package pkg as 
  c constant integer := 42;
end;
/

create or replace function f 
  return varchar2  sql_macro ( scalar ) as
begin
  return pkg.c;
end f;
/

select f from dual;

F    
   42 

Al, June 07, 2020 - 3:34 pm UTC

@Chris

WRT "The related ER is most definitely about package constants."

I wasn't aware there's a related ER. That's good news. Can you share a pointer to it?

Thanks,
Al
Chris Saxon
June 08, 2020 - 10:26 am UTC

It's listed right there in the idea:

This ER is now referenced as: Enh 28147631 - MAKE PUBLIC PACKAGE VARIABLES AVAILABLE TO SQL
Base ER: ENH 6525013 - ALLOW A PACKAGE CONSTANT IN SQL WHERE A PLACEHOLDER IS LEGAL

@Al: "compiler"?

Stew Ashton, June 07, 2020 - 8:16 pm UTC

Al, you say " If you have one getter function per SQL data type, you will pass the name of the constant as a string and the compiler will have no way of verifying whether it's a valid constant name."

There is no SQL "compiler". A SQL statement is parsed, not compiled. SQL macros are executed at parse time, so a specific getter function and a generic getter function would be checked at the same time. Of course, with a generic getter function one would have to code a meaningful exception.

I have had this kind of conversation with others concerning enhancement requests. People don't seem to like the suggestion that they can get (mostly) what they want if they consent to a little extra work. When 20c becomes available to you sometime in the future, I hope you will consider SQL macros for accessing package constants, while waiting for the enhancement that will certainly come, if it ever does, in an even later version.

Regards,
Stew
Chris Saxon
June 08, 2020 - 10:27 am UTC

You're right about SQL.

The macro definition is PL/SQL though - so you can have compile time safety for this.

Al, June 09, 2020 - 12:03 pm UTC

@Chris WRT Enh 28147631

I guess I meant a pointer you can follow and then read the actual ER. :-)
Chris Saxon
June 09, 2020 - 4:34 pm UTC

You can view details of published ERs in MOS. Sadly those two aren't published.

Al, June 09, 2020 - 12:49 pm UTC

@Stew

WRT: "A SQL statement is parsed, not compiled"

SQL Developer offers an option to "compile" a view though? I think the difference is irrelevant for reference safety though: The main benefit I would want is that something in the database will complain if I e.g. try to define a view referencing a non-existent package constant the same way it complains about a referenced non-existent table (except that in the error message it will hopefully say which constant reference was invalid).


WRT "with a generic getter function one would have to code a meaningful exception"

This is not the same as compile-/parse-time safety. You may not hit that exception until a user of yours enters a strange input value into a form in production. On the other hand, compile-/parse-time safety guarantees no dangling constant references at object definition time. I.e. you will catch that in your DEV environment before it ever gets near production.


WRT "I hope you will consider SQL macros for accessing package constants"

I don't see a reason not to use SQL macros as they promise better performance than functions. They just don't appear to offer anything over functions in terms of code maintainability, which is the OP's main concern here.

"Compile" and "parse" are not that similar

Stew Ashton, June 10, 2020 - 8:06 am UTC

>> SQL Developer offers an option to "compile" a view though?

You are right, creating a view will catch a misspelled getter name but not a misspelled parameter in a generic getter.

> compile-/parse-time safety guarantees no dangling constant references at object definition time. I.e. you will catch that in your DEV environment before it ever gets near production.

A SQL statement is not an "object" that can be defined. When an object such as a view is created, it is persisted. A SQL statement that is parsed goes into the shared pool (in memory) as a cursor; when the instance goes away, the shared pool goes away. I imagine that generally a SQL statement would be parsed at least once "before it ever gets near production"?

> I don't see a reason not to use SQL macros as they promise better performance than functions. They just don't appear to offer anything over functions in terms of code maintainability, which is the OP's main concern here.

True, since SQL macros are functions. The OP's main concern was to avoid hard-coding literals in several places, and SQL macros do help with that.
Chris Saxon
June 11, 2020 - 3:17 pm UTC

I imagine that generally a SQL statement would be parsed at least once "before it ever gets near production"?

I imagine that too Stew, sadly reality lets me down on this one occasionally!

Scalar macros in 19.9

Rajeshwaran, Jeyabal, December 21, 2020 - 10:59 am UTC

Team:

waat told that SQL macros got back ported to 19.8 - but the below example was not working - Kindly advice.

https://connor-mcdonald.com/2020/09/14/when-can-i-use-sql-macros/

demo@PDB1> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------------
-----------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0


demo@PDB1> create or replace function foo( x int,y int)
  2  return varchar2 sql_macro(scalar)
  3  as
  4  begin
  5  return q'#  least(x,y) #';
  6  end;
  7  /

Warning: Function created with compilation errors.

demo@PDB1> show err
Errors for FUNCTION FOO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/26     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         ; is default authid as cluster order using external
         deterministic parallel_enable pipelined aggregate
         result_cache accessible rewrite

demo@PDB1> create or replace function foo( x int,y int)
  2  return varchar2 sql_macro
  3  as
  4  begin
  5  return q'#  least(x,y) #';
  6  end;
  7  /

Function created.

demo@PDB1> select foo(sal,comm) from scott.emp where rownum <=5;
select foo(sal,comm) from scott.emp where rownum <=5
       *
ERROR at line 1:
ORA-64629: table SQL macro can only appear in FROM clause of a SQL statement


demo@PDB1>

Connor McDonald
December 22, 2020 - 7:13 am UTC

*TABLE* macros not *SCALAR* macros are backported

SQL macros and the original question

Stew Ashton, January 06, 2021 - 5:34 pm UTC

Getting back to the original question: "Is it possible to return a named constant in a SQL query? " and to the idea of using scalar SQL macros in version 21c and beyond:

Chris already showed above how to write a specific getter function for a specific package constant. The "get" would occur at parse time, and during execution the constant would be used just as a literal would.

I tried to write a generic getter function.
- The first thing I learned is that I needed one function per data type, since overloading works only if the "data type family" is different.
- The second thing I learned is that the only way to get the name of the package constant at parse time is to disguise it as a column name.
- Third, once I got the constant name in my code (as a variable!), I had to use EXECUTE IMMEDIATE to obtain the value. This adds some overhead, but only at parse time.

create or replace function const_char(
  p_in in dbms_tf.columns_t
) return varchar2 sql_macro(scalar) is
  l_out varchar2(128);
begin    
  execute immediate 'begin :x := '||trim('"' from p_in(1))||'; end;' using out l_out;
  return ''''||l_out||'''';
end;
/
select const_char(columns("SYS.DBMS_COMPARISON.CMP_SCAN_MODE_CYCLIC")) scan_mode from dual;

SCAN_M
------
CYCLIC
Note that the name of the package constant is checked for validity at parse time (or compile time if you prefer): in case of an error, the message "PLS-00302: component '%s' must be declared" comes back.

The serious drawback of this approach is that cursors using this generic function are not invalidated if the value of the constant changes! In other words, the macro is not really deterministic.

So, specific getters such as Chris showed look safe to me, but generic getters? Not so much...

Happy New Year to all,
Stew
Chris Saxon
January 07, 2021 - 8:40 am UTC

Thanks for sharing your findings Stew

More to Explore

DBMS_SESSION

More on PL/SQL routine DBMS_SESSION here