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

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.

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.

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;

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

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

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

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

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

@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

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 

@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

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 WRT Enh 28147631

I guess I meant a pointer you can follow and then read the actual ER. :-)

@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.

>> 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.

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>

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