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.