Oracle PL/SQL SQL

Using package variables in SQL

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.

2 Comments

  1. Christian

    Hi Mathias!

    May you have an Real World use case for that?
    Sounds pretty interesting but I can‘t Imagine any use case at the moment.

    Many thanks
    Christian

    • Mathias

      Hi!

      Thank you for stopping by. When it is your own code you can of course set up the constants you want for codes you store, like being able to have a where to pull out critical warnings from your logtable by referencing pkg.c_critical.

      For Oracle supplied then Oracle AQ comes to mind. When you want to look at messages that has been processed it would be easier to understand the SQL if it referenced dbms_aq.processed for the state in the AQ-table. If the code says “where state = 2” you will not understand the meaning if you have not been working with AQ for a while.

      So yes, I think there are lots of uses. But we have been su used to not be able to do this that they do not come tom mind until we start thinking of how we can write better code with it.

      Mathias

Leave a Comment

Your email address will not be published. Required fields are marked *

*