Skip navigation

Make public package variables available to SQL

score 580
You have not voted. Under Review - Voting Still Open

This annoys me:

 

SQL> create or replace package test_pkg as

 

   c_pi  constant number := 3.1415926;

 

   v_pi  number := 3.1415926;

 

   function f_pi return number;

 

end;

Package created.

 

SQL> create or replace package body test_pkg as

    function f_pi return number is

   begin

      return 3.1415926;

    end;

end;

Package body created.

 

SQL> select test_pkg.c_pi from dual

select test_pkg.c_pi from dual

       * Error at line 1 ORA-06553: PLS-221: 'C_PI' is not a procedure or is undefined

 

SQL> select test_pkg.v_pi from dual

select test_pkg.v_pi from dual

        * Error at line 1 ORA-06553: PLS-221: 'V_PI' is not a procedure or is undefined

 

SQL> select test_pkg.f_pi from dual

       F_PI

----------

3.1415926

 

1 row selected.

 

If it's possible to do a context switch and fetch the result of a packaged function from a package, why not a packaged variable? You can work around it (if you need to) by creating a function to simply return the value of fixed variables, but why not make the variables directly accessible?

 

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

Comments

Vote history