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.