Home » SQL & PL/SQL » SQL & PL/SQL » Creating Dynamic Variables (XP)
Creating Dynamic Variables [message #346984] Wed, 10 September 2008 05:43 Go to next message
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 #347011 is a reply to message #346984] Wed, 10 September 2008 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

What are you trying to achieve or in other words why do you want to use this code?

Regards
Michel
Re: Creating Dynamic Variables [message #347022 is a reply to message #347011] Wed, 10 September 2008 07:53 Go to previous messageGo to next message
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 #347036 is a reply to message #347022] Wed, 10 September 2008 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SYS_CONTEXT returns a string, 0 is a number.

Also:
SQL> create
  2  package pkg_zone_context
  3  is
  4  
  5     function get_context_name return number;
  6     procedure set_zone_0 (zone_0_p);
  7     procedure set_zone_7 (zone_7_p);
  8  
  9  end;
 10  /

Warning: Package created with compilation errors.

Something is missing in parameter definition.

Regards
Michel
Re: Creating Dynamic Variables [message #347043 is a reply to message #347036] Wed, 10 September 2008 08:48 Go to previous messageGo to next message
RhysGM
Messages: 3
Registered: September 2008
Junior Member
How do I get sys_context to return a number?
Re: Creating Dynamic Variables [message #347044 is a reply to message #347043] Wed, 10 September 2008 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can't, you can use TO_NUMBER on SYS_CONTEXT returned value or change 0 to '0' depending on what you finally want, a number or a string.

Regards
Michel
Re: Creating Dynamic Variables [message #347049 is a reply to message #347022] Wed, 10 September 2008 09:21 Go to previous messageGo to next message
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 #347051 is a reply to message #346984] Wed, 10 September 2008 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
RhysGM,

What problem are your really trying to solve?
It almost appears you have a solution in search of a problem.

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above


> I do not want to create the views everytime using VBA as there are 100's of instances of this variable.
Specifically please explain why do you think any view is required.

[Updated on: Wed, 10 September 2008 10:05] by Moderator

Report message to a moderator

Re: Creating Dynamic Variables [message #347081 is a reply to message #347049] Wed, 10 September 2008 10:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Summing of record values based on certain condition
Next Topic: Table Name Identification
Goto Forum:
  


Current Time: Tue Feb 11 03:43:51 CST 2025