vdw/trunk/stored_procs.txt


/* ==================================================================
    Web Warehouse listener on real DB - will start OS server process
   ================================================================== */
create or replace
package wou_obj_srvr_lsnr as

    procedure p_connect ( pp_uid       varchar2,
                          pp_passwd    varchar2,
                          pp_host      varchar2,
                          pp_port      integer );
end wou_obj_srvr_lsnr;
/
show errors

create public synonym wou_obj_srvr_lsnr for wou_obj_srvr_lsnr;
grant execute on wou_obj_srvr_lsnr to public;

create or replace
package body wou_obj_srvr_lsnr as

/* package-level private functions */
function f_valid_user(fp_uid       varchar2,
                      fp_passwd    varchar2)
return boolean is

begin

    if upper(fp_uid) = 'SOME_USER' and  /* put real user */
       upper(fp_passwd) = 'SECRET_PASSWORD'  /* put real password,
       or better, store encrypted in DB */
    then
        return TRUE;
    end if;

    /* fall-through */
    return FALSE;

end f_valid_user;


procedure p_connect ( pp_uid       varchar2,
                      pp_passwd    varchar2,
                      pp_host      varchar2,
                      pp_port      integer ) as

    retval    number;

BEGIN

    if f_valid_user(pp_uid, pp_passwd) then

      retval := wou_utl.f_gurjobsSubmit('GJAJOBS obj_srvr P
obj_srvr_start_user secret_password 0000 '
          || pp_host || ' ' || pp_port);

    end if;

END p_connect;

END wou_obj_srvr_lsnr;
/
show errors


/* =========================================================
    Utility that starts OS process from DB using DBMS_PIPE 
    (depends on OS listener process called GURJOBS - an
    SCT Banner program).  If you don't use SCT Banner, you  
    need write an OS listener process for DBMS_PIPE.  Or 
    you could create an Oracle external library that has a
    wrapper for the C "system" command, and call that
    passing the warehouse server executable as an argument.
   =========================================================
create or replace package WOU_UTL as

    function F_gurjobsSubmit(fp_cmd_str varchar2) return number;

end WOU_UTL;
/
show errors
exit


create or replace package body WOU_UTL as

    function F_gurjobsSubmit(fp_cmd_str varchar2)
    return number is

        retval                      integer;

    begin

        dbms_pipe.pack_message('HOST');
        dbms_pipe.pack_message(fp_cmd_str);
        dbms_pipe.pack_message(dbms_pipe.unique_session_name);

        retval := dbms_pipe.send_message('GURJOBS');

        if retval != 0 then
            return 1;
        end if;

        retval := dbms_pipe.receive_message(dbms_pipe.unique_session_name);

        if retval != 0 then
            return 1;
        end if;

        return 0;
    end F_gurjobsSubmit;

end WOU_UTL;
/
show errors
exit


/* ==============================================================
    Warehouse User Validation - put valid users in table zobjsrv 
    (they must also be valid DB users).
   ==============================================================
create table zobjsrv ( username   varchar2(30) );

create or replace function chk_obj_srvr_user (fp_username    varchar2)
return varchar2 as

    cursor obj_srvr_user_c is
    select 1 from zobjsrv
    where username = upper(fp_username);

    junk    varchar2(30);

begin

   open obj_srvr_user_c;
   fetch obj_srvr_user_c into junk;
   if obj_srvr_user_c%found then
       return 'VALID_OBJ_SRVR_USER';
   else
       return 'NOT_AN_OBJ_SRVR_USER';
   end if;
end;
/
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	/