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?
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
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
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.
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;
create or replace package body MY_CONST is
c_bus_rule_violation constant varchar2(255) := 'Business rule violation: ';
function bus_rule_violation return varchar2 is
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
Waiting CONSTANT NUMBER := 1;
Ready CONSTANT NUMBER := 2;
-- more stuff here
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
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:
for x in ( select * from t where status = const_states.waiting )
so, in views, use literals (they are your packages).
A reader, June 03, 2020 - 7:35 am UTC
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.
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.
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.
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
select pkg_constant('utl_raw.big_endian') big_endian
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.
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.)
Al, June 07, 2020 - 3:27 pm UTC
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.
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;
create or replace function f
return varchar2 sql_macro ( scalar ) as
select f from dual;
Al, June 07, 2020 - 3:34 pm UTC
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?
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
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.
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. :-)
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
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.
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
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;
Oracle Database 19c Enterprise Edition Release 22.214.171.124.0 - Production
demo@PDB1> create or replace function foo( x int,y int)
2 return varchar2 sql_macro(scalar)
5 return q'# least(x,y) #';
Warning: Function created with compilation errors.
demo@PDB1> show err
Errors for FUNCTION FOO:
2/26 PLS-00103: Encountered the symbol "(" when expecting one of the
; 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
5 return q'# least(x,y) #';
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
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
execute immediate 'begin :x := '||trim('"' from p_in(1))||'; end;' using out l_out;
select const_char(columns("SYS.DBMS_COMPARISON.CMP_SCAN_MODE_CYCLIC")) scan_mode from dual;
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,
January 07, 2021 - 8:40 am UTC
Thanks for sharing your findings Stew