1 PACKAGE BODY OLAP_API_SESSION_INIT AS
2 /* Define local function, available only inside package. */
3 FUNCTION getRoleID(roleName VARCHAR2) RETURN INT IS
4 user_num INT;
5 BEGIN
6 SELECT USER#
7 INTO user_num
8 FROM SYS.USER$
9 WHERE TYPE# = 0
10 AND DEFROLE = 1
11 AND NAME = roleName;
12 RETURN user_num;
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 RAISE;
16 END getRoleId;
17
18 FUNCTION newRole(roleNum INT, roleName VARCHAR2) return INT IS
19 num INT;
20 BEGIN
21 SELECT COUNT(*)
22 INTO num
23 FROM SYS.OLAP$ALTER_SESSION
24 WHERE ROLE# = roleNum
25 AND TEXT = roleName;
26 RETURN num;
27 END newRole;
28
29 /* Fully define subprograms specified in package. */
30 PROCEDURE add_alter_session (roleName VARCHAR2, sqlStmt VARCHAR2) IS
31 role_num INT;
32 isNewRole INT;
33 alterStmt VARCHAR2(4000);
34 BEGIN
35 DECLARE
36 BEGIN
37 role_num := getRoleID(roleName);
38 alterStmt := 'ALTER SESSION ' || UPPER(sqlStmt);
39 isNewRole := newRole(role_num, alterStmt);
40 IF isNewRole = 0 THEN
41 INSERT INTO SYS.OLAP$ALTER_SESSION (ROLE#, TEXT) VALUES (role_num, alterStmt);
42 DBMS_OUTPUT.PUT_LINE('Row inserted');
43 COMMIT;
44 ELSE
45 RAISE duplicate_role;
46 END IF;
47 END;
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 RAISE_APPLICATION_ERROR(-20001, roleName || ' is not a valid role');
51 RAISE invalid_role;
52 WHEN duplicate_role THEN
53 RAISE_APPLICATION_ERROR(-20002,
54 'ALTER SESSION command already exists for ' || roleName);
55 RAISE duplicate_role;
56 WHEN OTHERS THEN
57 DBMS_OUTPUT.PUT_LINE('Unknown exception occured');
58 END add_alter_session;
59
60 PROCEDURE delete_alter_session (roleName VARCHAR2, sqlStmt VARCHAR2) IS
61 role_num INT;
62 alterStmt VARCHAR2(4000);
63 BEGIN
64 DECLARE
65 BEGIN
66 role_num := getRoleID(roleName);
67 alterStmt := 'ALTER SESSION ' || UPPER(sqlStmt);
68 DELETE FROM SYS.OLAP$ALTER_SESSION
69 WHERE ROLE# = role_num AND TEXT = alterStmt;
70 COMMIT;
71 END;
72 EXCEPTION
73 WHEN NO_DATA_FOUND THEN
74 RAISE_APPLICATION_ERROR(-20001, roleName || ' is not a valid role');
75 RAISE invalid_role;
76 WHEN OTHERS THEN
77 DBMS_OUTPUT.PUT_LINE('Unknown exception occured');
78 END delete_alter_session;
79
80 PROCEDURE clean_alter_session IS
81 BEGIN
82 DELETE FROM olap$alter_session where role# NOT IN (
83 SELECT user# FROM SYS.USER$ WHERE TYPE# = 0 AND DEFROLE = 1);
84 END clean_alter_session;
85
86 -- BEGIN -- initialization part starts here
87 END OLAP_API_SESSION_INIT;
[Home] [Help]