실전오라클, 튜닝, 모델링, SQL, PLSQL
인기강좌 --> SQL초보에서전문가까지, 오라클초보에서전문가까지, Unix&Pro*C
www.oraclejava3.co.kr 오라클 강좌에서 확인하세요.
CREATE OR REPLACE PROCEDURE LIST_TRIG_ERR(p_Trigger IN VARCHAR2) AS
v_Trig LONG;
b_Continue BOOLEAN := True;
v_NumLines NUMBER := 0;
v_Line VARCHAR2(240);
v_NxtChr NUMBER := 0;
v_LstChr NUMBER := 0;
TYPE LineTabTyp IS TABLE OF VARCHAR2(240)
INDEX by BINARY_INTEGER;
t_Lines LineTabTyp;
CURSOR err_cur IS SELECT line, text
FROM User_Errors
WHERE name = p_Trigger
AND text not like '%Statement ignored%';
i NUMBER;
v_Prefix CHAR(10);
v_DDLCursor NUMBER;
v_DDLReturn NUMBER;
v_statemet CHAR(240);
compilation_error EXCEPTION;
PRAGMA EXCEPTION_INIT(compilation_error, -24344);
BEGIN
--
-- Fetch the trigger code into a variable that will be used to parse it.
--
BEGIN
SELECT trigger_body INTO v_Trig
FROM User_Triggers
WHERE Trigger_Name = p_Trigger;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Raise_Application_Error(-20001,'Trigger does not exist: '||p_Trigger);
END;
--
-- Use DBMS_SQL to execute the command that places the errors into USER_ERRORS
BEGIN
v_DDLCursor := dbms_sql.open_cursor;
DBMS_SQL.Parse(v_DDLCursor,'ALTER TRIGGER '||p_Trigger||' COMPILE DEBUG',1);
v_DDLReturn := dbms_sql.execute(v_DDLCursor);
DBMS_SQL.Close_Cursor(v_DDLCursor);
-- In Oracle 8i and above the preceding code can be replaced with the following:
-- v_statatement := 'ALTER TRIGGER '||p_Trigger||' COMPILE DEBUG';
-- execute immediate v_statement;
EXCEPTION
WHEN compilation_error THEN
null;
END;
--
-- Now loop through the lines in the trigger code and parse it into
-- a separate record in a PL/SQL table.
--
WHILE b_Continue LOOP
v_NumLines := v_NumLines + 1;
v_NxtChr := INSTR(v_Trig, CHR(10),1,v_NumLines);
v_Line := SUBSTR(v_Trig, v_LstChr+1, (v_NxtChr-v_LstChr));
t_Lines(v_NumLines) := v_Line;
IF v_NxtChr = 0 THEN
b_Continue := False;
ELSE
v_LstChr := v_NxtChr;
END IF;
END LOOP;
--
-- Loop through all of the errors in USER_ERRORS for this trigger,
-- displaying each error, followed by the triggers code, with
-- an ===> (arrow) pointing to the error line.
--
DBMS_OUTPUT.Put_Line('.');
FOR e IN err_cur LOOP
DBMS_OUTPUT.Put_Line(e.text);
DBMS_OUTPUT.Put_Line('.');
FOR i IN 1..v_NumLines LOOP
IF e.line = i THEN
v_Prefix := '========> ';
ELSE
-- Note: The dots (...) are used because DBMS_OUTPUT normally strips
-- leading spaces
v_Prefix := '......... ';
END IF;
DBMS_OUTPUT.Put_Line(v_Prefix||to_char(i)||': '||t_Lines(i));
END LOOP;
DBMS_OUTPUT.Put_Line('.');
END LOOP;
END;
/
=======================================================================
Example
=======
SQL> CREATE OR REPLACE TRIGGER Test_Trigger
2 before insert or update of price on items
3 for each row
4 BEGIN
5 -- If extended amount is greater than $10,000 set the status
6 -- to pending approval, else approve the item.
7 IF (new.price*new.amount) > 10000 THEN
8 :new.status = 'P';
9 ELSE
10 :new.status := 'A';
11 END IF;
12 END;
/
Warning: Trigger created with compilation errors.
SQL> @trigger TEST_TRIGGER
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ; indicator
The symbol ":= was inserted before "=" to continue.
......... 1: BEGIN
......... 2: -- If extended amount is greater than $10,000 set
-- the status
......... 3: -- to pending approval, else approve the item.
......... 4: IF (new.price*new.amount) > 10000 THEN
========> 5: :new.status = 'P';
......... 6: ELSE
......... 7: :new.status := 'A';
......... 8: END IF;
......... 9: END;
......... 10: