SQL> start agway
SQL> drop package agway;
Package dropped.
SQL> /
drop package agway
*
ERROR at line 1:
ORA-04043: object AGWAY does not exist
SQL> create or replace package agway is
2
2 procedure search;
3 procedure search(typein varchar2,op integer,fid varchar2);
4 procedure search(op integer,fid varchar2);
5
5 procedure search_button(op integer,fid varchar2);
6 procedure by_pca(fid varchar2,p varchar2);
7 procedure by_pca(fid varchar2);
8 procedure keyword(p varchar2,fid varchar2,op integer);
9 procedure notyet;
10 procedure list(p varchar2,c varchar2, n varchar2, m varchar2,pr varchar2,d varchar2,t varchar2);
11
11 function tr(name varchar2) return varchar2;
12 function th(name varchar2) return varchar2;
13 function td(name varchar2) return varchar2;
14
14 html constant varchar2(60) := '/agway/';
15 images constant varchar2(60) := html||'images/';
16 cgi_bin constant varchar2(60) := '/cgi-bin/agway/wow/';
17 username constant varchar2(10) := 'agway';
18 password constant varchar2(10) := 'demo';
19 -- client varchar2(30) := owa.remote_addr;
20 time_out number(8,5) := 5./1440.;
21
21 help_page constant varchar2(100) := htf.url('http://naos.npac.syr.edu:1963/demo/help.html','Help');
22
22 end;
23 /
Package created.
SQL> show errors;
No errors.
SQL>
SQL> create or replace package body agway is
2 total_visitors number(7);
3 total number(8);
4 hh_time date;
5
5 two prod%ROWTYPE;
6 h_time varchar2(50) := ' ';
7 h_desc varchar2(2000);
8 h_caption varchar2(600);
9
9 -- for date
10 TYPE wordstype IS TABLE OF varchar2(50) INDEX BY BINARY_INTEGER;
11 word_tab wordstype;
12 w_count BINARY_INTEGER;
13 date_tag char(1);
14
14 function td(name varchar2) return varchar2 is
15 begin
16 return ('
'||name||'
');
17 end;
18
18 function th(name varchar2) return varchar2 is
19 begin
20 return ('
'||name||'
');
21 end;
22
22 function tr(name varchar2) return varchar2 is
23 begin
24 return ('
'||name||'
');
25 end;
26
26 procedure search_button(op integer,fid varchar2) is
27 cursor cfolder is select distinct category from prod order by category;
28 begin
29 htp.p('');
30
30 htp.p('
By: ');
39 htp.formSelectOpen('op');
40 if (op = 0) then
41 htp.p('
');
72 htp.p('
Category:');
73 htp.p('
');
88 htp.p(htf.formClose);
89 htp.p('
');
90
90 end;
91
91 procedure search is
92 begin
93 --search(6,'0');
94 search_button(6,'0');
95 end;
96
96 procedure search(op integer,fid varchar2) is
97 begin
98 -- search_button(op,fid);
99 search(NULL,op,fid);
100 end;
101
101 procedure search(typein varchar2,op integer,fid varchar2) is
102 begin
103 search_button(op,fid);
104 if ((op = 6 ) and fid='0') then
105 by_pca(fid);
106 return;
107 end if;
108 if (op=6) then
109 by_pca(fid,NULL);
110 return;
111 end if;
112 if (op=1) then
113 by_pca(fid,lower(typein));
114 return;
115 end if;
116
116 if (op=0 or op=3 or op=4 or op=5) then
117 keyword(lower(typein),fid,op);
118 return;
119 end if;
120
120 notyet;
121
121 wow.sig;
122 end;
123
123
123
123 procedure keyword(p varchar2,fid varchar2,op integer) is
124 cursor cuser(p varchar2) is select * from prod where
125 (lower(description) like p) or
126 (lower(category) like p) or
127 (lower(name) like p) or
128 (lower(manufacturer) like p)
129 order by part_no asc, category asc;
130
130 cursor cuser1(fid varchar2,p varchar2) is select * from prod where
131 (category=fid) and (
132 (lower(description) like p) or
133 (lower(name) like p) or
134 (lower(manufacturer) like p))
135 order by part_no asc,category asc;
136
136 cursor cuser3(p varchar2) is select * from prod where
137 (lower(name) like p)
138 order by part_no asc, category asc;
139
139 cursor cuser31(fid varchar2,p varchar2) is select * from prod where
140 (category=fid) and
141 (lower(name) like p)
142 order by part_no asc,category asc;
143
143 cursor cuser4(p varchar2) is select * from prod where
144 (lower(manufacturer) like p)
145 order by part_no asc, category asc;
146
146 cursor cuser41(fid varchar2,p varchar2) is select * from prod where
147 (category=fid) and
148 (lower(manufacturer) like p)
149 order by part_no asc,category asc;
150
150 cursor c1(n number) is select * from prod where price >= n
151 order by part_no asc,category asc;
152
152 cursor c2(n number) is select * from prod where price <= n
153 order by part_no asc,category asc;
154
154 cursor c3(n number) is select * from prod where price = n
155 order by part_no asc,category asc;
156
156 cursor c11(n number,fid varchar2) is select * from prod where (category=fid) and price >= n
157 order by part_no asc,category asc;
158
158 cursor c22(n number,fid varchar2) is select * from prod where (category=fid) and price <= n
159 order by part_no asc,category asc;
160
160 cursor c33(n number,fid varchar2) is select * from prod where (category=fid) and price = n
161 order by part_no asc,category asc;
162
162 n prod.price%TYPE;
163
163
163 begin
164 htp.p('');
165 htp.p('
');
166
166 if fid = '0' then
167 if (op = 0) then
168 open cuser('%'||lower(p)||'%');
169 h_caption := 'Agway Products with Keyword '||htf.bold(p);
170 elsif (op=3) then
171 open cuser3('%'||lower(p)||'%');
172 h_caption := 'Agway Products with Keyword '||htf.bold(p)||' in Product Names';
173 elsif (op=4) then
174 open cuser4('%'||lower(p)||'%');
175 h_caption := 'Agway Products with Keyword '||htf.bold(p)||' in Manufactures';
176 else
177 if (p like '%>%') then
178 n := replace(p,'>');
179 n := replace(p,'=');
180 open c1(n);
181 h_caption := 'Agway Products with Price >= '||htf.bold(n);
182 elsif (p like '%<%') then
183 n := replace(p,'<');
184 n := replace(p,'=');
185 open c2(n);
186 h_caption := 'Agway Products with Price <= '||htf.bold(n);
187 else
188 n := replace(p,'=');
189 open c3(n);
190 h_caption := 'Agway Products with Price = '||htf.bold(n);
191 end if;
192 end if;
193 else
194 if (op = 0) then
195 open cuser1(fid,'%'||lower(p)||'%');
196 h_caption := 'Agway Products in Category '||htf.bold(fid)||' with Keyword '||htf.bold(p);
197 elsif (op=3) then
198 open cuser31(fid,'%'||lower(p)||'%');
199 h_caption := 'Agway Products in Category '||htf.bold(fid)||' with Keyword '||htf.bold(p)||
200 ' in Product Names';
201 elsif (op=4) then
202 open cuser41(fid,'%'||lower(p)||'%');
203 h_caption := 'Agway Products in Category '||htf.bold(fid)||' with Keyword '||htf.bold(p)||
204 ' in Manufactures';
205 else
206 if (p like '%>%') then
207 n := replace(p,'>');
208 n := replace(p,'=');
209 open c11(n,fid);
210 h_caption := 'Agway Products in Category '||htf.bold(fid)||
211 ' with Price >= '||htf.bold(n);
212 elsif (p like '%<%') then
213 n := replace(p,'<');
214 n := replace(p,'=');
215 open c22(n,fid);
216 h_caption := 'Agway Products in Category '||htf.bold(fid)||
217 ' with Price <= '||htf.bold(n);
218 else
219 n := replace(p,'=');
220 open c33(n,fid);
221 h_caption := 'Agway Products in Category '||htf.bold(fid)||
222 ' with Price = '||htf.bold(n);
223 end if;
224 end if;
225
225 end if;
226 htp.p('
');
227 htp.p(h_caption||'
');
228 htp.p(tr(th('Part No.')||th('Category')||th('Product Name')||th('Manufacturer')||th('Price')||
229 th('Description')||th('Tips')));
230 loop
231 if fid = '0' then
232 if (op = 0) then
233 fetch cuser into two;
234 exit when cuser%NOTFOUND;
235 elsif (op=3) then
236 fetch cuser3 into two;
237 exit when cuser3%NOTFOUND;
238 elsif (op=4) then
239 fetch cuser4 into two;
240 exit when cuser4%NOTFOUND;
241 else
242 if (p like '%>%') then
243 fetch c1 into two;
244 exit when c1%NOTFOUND;
245 elsif (p like '%<%') then
246 fetch c2 into two;
247 exit when c2%NOTFOUND;
248 elsif (p like '%=%') then
249 fetch c3 into two;
250 exit when c3%NOTFOUND;
251 end if;
252 end if;
253 else
254 if (op = 0) then
255 fetch cuser1 into two;
256 exit when cuser1%NOTFOUND;
257 elsif (op=3) then
258 fetch cuser31 into two;
259 exit when cuser31%NOTFOUND;
260 elsif (op = 4) then
261 fetch cuser41 into two;
262 exit when cuser41%NOTFOUND;
263 else
264 if (p like '%>%') then
265 fetch c1 into two;
266 exit when c1%NOTFOUND;
267 elsif (p like '%<%') then
268 fetch c2 into two;
269 exit when c2%NOTFOUND;
270 elsif (p like '%=%') then
271 fetch c3 into two;
272 exit when c3%NOTFOUND;
273 end if;
274 end if;
275 end if;
276 list(htf.url(images||two.image,two.part_no),
277 two.category,two.name,two.manufacturer,two.price,
278 two.description,htf.url(html||two.tips,'How to use '||two.name));
279 end loop;
280
280
280 htp.p('
');
281 htp.p('
');
282 wow.sig;
283 end;
284
284 procedure by_pca(fid varchar2) is
285 begin by_pca(fid,NULL); end;
286
286 procedure by_pca(fid varchar2,p varchar2) is
287 cursor cuser(fid varchar2) is select * from prod where
288 (category=fid) order by part_no asc,category asc;
289
289 cursor cuser1(fid varchar2,p varchar2) is select * from prod where
290 (category=fid) and (lower(part_no) like '%'||p||'%') order by part_no asc,category asc;
291
291 cursor cuser2(p varchar2) is select * from prod where
292 (lower(part_no) like '%'||p||'%') order by part_no asc,category asc;
293
293 cursor cuser3 is select * from prod
294 order by part_no asc,category asc;
295 tag integer := 0;
296
296 begin
297 -- search_button(1,fid);
298 if p IS NULL then
299 if (fid = '0') then
300 open cuser3;
301 tag := 3;
302 h_caption := 'List of All Agway Products';
303 else
304 open cuser(fid);
305 tag := 0;
306 h_caption := 'List of All Agway Products in Category '||htf.bold(fid);
307 end if;
308 elsif (fid = '0') then
309 open cuser2(p);
310 tag := 2;
311 h_caption := 'List of All Agway Products with Product No. contains '||htf.bold(p);
312 else
313 open cuser1(fid,p);
314 tag := 1;
315 h_caption := 'List of All Agway Products with Product No. contains '||
316 htf.bold(p)||' in Category '||htf.bold(fid);
317 end if;
318
318 htp.p('');
319 htp.p('
');
320 htp.p('
'||h_caption||'
');
321 htp.p(tr(th('Part No.')||th('Category')||th('Product Name')||th('Manufacturer')||th('Price')||
322 th('Description')||th('Tips')));
323
323 loop
324 if tag=0 then
325 fetch cuser into two;
326 exit when cuser%NOTFOUND;
327 elsif tag=2 then
328 fetch cuser2 into two;
329 exit when cuser2%NOTFOUND;
330 elsif tag=1 then
331 fetch cuser1 into two;
332 exit when cuser1%NOTFOUND;
333 else
334 fetch cuser3 into two;
335 exit when cuser3%NOTFOUND;
336 end if;
337
337 if (two.description IS NULL) then
338 h_desc := ' ';
339 else
340 h_desc := two.description;
341 end if;
342 list(htf.url(images||two.image,two.part_no),
343 two.category,two.name,two.manufacturer,two.price,
344 two.description,htf.url(html||two.tips,'How to use '||two.name));
345 end loop;
346
346 if tag=0 then
347 close cuser;
348 elsif tag=2 then
349 close cuser2;
350 elsif tag=1 then
351 close cuser1;
352 else
353 close cuser3;
354 end if;
355
355
355 htp.p('
');
356 htp.p('
');
357 wow.sig;
358 end;
359
359 procedure list(p varchar2,c varchar2, n varchar2, m varchar2,pr varchar2,d varchar2,t varchar2) is
360
360 begin
361 htp.p('
');
365 end;
366
366 procedure notyet is
367 begin
368 --search_button(6,0);
369 htp.p('
Sorry, this function has not implemented yet in the demo. Stay tuned!');
370 end;
371
371 end agway;
372 /
Package body created.
SQL> show errors;
No errors.
SQL> /
Package body created.
SQL> spool off
'));
34 htp.p('Find Agway Products: ');
35 htp.p(' ');
36 htp.p(' ');
37 htp.p(help_page);
38 htp.p('