Home » SQL & PL/SQL » SQL & PL/SQL » Creating Dynamic Variables (XP)
Creating Dynamic Variables [message #346984] |
Wed, 10 September 2008 05:43  |
RhysGM
Messages: 3 Registered: September 2008
|
Junior Member |
|
|
Hello I nicked this code off someone else in this forum and I understand it's been covered a few times before but I can't get my code working. My first language is SAS and not Oracle SQL so I'm having some issues, can anyone please help (by the way I do not understand any of whats written below except for the SQL bit at the bottom;
create
context zone_context
using pkg_zone_context
accessed globally
/
create
package pkg_zone_context
is
function get_context_name return number;
procedure set_zone_0 (zone_0_p);
procedure set_zone_1 (zone_1_p);
procedure set_zone_2 (zone_2_p);
procedure set_zone_3 (zone_3_p);
procedure set_zone_4 (zone_4_p);
procedure set_zone_5 (zone_5_p);
procedure set_zone_6 (zone_6_p);
procedure set_zone_7 (zone_7_p);
end;
/
show errors
create
package body pkg_zone_context
is
context_name_gv number := 'ZONE_CONTEXT';
procedure zone_0 (zone_0_p) is
begin
dbms_session.set_context(
context_name_gv
, 'ZONE_0'
, zone_0_p
);
end;
procedure zone_1 (zone_1_p) is
begin
dbms_session.set_context(
context_name_gv
, 'ZONE_1'
, zone_1_p
);
end;
procedure zone_2 (zone_2_p) is
begin
dbms_session.set_context(
context_name_gv
, 'ZONE_2'
, zone_2_p
);
end;
procedure zone_3 (zone_3_p) is
begin
dbms_session.set_context(
context_name_gv
, 'ZONE_3'
, zone_3_p
);
end;
procedure zone_4 (zone_4_p) is
begin
dbms_session.set_context(
context_name_gv
, 'ZONE_4'
, zone_4_p
);
end;
procedure zone_5 (zone_5_p) is
begin
dbms_session.set_context(
context_name_gv
, 'ZONE_5'
, zone_5_p
);
end;
procedure zone_6 (zone_6_p) is
begin
dbms_session.set_context(
context_name_gv
, 'ZONE_6'
, zone_6_p
);
end;
procedure zone_7 (zone_7_p) is
begin
dbms_session.set_context(
context_name_gv
, 'ZONE_7'
, zone_7_p
);
end;
end;
/
show errors
create view vw_test as
select case
when bal >= sys_context('ZONE_CONTEXT','ZONE_7') then sys_context('ZONE_CONTEXT','ZONE_7')
when bal >= sys_context('ZONE_CONTEXT','ZONE_6') then sys_context('ZONE_CONTEXT','ZONE_6')
when bal >= sys_context('ZONE_CONTEXT','ZONE_5') then sys_context('ZONE_CONTEXT','ZONE_5')
when bal >= sys_context('ZONE_CONTEXT','ZONE_4') then sys_context('ZONE_CONTEXT','ZONE_4')
when bal >= sys_context('ZONE_CONTEXT','ZONE_3') then sys_context('ZONE_CONTEXT','ZONE_3')
when bal >= sys_context('ZONE_CONTEXT','ZONE_2') then sys_context('ZONE_CONTEXT','ZONE_2')
when bal >= sys_context('ZONE_CONTEXT','ZONE_1') then sys_context('ZONE_CONTEXT','ZONE_1')
when bal >= sys_context('ZONE_CONTEXT','ZONE_0') then sys_context('ZONE_CONTEXT','ZONE_0')
else 0 end as band
from amounts
/
exec pkg_zone_context.set_zone_0(250);
exec pkg_zone_context.set_zone_1(750);
exec pkg_zone_context.set_zone_2(1500);
exec pkg_zone_context.set_zone_3(2500);
exec pkg_zone_context.set_zone_4(5000);
exec pkg_zone_context.set_zone_5(6500);
exec pkg_zone_context.set_zone_6(8000);
exec pkg_zone_context.set_zone_7(10000);
select *
from vw_test
|
|
|
|
Re: Creating Dynamic Variables [message #347022 is a reply to message #347011] |
Wed, 10 September 2008 07:53   |
RhysGM
Messages: 3 Registered: September 2008
|
Junior Member |
|
|
Appologies for before, I don't know what version of Oracle I'm using all I know is it's 10g?!
I get an ORA-00932 error when I try to run the create view bit.
What I am trying to do is create a dynamic variable that I will send from Excel VBA (not sure how yet) which a series of views is based on. I do not want to create the views everytime using VBA as there are 100's of instances of this variable.
If I was coding in somethinig else I would define a variable then store a value in it, so I can call it else where in the code. This is what I am trying to achieve.
create or replace
context zone_context
using pkg_zone_context
accessed globally
/
create
package pkg_zone_context
is
function get_context_name return number;
procedure set_zone_0 (zone_0_p);
procedure set_zone_7 (zone_7_p);
end;
/
show errors
create
package body pkg_zone_context
is
context_name_gv number := 'ZONE_CONTEXT';
procedure zone_0 (zone_0_p) is
begin
dbms_session.set_context(
context_name_gv
, 'ZONE_0'
, zone_0_p
);
end;
procedure zone_7 (zone_7_p) is
begin
dbms_session.set_context(
context_name_gv
, 'ZONE_7'
, zone_7_p
);
end;
end;
/
show errors
create view vw_test as
select case
when bal >= sys_context('ZONE_CONTEXT','ZONE_7') then sys_context('ZONE_CONTEXT','ZONE_7')
when bal >= sys_context('ZONE_CONTEXT','ZONE_0') then sys_context('ZONE_CONTEXT','ZONE_0')
else 0 end as band
from tb_amount
/
exec pkg_zone_context.set_zone_0(250);
exec pkg_zone_context.set_zone_7(1000);
select *
from vw_test
|
|
|
|
|
|
Re: Creating Dynamic Variables [message #347049 is a reply to message #347022] |
Wed, 10 September 2008 09:21   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Are you aware of the fact that you can only use this view in a session after you defined the bind-variables?
In other words, you will have to call the package to define the context for each and every session prior to using the view.
|
|
|
|
Re: Creating Dynamic Variables [message #347081 is a reply to message #347049] |
Wed, 10 September 2008 10:37   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Frank wrote on Wed, 10 September 2008 16:21 | Are you aware of the fact that you can only use this view in a session after you defined the bind-variables?
In other words, you will have to call the package to define the context for each and every session prior to using the view.
|
This is not true as this is a global context:
SQL> create
2 context zone_context
3 using pkg_zone_context
4 accessed globally
5 /
Context created.
SQL> create or replace
2 package pkg_zone_context
3 is
4 procedure set_zone_0 (zone_0_p varchar2);
5 end;
6 /
Package created.
SQL> create or replace
2 package body pkg_zone_context
3 is
4 context_name_gv varchar2(20) := 'ZONE_CONTEXT';
5 procedure set_zone_0 (zone_0_p varchar2) is
6 begin
7 dbms_session.set_context(
8 context_name_gv
9 , 'ZONE_0'
10 , zone_0_p
11 );
12 end;
13 end;
14 /
Package body created.
SQL> exec pkg_zone_context.set_zone_0(250);
PL/SQL procedure successfully completed.
SQL> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------
150
1 row selected.
SQL> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------------------
159
1 row selected.
SQL> select value from global_context where namespace='ZONE_CONTEXT' and attribute='ZONE_0';
VALUE
--------------------------------------------------------------------------------------------
250
1 row selected.
It just needs a "master" session.
Regards
Michel
|
|
|
Re: Creating Dynamic Variables [message #347097 is a reply to message #347081] |
Wed, 10 September 2008 12:31   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
You are right.
Still the restriction remains, albeit in a lesser way: like you also state, there must always be a session in which the variables were set.
Introducing a new culprit (or perhaps beneficial feature): changing values in one session will influence others.
|
|
|
Re: Creating Dynamic Variables [message #347107 is a reply to message #347097] |
Wed, 10 September 2008 13:45  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
The "master" session can be a startup trigger if values are static or a call for application server during its initialization if it is an applicatioh specific context.
Also you can prevent from others setting the context in the package. For instance, the package can check if there is a value yet or it can check for anything like a secret passphrase, IP address, username... anything you can also do for a secure application role.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Tue Feb 11 03:43:51 CST 2025
|