read package level variables from sql

Nimish Garg

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.

top.gun

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

Nimish Garg

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

Duplicate of

William Robertson

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

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

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

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

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

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

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.