1 package body ALR_PROFILE as
2 /* $Header: ALPROFLB.pls 115.1 99/07/16 19:01:22 porting ship $ */
3
4 type VAL_TAB_TYPE is table of varchar2(2000) index by binary_integer;
5 type NAME_TAB_TYPE is table of varchar2(80) index by binary_integer;
6
7 /*
8 ** define the internal table that will cache the profile values
9 ** val_tab(x) is associated with name_tab(x)
10 */
11 VAL_TAB VAL_TAB_TYPE; /* the table of values */
12 NAME_TAB NAME_TAB_TYPE; /* the table of names */
13 TABLE_SIZE binary_integer := 0; /* the size of above tables*/
14
15
16 /*
17 ** FIND - find index of an option name
18 **
19 ** RETURNS
20 ** table index if found, TABLE_SIZE if not found.
21 */
22 function FIND(NAME in varchar2) return binary_integer is
23 TAB_INDEX binary_integer;
24 FOUND boolean;
25 begin
26 TAB_INDEX := 0;
27 FOUND := false;
28
29 while (TAB_INDEX < TABLE_SIZE) and (not FOUND) loop
30 if NAME_TAB(TAB_INDEX) = NAME then
31 FOUND := true;
32 else
33 TAB_INDEX := TAB_INDEX + 1;
34 end if;
35 end loop;
36
37 return TAB_INDEX;
38 end;
39
40
41 /*
42 ** GET_DB - Get profile value from database
43 */
44 procedure GET_DB(NAME_Z in varchar2,
45 VAL_Z out varchar2,
46 DEFINED_Z out boolean) is
47
48 cursor C1 is
49 select profile_option_value
50 from alr_profile_options
51 where profile_option_name = NAME_Z;
52
53 begin
54
55 /* fetch the profile values on out of the database */
56 open C1;
57 fetch C1 into VAL_Z;
58
59 if (C1%NOTFOUND) then
60 VAL_Z := NULL;
61 DEFINED_Z := FALSE;
62 else
63 DEFINED_Z := TRUE;
64 end if;
65
66 close C1;
67
68 end GET_DB;
69
70
71
72 /*
73 ** PUT - Update or Insert a profile option value
74 */
75 procedure PUT(NAME in varchar2, VAL in varchar2) is
76 TABLE_INDEX binary_integer;
77 begin
78 /*
79 ** search for the option name
80 */
81 TABLE_INDEX := FIND(NAME);
82
83 if TABLE_INDEX < TABLE_SIZE then
84 /*
85 ** if found, set the value
86 */
87 VAL_TAB(TABLE_INDEX) := VAL;
88 else
89 /*
90 ** if not found, create a new option
91 */
92 VAL_TAB(TABLE_SIZE) := VAL;
93 NAME_TAB(TABLE_SIZE) := NAME;
94 TABLE_SIZE := TABLE_SIZE + 1;
95 end if;
96
97 exception
98 when others then
99 null;
100 end;
101
102 /*
103 ** GET - get the value of a profile option
104 **
105 ** NOTES
106 ** If the option cannot be found, the out buffer is set to NULL.
107 ** Use the DEFINED function to check if a profile option exists.
108 */
109 procedure GET(NAME in varchar2, VAL out varchar2) is
110 TABLE_INDEX binary_integer;
111 DEFINED boolean;
112 OUTVAL varchar2(2000);
113 begin
114 /*
115 ** search for the option
116 */
117 TABLE_INDEX := FIND(NAME);
118
119 if TABLE_INDEX < TABLE_SIZE then
120 VAL := VAL_TAB(TABLE_INDEX);
121 else
122 /* Can't find profile in the cached table; look in the db */
123 GET_DB(NAME, OUTVAL, DEFINED);
124 if (defined) then
125 /* put the value into the table */
126 VAL_TAB(TABLE_SIZE) := OUTVAL;
127 NAME_TAB(TABLE_SIZE) := NAME;
128 TABLE_SIZE := TABLE_SIZE + 1;
129 VAL := OUTVAL;
130 else
131 VAL := null;
132 end if;
133 end if;
134
135 exception
136 when others then
137 null;
138 end;
139
140
141
142 /*
143 ** value - get profile value, return as function value
144 */
145 function VALUE(NAME in varchar2) return varchar2 is
146 RETVALUE varchar2(2000);
147 begin
148 GET(NAME, RETVALUE);
149 return (RETVALUE);
150 end;
151
152
153 begin
154 /*
155 ** initialization section
156 */
157 TABLE_SIZE := 0;
158
159 end ALR_PROFILE;