- 3,707,910 Users
- 2,240,909 Discussions
- 7,839,920 Comments
Forum Stats
Discussions
Categories
- 17 Industry Applications
- 3.2K Intelligent Advisor
- 3 Insurance
- 689 On-Premises Infrastructure
- 230 Analytics Software
- 19 Application Development Software
- 1.7K Cloud Platform
- 700.4K Database Software
- 17.4K Enterprise Manager
- 4 Hardware
- 96 Infrastructure Software
- 62 Integration
- 44 Security Software
Make public package variables available to SQL
Chris Hunt
Posts: 2,066
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
then the compiler generates something like
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.
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?
My thoughts where much simpler: what is the fundamental difference in the implementation between
my_package.my_public_functionand
my_package.my_public_variable_or_constantwithin a SQL statement?
bye
TPD
The stuff I said?
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
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.
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.
You are right.
But the error raises is the same when you move v_pi to the package declaration (of a different package):
create package usage_test is
v_pi constant number(1,2) := 3.14
end usage_test;
select usage_test.v_pi from dual; -- fails
bye
TPD
I disagree. Allowing access to a public package variable is no less encapsulated than allowing access to a public function. And what is the point of setting up a function purely to retrieve the value of a constant? Maybe C# and Java insist on working that way (I neither know nor care), but so what?
Here's a use case. I have a package that defines certain useful numbers as constants in the package spec. These constants are used within SQL statements in other packages within the system, where, of course, they work because they're running in a pl/sql block. But if I'm trying to track down a bug, I might want to cut & paste a SQL statement from within a package and run it to see what happens - but it will choke because the PL/SQL constants can't be evaluated in a plain SQL statement.
I also don't buy the supposed technical difficulty of this. The SQL engine is perfectly capable of identifying and dealing with a PL/SQL object within a query - provided that that object is a function. It just grabs the value returned by the function and feeds it in to the query. I'm sure it takes a great deal of clever programming under the hood to do that, but I don't see why it should be any more difficult to do exactly the same thing with a variable.
Chris, a getter function can do more than just retrieving and returning a value. It can do more processing such as logging the accessor before it sends back the retrieved value. I still think a getter is the best as demonstrated in TPD's code.
I wish that oracle provides this feature in future release. Voted
We have been utilizing a package to hold session variables for years. We use them as bind variables inside a view. The session sets the values of the variables in the package, which are visible in the package header. Then the function called associated to the variable is put in the view as a retrieval of a constant literal value. This allows multiple sessions to run the exact same view for different values.
CREATE OR REPLACE PACKAGE PROD.Dyn_Global AS
client_id NUMBER :=0;
program_id NUMBER :=0;
program_year number :=0;
from_date DATE := TO_DATE('30000101','YYYYMMDD');
thru_date DATE := TO_DATE('30000101','YYYYMMDD');
FUNCTION dyn_client
RETURN NUMBER;
FUNCTION dyn_program
RETURN NUMBER;
FUNCTION dyn_program_year
RETURN NUMBER;
FUNCTION dyn_from_date
RETURN DATE;
FUNCTION dyn_thru_date
RETURN DATE;
PROCEDURE setprogramyear (
iprogramid IN INTEGER,
iprogramyear IN INTEGER);
PROCEDURE setclientprogram (
iclientid IN INTEGER,
iprogramid IN INTEGER);
PROCEDURE setfromthrudates (
frm_date IN DATE,
thr_date IN DATE);
PRAGMA RESTRICT_REFERENCES(dyn_client,wnds,rnds,wnps);
PRAGMA RESTRICT_REFERENCES(dyn_program,wnds,rnds,wnps);
PRAGMA RESTRICT_REFERENCES(dyn_program_year,wnds,rnds,wnps);
PRAGMA RESTRICT_REFERENCES(dyn_from_date,wnds,rnds,wnps);
PRAGMA RESTRICT_REFERENCES(dyn_thru_date,wnds,rnds,wnps);
PRAGMA RESTRICT_REFERENCES(setprogramyear, wnds, rnds);
PRAGMA RESTRICT_REFERENCES(setclientprogram, wnds, rnds);
PRAGMA RESTRICT_REFERENCES(setfromthrudates, wnds, rnds);
PRAGMA RESTRICT_REFERENCES(Dyn_Global,wnds,rnds);
END Dyn_Global;
/
CREATE OR REPLACE PACKAGE BODY PROD.Dyn_Global AS
FUNCTION dyn_client RETURN NUMBER AS
BEGIN
RETURN client_id;
END;
FUNCTION dyn_program RETURN NUMBER AS
BEGIN
RETURN program_id;
END;
FUNCTION dyn_program_year RETURN NUMBER AS
BEGIN
RETURN program_year;
END;
FUNCTION dyn_from_date RETURN DATE AS
BEGIN
RETURN from_date;
END;
FUNCTION dyn_thru_date RETURN DATE AS
BEGIN
RETURN thru_date;
END;
PROCEDURE setprogramyear (
iprogramid IN INTEGER,
iprogramyear IN INTEGER) AS
BEGIN
prod.Dyn_Global.program_id := iprogramid;
prod.Dyn_Global.program_year := iprogramyear;
END;
PROCEDURE setclientprogram (
iclientid IN INTEGER,
iprogramid IN INTEGER) AS
BEGIN
prod.Dyn_Global.client_id := iclientid;
prod.Dyn_Global.program_id := iprogramid;
END;
PROCEDURE setfromthrudates (
frm_date IN DATE,
thr_date IN DATE) AS
BEGIN
prod.Dyn_Global.from_date := frm_date;
prod.Dyn_Global.thru_date := thr_date;
END;
END Dyn_Global;
/
CREATE OR REPLACE VIEW TEST_VIEW
AS
SELECT CLIENT_ID, PROGRAM_ID, COUNT(*) ENROLLMENT_COUNT
FROM ENROLLMENT_TABLE
WHERE CLIENT_ID = DYN_GLOBAL.DYN_CLIENT
AND PROGRAM_ID = DYN_GLOBAL.DYN_PROGRAM
AND ENROLLMENT_DATE BETWEEN DYN_GLOBAL.DYN_FROM_DATE AND DYN_GLOBAL.DYN_THRU_DATE
GROUP BY CLIENT_ID, PROGRAM_ID;
In 12c there is a new feature like this:
Using a PL/SQL Function in the WITH Clause
The plsql_declarations clause lets you declare and define PL/SQL functions and procedures. You can then reference the PL/SQL functions in the query in which you specify this clause, as well as its subqueries, if any.
WITH
FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS
pos BINARY_INTEGER;
len BINARY_INTEGER;
BEGIN
pos := INSTR(url, 'www.');
len := INSTR(SUBSTR(url, pos + 4), '.') - 1;
RETURN SUBSTR(url, pos + 4, len);
END;
SELECT DISTINCT get_domain(catalog_url)
FROM product_information;
That's not a bad idea. I don't have 12c to test on, but presumably you could use package global variables like this:
IMHO this does not improve readability which would be my main goal...
bye
TPD
Note that the Oracle optimizer treats very differently bind variables and functions. In many cases it does matter whether you use "where column = some_variable" or "where column = getter_function()". Even when the function has the "deterministic" attribute and proper "restrict_references" pragma. For example, when we define views, we create application context variables and use them with SYS_CONTEXT (these are treated more like bind variables). We do this just to workaround the suboptimal execution plans caused by getter functions.
So, the requested feature will be most useful if it does not "generate a temporary wrapper function", but use bind variables (similarly to the way the SQL embedded in PL/SQL is handled).
Voted up.
We often have CONSTANTs which need to be accessed from outside.
I don't care about variables, but the ability to access public package constants from SQL would help.
For example, today we have a public VERSION function in each and every package which simple returns a constant.
But what if you do not want to "do more processing"? What if you are trying to write fast, efficient SQL statements that will deal with large volumes of data and outcomes will be affected by those simple (mostly constant) values? Threshold/cutoff/tolerance, conversion factors, "thumbprints", and so much more. Also, this feature as proposed would of course not make it any more difficult for those that prefer getters to just keep on doing that.
In regard to readability, you could convert to using UDF pragma?
Though that also seems to be a little rough around the edges
https://mwidlake.wordpress.com/2015/11/11/pragma-udf-some-current-limitations/
This would be very helpful and I'd imagine easier than variables. We have hardcoded strings everywhere in views but all PL/SQL refers back to the constants. If there's a constant Oracle could "copy" it at the time the view is compiled. If the package spec with the constant changes it would invalidate the view anyways so it would just "copy" it again when compiled. Keeping the real SQL text, with the constant reference, and the compiled version of the query with the variable replaced would be the tricky part.
Based strictly on Keith Laker's OOW presentation (no testing possible), it appears that Oracle 20c with SQL Macros will provide a robust workaround.
Keith's example of "scalar" macros is as follows:
"Macro expansion rewrites SQL to..."
I imagine the following would work:
Macro expansion would rewrite the query as:
Note that this rewrite would allow histograms to be used by the parser, unlike getter functions. For that reason, if Oracle were to automatically create "wrapper" functions as suggested above, those functions should be scalar SQL macros rather than getter functions.
Best regards,
Stew Ashton
I am clarifying this comment after it was mentioned in https://asktom.oracle.com/pls/apex/asktom.search?tag=returning-a-named-constant-in-a-sql-query
In reply to a question I asked on Twitter, Andrej Pashchenko explained more about how parsing works with SQL macros; see https://blog.sqlora.com/en/sql-macros-part-2-parameters-and-parsing/
Basically, a SQL Macro must be deterministic: calling it with the same input parameters must always produce the same output. This requirement allows the Optimizer to call the macro only when the SQL statement is parsed, not every time it is executed.
In my original comment, I defined a package variable called PI. I forgot to define it as a constant. This is crucial. To be deterministic, a SQL Macro must not reference any variable other than its own input variables. It can reference constants, since any change to a constant means a code change, which will invalidate any cursors that depend on the macro.
Bottom line: as I understand them, SQL Macros cannot be used to "make public package variables available to SQL". They can be used to make public package constants available to SQL.
If this interpretation is correct, starting with 20c we will still need to write getter functions to access public package variables and constants, but the getter functions for constants should be SQL Macros. Sorry for being overly optimistic...
Best regards,
Stew Ashton
What is the use case for accessing package variables in SQL?
If we restricted this idea to package constants, wouldn't 80% of the implementation concerns go away while 80% of the benefit would be achieved?
Al
I don't know about the percentages, but I agree there is a big difference.
Accessing PL/SQL requires a context switch, which is a very costly operation. For a public package constant, that context switch could be done at parse time only. Why? because any change to the constant would require compiling the package, which would invalidate any cursor that accesses the package, which in turn would cause the statement to be reparsed.
Accessing a package variable would have to be done at run time, possibly many times.
Beyond performance, we don't have full control over what the database does (and in what order) to provide the result of a query. Oracle may just rewrite the whole thing. Right now with functions, we don't always know when and how many times a function will be called. The same would be true of package variables.
It seems more reasonable to me, when sharing values between SQL and PL/SQL, to have some in-between area in memory where the value could be accessed efficiently from SQL and changed in a controlled manner by PL/SQL -- oh wait, Oracle already has that: application contexts!
Stew
I have not forgotten that this idea is about package variables, however many comments speak about package constants, and there is a reference to an enhancement request to
ENH 6525013 - ALLOW A PACKAGE CONSTANT IN SQL WHERE A PLACEHOLDER IS LEGAL.
Speaking of constants, there is a special treatment of literals in SQL Table Macros and polymorphic table functions: during parsing, the shape of the result set is determined based on input parameters, but only identifiers and literals are taken into account, not variables. It would be very important to give package constants the same treatment as literals, meaning the value of the package constant would be taken into account during parsing.
This is exactly what would happen if a SQL scalar macro accessed a package constant.
Returning to my comments on using SQL macros to access PL/SQL package constants or variables:
It looks like SQL scalar macros can be used to wrap package constants for use in SQL statements. An example:
create or replace package test_pkg as pi constant number := 3.14159; function f_pi return varchar2 SQL_MACRO(SCALAR); end test_pkg; / create or replace package body test_pkg as function f_pi return varchar2 SQL_MACRO(SCALAR) is begin return to_char(pi,'TM9','nls_numeric_characters=''.,'''); end; end test_pkg; / drop table t purge; create table t(n) as select 3.14159 from dual; select * from t where n = test_pkg.f_pi; select * from table(dbms_xplan.display_cursor(null,null,'+PREDICATE')); ... Predicate Information (identified by operation id): --------------------------------------------------- 1 - storage("N"=3.14159) filter("N"=3.14159)The execution plan uses the value of the constant directly, which would allow the use of histograms just as if it were a literal.
Such getter functions would be a pain to write and use, but they would get the job done.