Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
Make public package variables available to SQL

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
-
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
-
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.
-
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 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?
-
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 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?
-
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.
-
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 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.
-
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.