I often want to use the constants defined in packages also in my SQL. It has in the past never worked and it for the most part still does not.
If I have a a package like this to simplify my code for what department it is I’m looking at.
create package dept_info as
c_accounting constant scott.dept.dname%type := 10;
c_research constant scott.dept.dname%type := 20;
c_sales constant scott.dept.dname%type := 30;
c_operations constant scott.dept.dname%type := 40;
end dept_info;
This now allows using dept_info.c_accounting in PL/SQL instead of just hardcoding 10 and having to remember what department 10 is. This is how we usually use PL/SQL and especially with Oracles packages where they often provide constants for ease-of-use.
However if I now try to use it in a SQL, it will not work.
select *
from scott.emp
where deptno = dept_info.c_accounting;
ORA-06553: PLS-221: 'C_ACCOUNTING' is not a procedure or is undefined
Instead of that descriptive reference to the accounting department I have to resort to this.
select *
from scott.emp
where deptno = 10;
Then the other day I realised that a feature that is way underused and typically used for allowing declaring programmatic logic in SQL can be used to get access to the constant in the SQL-statement. The with construct can nowadays define a function. That function can return a constant from a package.
with
function c_accounting return number is
begin
return dept_info.c_accounting;
end;
select *
from scott.emp
where deptno = c_accounting
/
Pretty nifty, eh? Notice that such SQL cannot be terminated with semicolon as semicolon is used to delimit statements in the PL/SQL so you have to terminate the SQL with a slash in SQL*Plus or SQLcl. Livesql on the other hand parses it just fine if it is terminated with a semicolon.
This is a feature I’m sure will soon be in many SQLs I write.