Forum Stats

  • 3,826,625 Users
  • 2,260,682 Discussions
  • 7,897,041 Comments

Discussions

Make public package variables available to SQL

Chris Hunt
Chris Hunt Member Posts: 2,066 Gold Trophy
edited Jun 8, 2018 6:13AM 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 ErlingerulohmannNimish GargApexBineuser7904656Jeffrey Kempuser7048955FatMartinRuser6192574Kevan GellinggkbTexasApexDevelopersensoftAparna Dutta-Oraclejnicholas330jbosmantonibony7Christian Neumueller-OracleGunther PippèrrdherzhauNiels HeckerberxMarwimThorsten KettnerJustin WarwickgassenmjSpike HouseJeroen.Scott WesleyKateMPerthJon TheriaultChristina Brashear-OracleMike KutzgaverillEmad Al-MousaEvandro Lima-OraclePeter HraškoOren NakdimonPiotr WrzosekpchircopDenis SavenkoPhilipp SalvisbergJorge Rimblasuser6206865Gerald Venzl-OracleSebastianKoellBilly Verreynnetry-itmathguyRogerTErik van RoonAndreas GroeneveltsdstuberMarkStewartjbbarretoUser_WWF93User_R1YILUser_7USOQcommi235User_Q5R4A
84 votes

Under Review - Voting Still Open · Last Updated

«13

Comments

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy

    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
    William Robertson Member Posts: 9,567 Bronze Crown
    edited Nov 11, 2015 10:38AM

    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
    TPD-Opitz Member Posts: 2,465 Silver Trophy

    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 Member Posts: 9,567 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-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy

    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

    MarkStewart
  • William Robertson
    William Robertson Member Posts: 9,567 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. Member Posts: 10,535 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-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy
    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 Member Posts: 9,567 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 D
    Rafiq D Member Posts: 57 Blue Ribbon
    edited Dec 10, 2015 10:44AM

    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.