![]() ![]() Session variables - Exist for one client or browser during a single session.Client variables - Tied to a single client over multiple sessions.Application variables - Tied to a single application and accessible by multiple clients.Server variables - Accessible by all clients and applications on a single.If you know that the variable ( foo) may change within a single query then remove parallel_enable, which should otherwise be more performant if queries are parallelizable.ĭepending on ones needs one could of course add some foo_reset() to set it to null etc.A session variable is one of several types of variables that persist across multiple templates: (same test comments like in foo above skipped for brevity)įunction foo( set varchar2 := null ) return varchar2 parallel_enable as beginįunction bar( set number := null ) return number parallel_enable as begin If val is not null then ref := val end if įunction set_if_not_null( ref in out number, val number ) return number as begin Package body: create or replace package body ctx asįunction set_if_not_null( ref in out varchar2, val varchar2 ) return varchar2 as (samples like in foo above as executable test comments like in foo above skipped forįunction bar( set number := null ) return number parallel_enable parallel_enable for queries since it should not change inside of themįunction foo( set varchar2 := null ) return varchar2 parallel_enable select 'ups' from (select ctx.foo('a') from dual) select ctx.foo from dual (optimizer should cause the subquerys unselected columns not to be executed:) select ctx.foo('b') from dual select ctx.foo from dual - => 'b', 'b' select ctx.foo from dual - => null (init) Package header: create or replace package ctx as Select ctx.bar(1.5) from dual select ctx.bar from dual - => 1.5, 1.5 Select 'ups' from (select ctx.foo('a') from dual) select ctx.foo from dual - => null ![]() Select ctx.foo('b') from dual select ctx.foo from dual - => 'b', 'b' Usage: select ctx.foo from dual - => null (init) (valid for the current session only in my case no need to implement it as user-created-context variables, but easily changeable to it under the hood example with some foo varchar2 and bar number vars) some ctx package that just provides one function to set and get some global "variables". ![]() ![]() I like to use a short, but intuitive syntax and thus would create e.g. WHERE ROLE_ID = SYS_CONTEXT(PKG_COMMON.FCN_GET_CONTEXT_NAME, 'MyVariable') PKG_COMMON.prc_set_context_value('MyVariable', 9000)Īnd use it anywhere - any procedure, package, function or event a view. Set a context variable using a procedure call begin With the context and the package created you can start using them. The prc_set_context_value can be more advanced, that's just an example. PROCEDURE prc_set_context_value (var_name VARCHAR2, var_value NUMBER)ĭBMS_t_context (common_ctx_name, var_name, var_value) PROCEDURE prc_set_context_value (var_name VARCHAR2, var_value NUMBER) ĬREATE OR REPLACE PACKAGE BODY PKG_COMMON := SYS_CONTEXT ('userenv', 'current_schema')įUNCTION fcn_get_context_name RETURN VARCHAR2 Second, create a package that would manage your context: CREATE OR REPLACE PACKAGE PKG_COMMON You can use a 'user-created context' to store data shared across multiple units within a session.įirst, create a context: CREATE CONTEXT SYS_CONTEXT ('userenv', 'current_schema')|| '_ctx' USING PKG_COMMON ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |