Make public package variables available to SQL — oracle-tech

    Forum Stats

  • 3,707,910 Users
  • 2,240,909 Discussions
  • 7,839,920 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Make public package variables available to SQL

Chris HuntChris Hunt Posts: 2,066
edited June 2018 in Database Ideas - Ideas

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

Chris HuntctriebLothar FlatzManish ChaturvediZlatko SiroticMortenBratenabhinivesh.jainKiran PawarJitendraTPD-Opitzfac586borneselInoLFranck PachotPatrick Wolf-Oracletop.gunGeert GruwezJagadekaravinaykumar2KayKitshakChristian ErlingerulohmannSven W.Nimish GargApexBineuser7904656Jeffrey Kempuser7048955FatMartinRuser6192574Kevan GellinggkbTexasApexDevelopersensoftAparna Dutta-Oraclejnicholas330jbosmantonibony7Christian Neumueller-OracleGunther PippèrrdherzhauNiels HeckerberxMarwimThorsten KettnerJustin WarwickgassenmjSpike HouseJeroen GraafmansScott WesleyKateMPerthJon TheriaultChristina Brashear-OracleMike KutzgaverillEmad Al-MousaEvandro Lima-OraclePeter HraškoOren NakdimonPiotr WrzosekpchircopDenis SavenkoErik van RoonPhilipp SalvisbergRogerTJorge Rimblasuser6206865Gerald Venzl-OracleSebastianKoellBilly Verreynnetry-itmathguy
76 votes

Under Review - Voting Still Open · Last Updated

Comments

  • TPD-OpitzTPD-Opitz Posts: 2,465

    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 RobertsonWilliam Robertson Posts: 9,560 Bronze Crown
    edited November 2015

    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-OpitzTPD-Opitz Posts: 2,465

    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 RobertsonWilliam Robertson Posts: 9,560 Bronze Crown
    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-OpitzTPD-Opitz Posts: 2,465

    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 RobertsonWilliam Robertson Posts: 9,560 Bronze Crown
    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.Sven W. Posts: 10,489 Gold Crown

    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.

    ApexBine
  • TPD-OpitzTPD-Opitz Posts: 2,465
    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 RobertsonWilliam Robertson Posts: 9,560 Bronze Crown
    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 DRafiq D Posts: 50
    edited December 2015

    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.

  • TPD-OpitzTPD-Opitz Posts: 2,465
    edited December 2015

    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.

    Rafiq D wrote:
    
    TPD, v_pi is not a package variable. It's local to procedure workung_example so it cannot be accessed outside of that procedure.
    
    

    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

  • Chris HuntChris Hunt Posts: 2,066

    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.

    It's not 'proper' and in fact, defeats encapsulation, to allow direct access to package variables.
    

    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.

    Sven W.Justin Warwick
  • It's not 'proper' and in fact, defeats encapsulation, to allow direct access to package variables.
    

    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.

  • Nimish GargNimish Garg Posts: 3,185

    I wish that oracle provides this feature in future release. Voted

  • rvstuckervstucke Posts: 11

    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;

  • top.guntop.gun Posts: 3,666 Gold Crown

    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;

    William Robertson
  • William RobertsonWilliam Robertson Posts: 9,560 Bronze Crown

    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:

    with
        function business_date return date as begin return mypackage.g_busdate; end business_date;
    select *
    from   sales s
    where  s.somedate = business_date;
    
  • TPD-OpitzTPD-Opitz Posts: 2,465

    That's not a bad idea. I don't have 12c to test on, but presumably you could use package global variables like this:

    with
        function business_date return date as begin return mypackage.g_busdate; end business_date;
    select *
    from   sales s
    where  s.somedate = business_date;
    

    IMHO this does not improve readability which would be my main goal...

    bye

    TPD

    ApexBine
  • tonibony7tonibony7 Posts: 14 Blue Ribbon
    edited September 2016

    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.

    TPD-Opitz
  • bargenbargen Posts: 9 Blue Ribbon

    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.

    Sven W.Jon Theriaulttry-it
  • Justin WarwickJustin Warwick Posts: 103 Bronze Badge

    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.

    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.

    mathguy
  • Scott WesleyScott Wesley Posts: 5,840 Gold Crown
    edited May 2018

    IMHO this does not improve readability which would be my main goal...

    bye

    TPD

    In regard to readability, you could convert to using UDF pragma?

    create or replace package test_pkg  as  pi number := 3.14159;  function f_pi return varchar2 ;end;/create or replace package body test_pkg  as  function f_pi return varchar2  is pragma udf;  begin   return pi;  end;end;/select test_pkg.f_pi from dual;

    Though that also seems to be a little rough around the edges

    https://mwidlake.wordpress.com/2015/11/11/pragma-udf-some-current-limitations/

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

    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. 

    try-it
  • Stew AshtonStew Ashton Posts: 2,849 Gold Trophy

    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:

    create function clip(lo number, x number, hi number) return varchar2 SQL_MACRO(SCALAR)is begin    return 'least(greatest(x, lo), hi)';end;/SELECT  ename, CLIP (:lower, sal, :upper)FROM emp;

    "Macro expansion rewrites SQL to..."

    SELECT  ename, least(greatest(sal, :lower), :upper)FROM emp;

    I imagine the following would work:

    create or replace package test_pkg  as  pi number := 3.14159;   function f_pi return varchar2 SQL_MACRO(SCALAR);end;/create or replace package body test_pkg  as  function f_pi return varchar2 SQL_MACRO(SCALAR) is  begin   return pi||null;  end;end;/select test_pkg.f_pi from dual;

    Macro expansion would rewrite the query as:

    select 3.14159 from dual;

    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

    William RobertsonPhilipp Salvisberg
  • Stew AshtonStew Ashton Posts: 2,849 Gold Trophy

    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:

    create function clip(lo number, x number, hi number) return varchar2 SQL_MACRO(SCALAR)is begin    return 'least(greatest(x, lo), hi)';end;/SELECT  ename, CLIP (:lower, sal, :upper)FROM emp;

    "Macro expansion rewrites SQL to..."

    SELECT  ename, least(greatest(sal, :lower), :upper)FROM emp;

    I imagine the following would work:

    create or replace package test_pkg  as  pi number := 3.14159;   function f_pi return varchar2 SQL_MACRO(SCALAR);end;/create or replace package body test_pkg  as  function f_pi return varchar2 SQL_MACRO(SCALAR) is  begin   return pi||null;  end;end;/select test_pkg.f_pi from dual;

    Macro expansion would rewrite the query as:

    select 3.14159 from dual;

    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

    berx
  • try-ittry-it Posts: 16 Blue Ribbon

    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

  • Stew AshtonStew Ashton Posts: 2,849 Gold Trophy

    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

  • Stew AshtonStew Ashton Posts: 2,849 Gold Trophy

    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.

    Sven W.
  • Stew AshtonStew Ashton Posts: 2,849 Gold Trophy

    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.

Sign In or Register to comment.