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	dpavlin	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			/