Tags

,


/* Formatted on 05.05.2011 10:58:34 (QP5 v5.163.1008.3004) */
DECLARE
the_list VARCHAR2 (4000) := ‘ alter table user.tab drop part x’;
in_str VARCHAR (4000);

TYPE EventType IS TABLE OF VARCHAR2 (4000);

v_EventType EventType := EventType ();

TYPE nestedSql IS TABLE OF VARCHAR2 (4000);

v_nested_sql NestedSQL := NestedSQL ();

is_member BOOLEAN := FALSE;
v_cn number := 1;
no_valid_action EXCEPTION;
no_valid_schema EXCEPTION;

t_str varchar2(100);

v_user varchar2(30);
v_table varchar2(30);

BEGIN
v_EventType := EventType (‘ALTER’, ‘DROP’, ‘ DELETE’);

SELECT RTRIM (LTRIM (REGEXP_REPLACE (the_list, ‘( ){2,}’, ‘ ‘)))
INTO in_str
FROM DUAL;

SELECT count(SUBSTR (main_string,
position_from + 1,
position_to – position_from – 1))
into v_cn
FROM ( SELECT main_string,
DECODE (ROWNUM – 1,
0, 0,
INSTR (main_string,
‘,’,
1,
ROWNUM – 1))
position_from,
INSTR (main_string,
‘,’,
1,
ROWNUM)
position_to
FROM (SELECT REPLACE (UPPER (in_str), CHR (32), ‘,’)
main_string
FROM DUAL)
CONNECT BY LEVEL <= LENGTH (main_string))
WHERE position_to > 0;

FOR i
IN (SELECT SUBSTR (main_string,
position_from + 1,
position_to – position_from – 1)
s,
ROWNUM c
FROM ( SELECT main_string,
DECODE (ROWNUM – 1,
0, 0,
INSTR (main_string,
‘,’,
1,
ROWNUM – 1))
position_from,
INSTR (main_string,
‘,’,
1,
ROWNUM)
position_to
FROM (SELECT REPLACE (UPPER (in_str), CHR (32), ‘,’)
main_string
FROM DUAL)
CONNECT BY LEVEL <= LENGTH (main_string))
WHERE position_to > 0)
LOOP
DBMS_OUTPUT.PUT_LINE (i.s);

IF i.c = 1
THEN
is_member := i.s MEMBER OF v_EventType;

IF is_member
THEN
v_nested_sql.EXTEND (v_cn);
v_nested_sql (i.c) := i.s;
–DBMS_OUTPUT.PUT_LINE (i.c||’. element is ‘||
v_nested_sql (i.c) );
ELSE
RAISE no_valid_action;
END IF;
ELSE
v_nested_sql (i.c) := i.s;
–DBMS_OUTPUT.PUT_LINE (i.c||’. element is ‘|| v_nested_sql (i.c) );
END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE(chr(9));
DBMS_OUTPUT.NEW_LINE;

— 3. element is table
if v_nested_sql (1) =v_EventType(1) then
t_str := v_nested_sql (3) ;
DBMS_OUTPUT.PUT_LINE (‘ table is ‘|| t_str);
v_user:= substr(t_str,1,instr(t_str,’.’,1)-1);
DBMS_OUTPUT.PUT_LINE (‘ user is ‘|| v_user);
— hier kann die Berechtigung auf das Schema geprüft werden

— example
if v_user = ‘SYS’ then
raise no_valid_schema;
end if;

end if;

EXCEPTION
WHEN no_valid_action
THEN
RAISE_APPLICATION_ERROR (-20001, ‘no valid action’);
WHEN no_valid_schema
THEN
RAISE_APPLICATION_ERROR (-20002, ‘no valid schema’);
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/

Advertisements