Forum Stats

  • 3,874,704 Users
  • 2,266,767 Discussions
  • 7,911,957 Comments

Discussions

read package level variables from sql

Nimish Garg
Nimish Garg Member Posts: 3,185 Gold Trophy
edited Jan 24, 2016 11:53AM in Database Ideas - Ideas

I think it would be helpful if we can read package level variables direct from sql. I mostly need to write functions in package instead of package level constants. If we can read package level variable directly from sql, it will save the time to create functions and make life little easy.

BPeaslandDBANimish GargStauderBoneistuser7904656trentsensoftjnicholas330user8910179sdstuber
11 votes

Active · Last Updated

«1

Comments

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

    Can you provide an example of what you're proposing?

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy

    Can you provide an example of what you're proposing?

    let say i have following package

    create package pkg_constants

    as

    g_const constant number := 1000;

    end;

    /

    i wish that we should be able to atleast read g_const  from sql. like

    select pkg_constants.g_const from dual

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    Just to bang on about architecture again - do you see this being implemented within the SQL or PL/SQL engine? What would it look like in the execution plan?

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy

    Just to bang on about architecture again - do you see this being implemented within the SQL or PL/SQL engine? What would it look like in the execution plan?

    I don't mind about the context switching. I just that it would be good if it can be added to provide simplicity. As oracle did in case of direct value assignment of sequence in plsql. No performance benefit just simplicity.

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited Jan 26, 2016 9:05AM

    I don't mind about the context switching. I just that it would be good if it can be added to provide simplicity. As oracle did in case of direct value assignment of sequence in plsql. No performance benefit just simplicity.

    I wasn't really thinking of context switching or performance.

    Edit:

    To clarify, I meant that the execution plan is one way to see how PL/SQL is a wrapper for SQL and not the other way around:

    declare
        v dual.dummy%type := 'X';
    begin
        select dummy into v from dual where dummy =v;     for r in (
            select *
            from  table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST -OUTLINE +NOTE')) p
        )
        loop
            dbms_output.put_line(r.plan_table_output);
        end loop;
    end;
    / SQL_ID  79qqw6zqtj0b9, child number 1
    -------------------------------------
    SELECT DUMMY FROM DUAL WHERE DUMMY =:B1
    Plan hash value: 272002086
    ------------------------------------------------------------------------------------
    | Id  | Operation        | Name | Starts | E-Rows | A-Rows |  A-Time  | Buffers |
    ------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |      2 |
    |*  1 |  TABLE ACCESS FULL| DUAL |      1 |      1 |      1 |00:00:00.01 |      2 |
    ------------------------------------------------------------------------------------ Predicate Information (identified by operation id):
    ---------------------------------------------------
    1 - filter("DUMMY"=:B1)

    The query that is sent to the SQL engine is the one at line 18. The SQL engine doesn't know anything about PL/SQL.

    Now if we want this kind of PL/SQL variable binding to take place without a PL/SQL compiler, how should it work?

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy

    may be same as it will work if we have a function for example

    create package pkg_constants
    as
    g_const constant number := 1000;
    
    function get_value
    return number;
    end;
    /
    
    
    create package body pkg_constants 
    as
    
    function get_value
    return number
    is
    begin
      return 1000;
    end;
    
    end;
    /
    
    
    SQL> select pkg_constants.get_value from dual;
    
    
    
    
    Execution Plan
    ----------------------------------------------------------
    
    
    -------------------------------------------------
    | Id  | Operation        | Name | Rows  | Cost  |
    -------------------------------------------------
    |   0 | SELECT STATEMENT |      |     1 |     2 |
    |   1 |  FAST DUAL       |      |     1 |     2 |
    -------------------------------------------------
    

    I just wish that if we can get the constant value as we can get the value from a function, it would be helpful

    select pkg_constants.g_const from dual;
    
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    may be same as it will work if we have a function for example

    create package pkg_constants
    as
    g_const constant number := 1000;
    
    function get_value
    return number;
    end;
    /
    
    
    create package body pkg_constants 
    as
    
    function get_value
    return number
    is
    begin
      return 1000;
    end;
    
    end;
    /
    
    
    SQL> select pkg_constants.get_value from dual;
    
    
    
    
    Execution Plan
    ----------------------------------------------------------
    
    
    -------------------------------------------------
    | Id  | Operation        | Name | Rows  | Cost  |
    -------------------------------------------------
    |   0 | SELECT STATEMENT |      |     1 |     2 |
    |   1 |  FAST DUAL       |      |     1 |     2 |
    -------------------------------------------------
    

    I just wish that if we can get the constant value as we can get the value from a function, it would be helpful

    select pkg_constants.g_const from dual;
    

    You are missing my point. I don't care about the execution plan! What is your example supposed to show?

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy

    I think there is a disconnect between our thoughts.

    I simply wish that SQL should be able to read the PLSQL variables. Like for my last example

    select pkg_constants.g_const from dual;
    

    output should be 1000.


    I am not sure how it is going to work internally. In my last example I tried to explain that it might work as same as it works in case or functions.

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited Jan 27, 2016 7:53AM

    I think there is a disconnect between our thoughts.

    I simply wish that SQL should be able to read the PLSQL variables. Like for my last example

    select pkg_constants.g_const from dual;
    

    output should be 1000.


    I am not sure how it is going to work internally. In my last example I tried to explain that it might work as same as it works in case or functions.

    I know what you are asking for. I'd like it too. I was just commenting that SQL does not currently know anything about PL/SQL and has no facilities for binding variables, because PL/SQL is the wrapper for SQL and not the other way around. SQL can use functions via a declared specification but it doesn't know what's inside them (they could be Java for all it knows). In your example the cursor submitted to the SQL engine is

    SELECT PKG_CONSTANTS.GET_VALUE FROM DUAL
    
    

    Now perhaps the PL/SQL compiler could implicitly generate a new type of invisible pragma UDF function for every package constant, with the same name as the constant, and then somehow know which to pick when referring to it from within PL/SQL using its overloading rules.

    Or, for

    select pkg_constants.g_const from dual;
    
    

    to get transformed internally to

    select :b1 from dual
    
    

    and passed the value of pkg_constants.g_const, then the SQL engine may need a whole PL/SQL parser that it doesn't currently have.

    Maybe this can be done. I'm just saying that it looks to me like a major architectural change for a minor benefit in coding convenience and this is probably why Oracle hasn't done it, however much we would like it to work the same as for functions.