Make public package variables available to SQL

Chris Hunt

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

TPD-Opitz

Currently there is some Kind of inconsistency when embedding SQL in a PL/SQL package:

create package Body usage_test is

procedure workung_example is

  v_pi number := 3.14;

  v_test number;

begin

  select v_pi into v_test from dual; -- works

end;

procedure failing exapmle is

  v_test number;

begin

  select test_pkg.v_pi into v_test from dual; -- raises ORA-06553: PLS-221

end;

end;

bye

TPD

William Robertson

Of course I can't say how big a change this would be for Oracle, but I suspect it could be a significant amount of work.

In PL/SQL, the compiler takes references to PL/SQL variables within SQL statements and builds a version of the SQL with a numbered bind variable and the associated code for binding. For example if a package contains

    where empno = v_empno

then the compiler generates  something like

    WHERE EMPNO = :B1

plus the code to declare :B1 and do the runtime binding etc.

Now if we want that to work outside PL/SQL there is no compiler to do all of that for us, so the SQL parser would have to manage it somehow, perhaps in a similar way to its other internal transformations. I'm not sure where it would declare the bind variable though, or how practical it would be to add an internal bind phase. Probably it's possible, but I just think that maybe it is not the trivial change you think it should be.

TPD-Opitz

Of course I can't say how big a change this would be for Oracle, but I suspect it could be a significant amount of work.

In PL/SQL, the compiler takes references to PL/SQL variables within SQL statements and builds a version of the SQL with a numbered bind variable and the associated code for binding. For example if a package contains

    where empno = v_empno

then the compiler generates  something like

    WHERE EMPNO = :B1

plus the code to declare :B1 and do the runtime binding etc.

Now if we want that to work outside PL/SQL there is no compiler to do all of that for us, so the SQL parser would have to manage it somehow, perhaps in a similar way to its other internal transformations. I'm not sure where it would declare the bind variable though, or how practical it would be to add an internal bind phase. Probably it's possible, but I just think that maybe it is not the trivial change you think it should be.

William Robertson wrote:
Now if we want that to work outside PL/SQL there is no compiler to do all of that for us, so the SQL parser would have to manage it somehow, perhaps in a similar way to its other internal transformations. I'm not sure where it would declare the bind variable though, or how practical it would be to add an internal bind phase. Probably it's possible, but I just think that maybe it is not the trivial change you think it should be.

I'd say it should be as hard as using public package functions in pure SQL like this:

select my_package.my_public_function() from dual where 1 = my_package.my_other_function();

bye

TPD

William Robertson

William Robertson wrote:
Now if we want that to work outside PL/SQL there is no compiler to do all of that for us, so the SQL parser would have to manage it somehow, perhaps in a similar way to its other internal transformations. I'm not sure where it would declare the bind variable though, or how practical it would be to add an internal bind phase. Probably it's possible, but I just think that maybe it is not the trivial change you think it should be.

I'd say it should be as hard as using public package functions in pure SQL like this:

select my_package.my_public_function() from dual where 1 = my_package.my_other_function();

bye

TPD

Right, so we want the SQL parser to generate a temporary PL/SQL wrapper function, transform the query to use that instead of the bind, and somehow square things with the optimizer?

TPD-Opitz

Right, so we want the SQL parser to generate a temporary PL/SQL wrapper function, transform the query to use that instead of the bind, and somehow square things with the optimizer?

William Robertson wrote:

Right, so we want the SQL parser to generate a temporary PL/SQL wrapper function, transform the query to use that instead of the bind, and somehow square things with the optimizer?

My thoughts where much simpler: what is the fundamental difference in the implementation between

my_package.my_public_function

and

my_package.my_public_variable_or_constant

within a SQL statement?

bye

TPD

William Robertson

William Robertson wrote:

Right, so we want the SQL parser to generate a temporary PL/SQL wrapper function, transform the query to use that instead of the bind, and somehow square things with the optimizer?

My thoughts where much simpler: what is the fundamental difference in the implementation between

my_package.my_public_function

and

my_package.my_public_variable_or_constant

within a SQL statement?

bye

TPD

The stuff I said?

Sven W.

Of course I can't say how big a change this would be for Oracle, but I suspect it could be a significant amount of work.

In PL/SQL, the compiler takes references to PL/SQL variables within SQL statements and builds a version of the SQL with a numbered bind variable and the associated code for binding. For example if a package contains

    where empno = v_empno

then the compiler generates  something like

    WHERE EMPNO = :B1

plus the code to declare :B1 and do the runtime binding etc.

Now if we want that to work outside PL/SQL there is no compiler to do all of that for us, so the SQL parser would have to manage it somehow, perhaps in a similar way to its other internal transformations. I'm not sure where it would declare the bind variable though, or how practical it would be to add an internal bind phase. Probably it's possible, but I just think that maybe it is not the trivial change you think it should be.

William Robertson wrote:

Of course I can't say how big a change this would be for Oracle, but I suspect it could be a significant amount of work.
 ...

I don't care how difficult it is to implement. Of cause performance should not suffer.

I feel this idea is pretty useful. In many projects I've seen versions of some "constant" package along with some kind of "getter functions" just to retrieve the constant values. This could be streamlined considerably. On the other hand a parameter table would solve the same problem nicely too.

TPD-Opitz

William Robertson wrote:

Of course I can't say how big a change this would be for Oracle, but I suspect it could be a significant amount of work.
 ...

I don't care how difficult it is to implement. Of cause performance should not suffer.

I feel this idea is pretty useful. In many projects I've seen versions of some "constant" package along with some kind of "getter functions" just to retrieve the constant values. This could be streamlined considerably. On the other hand a parameter table would solve the same problem nicely too.

You mention the performance aspect.

I'd suspect that having to fetch the value of package constant or variable once while hard parsing the SQL statement first time it occurs (most likely within the same stored package) should have less performance loss than having an additional join in the SQL, eve if this "configuration table" is in the session cache.

Of cause the impact depends on how often this particular SQL is used.

But I'm a Java guy and not so deep in the database technology...

bye

TPD

William Robertson

William Robertson wrote:

Of course I can't say how big a change this would be for Oracle, but I suspect it could be a significant amount of work.
 ...

I don't care how difficult it is to implement. Of cause performance should not suffer.

I feel this idea is pretty useful. In many projects I've seen versions of some "constant" package along with some kind of "getter functions" just to retrieve the constant values. This could be streamlined considerably. On the other hand a parameter table would solve the same problem nicely too.

Sven W. wrote:

I don't care how difficult it is to implement.  

I'd like to see this too. My point was just that it might not have been implemented because it is architecturally complex, rather than Oracle just not happening to think of it. But sure, we can put a man on the moon, so why not...

Maybe all it would take would be to have the PL/SQL compiler generate a hidden getter function for any public package variable, that is only visible to the SQL engine. Who knows. Someone should ask Bryn.

Rafiq D

Currently there is some Kind of inconsistency when embedding SQL in a PL/SQL package:

create package Body usage_test is

procedure workung_example is

  v_pi number := 3.14;

  v_test number;

begin

  select v_pi into v_test from dual; -- works

end;

procedure failing exapmle is

  v_test number;

begin

  select test_pkg.v_pi into v_test from dual; -- raises ORA-06553: PLS-221

end;

end;

bye

TPD

Note, I have edited my original post but all quotes from me below were in the original post and are valid:

TPD, the v_pi called in the first procedure is not the package variable. It's the local one declared in procedure workung_example, that is why it did not return any error.