1 |
|
2 |
/* ================================================================== |
3 |
Web Warehouse listener on real DB - will start OS server process |
4 |
================================================================== */ |
5 |
create or replace |
6 |
package wou_obj_srvr_lsnr as |
7 |
|
8 |
procedure p_connect ( pp_uid varchar2, |
9 |
pp_passwd varchar2, |
10 |
pp_host varchar2, |
11 |
pp_port integer ); |
12 |
end wou_obj_srvr_lsnr; |
13 |
/ |
14 |
show errors |
15 |
|
16 |
create public synonym wou_obj_srvr_lsnr for wou_obj_srvr_lsnr; |
17 |
grant execute on wou_obj_srvr_lsnr to public; |
18 |
|
19 |
create or replace |
20 |
package body wou_obj_srvr_lsnr as |
21 |
|
22 |
/* package-level private functions */ |
23 |
function f_valid_user(fp_uid varchar2, |
24 |
fp_passwd varchar2) |
25 |
return boolean is |
26 |
|
27 |
begin |
28 |
|
29 |
if upper(fp_uid) = 'SOME_USER' and /* put real user */ |
30 |
upper(fp_passwd) = 'SECRET_PASSWORD' /* put real password, |
31 |
or better, store encrypted in DB */ |
32 |
then |
33 |
return TRUE; |
34 |
end if; |
35 |
|
36 |
/* fall-through */ |
37 |
return FALSE; |
38 |
|
39 |
end f_valid_user; |
40 |
|
41 |
|
42 |
procedure p_connect ( pp_uid varchar2, |
43 |
pp_passwd varchar2, |
44 |
pp_host varchar2, |
45 |
pp_port integer ) as |
46 |
|
47 |
retval number; |
48 |
|
49 |
BEGIN |
50 |
|
51 |
if f_valid_user(pp_uid, pp_passwd) then |
52 |
|
53 |
retval := wou_utl.f_gurjobsSubmit('GJAJOBS obj_srvr P |
54 |
obj_srvr_start_user secret_password 0000 ' |
55 |
|| pp_host || ' ' || pp_port); |
56 |
|
57 |
end if; |
58 |
|
59 |
END p_connect; |
60 |
|
61 |
END wou_obj_srvr_lsnr; |
62 |
/ |
63 |
show errors |
64 |
|
65 |
|
66 |
|
67 |
/* ========================================================= |
68 |
Utility that starts OS process from DB using DBMS_PIPE |
69 |
(depends on OS listener process called GURJOBS - an |
70 |
SCT Banner program). If you don't use SCT Banner, you |
71 |
need write an OS listener process for DBMS_PIPE. Or |
72 |
you could create an Oracle external library that has a |
73 |
wrapper for the C "system" command, and call that |
74 |
passing the warehouse server executable as an argument. |
75 |
========================================================= |
76 |
create or replace package WOU_UTL as |
77 |
|
78 |
function F_gurjobsSubmit(fp_cmd_str varchar2) return number; |
79 |
|
80 |
end WOU_UTL; |
81 |
/ |
82 |
show errors |
83 |
exit |
84 |
|
85 |
|
86 |
create or replace package body WOU_UTL as |
87 |
|
88 |
function F_gurjobsSubmit(fp_cmd_str varchar2) |
89 |
return number is |
90 |
|
91 |
retval integer; |
92 |
|
93 |
begin |
94 |
|
95 |
dbms_pipe.pack_message('HOST'); |
96 |
dbms_pipe.pack_message(fp_cmd_str); |
97 |
dbms_pipe.pack_message(dbms_pipe.unique_session_name); |
98 |
|
99 |
retval := dbms_pipe.send_message('GURJOBS'); |
100 |
|
101 |
if retval != 0 then |
102 |
return 1; |
103 |
end if; |
104 |
|
105 |
retval := dbms_pipe.receive_message(dbms_pipe.unique_session_name); |
106 |
|
107 |
if retval != 0 then |
108 |
return 1; |
109 |
end if; |
110 |
|
111 |
return 0; |
112 |
end F_gurjobsSubmit; |
113 |
|
114 |
end WOU_UTL; |
115 |
/ |
116 |
show errors |
117 |
exit |
118 |
|
119 |
|
120 |
/* ============================================================== |
121 |
Warehouse User Validation - put valid users in table zobjsrv |
122 |
(they must also be valid DB users). |
123 |
============================================================== |
124 |
create table zobjsrv ( username varchar2(30) ); |
125 |
|
126 |
create or replace function chk_obj_srvr_user (fp_username varchar2) |
127 |
return varchar2 as |
128 |
|
129 |
cursor obj_srvr_user_c is |
130 |
select 1 from zobjsrv |
131 |
where username = upper(fp_username); |
132 |
|
133 |
junk varchar2(30); |
134 |
|
135 |
begin |
136 |
|
137 |
open obj_srvr_user_c; |
138 |
fetch obj_srvr_user_c into junk; |
139 |
if obj_srvr_user_c%found then |
140 |
return 'VALID_OBJ_SRVR_USER'; |
141 |
else |
142 |
return 'NOT_AN_OBJ_SRVR_USER'; |
143 |
end if; |
144 |
end; |
145 |
/ |