dplinnane
asked on
Compliation error on pl/sql trigger
I get the following errors.
SQL> show errors;
Errors for TRIGGER RESUMABLE_DEFAULT:
LINE/COL ERROR
-------- -------------------------- ---------- ---------- ---------- ---------
9/22 PLS-00215: String length constraints must be in range (1 ..
32767)
I am unsure of how to dreate a TYPE for these variables, I think that is the problem?
Thanks in advance.
err_type VARCHAR2;
object_owner VARCHAR2;
object_type VARCHAR2;
table_space_name VARCHAR2;
object_name VARCHAR2;
sub_object_name VARCHAR2;
error_txt VARCHAR2;
msg_body VARCHAR2;
10/22 PLS-00215: String length constraints must be in range (1 ..
32767)
11/22 PLS-00215: String length constraints must be in range (1 ..
32767)
12/22 PLS-00215: String length constraints must be in range (1 ..
32767)
LINE/COL ERROR
-------- -------------------------- ---------- ---------- ---------- ---------
13/22 PLS-00215: String length constraints must be in range (1 ..
32767)
14/22 PLS-00215: String length constraints must be in range (1 ..
32767)
15/22 PLS-00215: String length constraints must be in range (1 ..
32767)
16/22 PLS-00215: String length constraints must be in range (1 ..
LINE/COL ERROR
-------- -------------------------- ---------- ---------- ---------- ---------
32767)
SQL> show errors;
Errors for TRIGGER RESUMABLE_DEFAULT:
LINE/COL ERROR
-------- --------------------------
9/22 PLS-00215: String length constraints must be in range (1 ..
32767)
I am unsure of how to dreate a TYPE for these variables, I think that is the problem?
Thanks in advance.
err_type VARCHAR2;
object_owner VARCHAR2;
object_type VARCHAR2;
table_space_name VARCHAR2;
object_name VARCHAR2;
sub_object_name VARCHAR2;
error_txt VARCHAR2;
msg_body VARCHAR2;
10/22 PLS-00215: String length constraints must be in range (1 ..
32767)
11/22 PLS-00215: String length constraints must be in range (1 ..
32767)
12/22 PLS-00215: String length constraints must be in range (1 ..
32767)
LINE/COL ERROR
-------- --------------------------
13/22 PLS-00215: String length constraints must be in range (1 ..
32767)
14/22 PLS-00215: String length constraints must be in range (1 ..
32767)
15/22 PLS-00215: String length constraints must be in range (1 ..
32767)
16/22 PLS-00215: String length constraints must be in range (1 ..
LINE/COL ERROR
-------- --------------------------
32767)
CREATE OR REPLACE TRIGGER resumable_default
AFTER SUSPEND
ON DATABASE
DECLARE
/* declare transaction in this trigger is autonomous */
/* this is not required because transactions within a trigger
are always autonomous */
PRAGMA AUTONOMOUS_TRANSACTION;
cur_sid NUMBER;
cur_inst NUMBER;
errno NUMBER;
err_type VARCHAR2;
object_owner VARCHAR2;
object_type VARCHAR2;
table_space_name VARCHAR2;
object_name VARCHAR2;
sub_object_name VARCHAR2;
error_txt VARCHAR2;
msg_body VARCHAR2;
ret_value BOOLEAN;
mail_conn UTL_SMTP.CONNECTION;
BEGIN
-- Get session ID
SELECT DISTINCT(SID) INTO cur_SID FROM V$MYSTAT;
-- Get instance number
cur_inst := userenv('instance');
-- Get space error information
ret_value :=
DBMS_RESUMABLE.SPACE_ERROR_INFO(err_type,object_type,object_owner,
table_space_name,object_name, sub_object_name);
/*
-- If the error is related to undo segments, log error, send email
-- to DBA, and abort the statement. Otherwise, set timeout to 8 hours.
--
-- sys.rbs_error is a table which is to be
-- created by a DBA manually and defined as
-- (sql_text VARCHAR2(1000), error_msg VARCHAR2(4000),
-- suspend_time DATE)
*/
IF OBJECT_TYPE = 'UNDO SEGMENT' THEN
/* LOG ERROR */
INSERT INTO sys.rbs_error (
SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME
FROM DBMS_RESUMABLE
WHERE SESSION_ID = cur_sid AND INSTANCE_ID = cur_inst
);
SELECT ERROR_MSG INTO error_txt FROM DBMS_RESUMABLE
WHERE SESSION_ID = cur_sid and INSTANCE_ID = cur_inst;
-- Send email to receipient via UTL_SMTP package
msg_body:='Subject: Space Error Occurred
Space limit reached for undo segment ' || object_name ||
on ' || TO_CHAR(SYSDATE, 'Month dd, YYYY, HH:MIam') ||
'. Error message was ' || error_txt;
mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25);
UTL_SMTP.HELO(mail_conn, 'localhost');
UTL_SMTP.MAIL(mail_conn, 'sender@localhost');
UTL_SMTP.RCPT(mail_conn, 'recipient@localhost');
UTL_SMTP.DATA(mail_conn, msg_body);
UTL_SMTP.QUIT(mail_conn);
-- Abort the statement
DBMS_RESUMABLE.ABORT(cur_sid);
ELSE
-- Set timeout to 8 hours
DBMS_RESUMABLE.SET_TIMEOUT(28800);
END IF;
/* commit autonomous transaction */
COMMIT;
END;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This trigger was full of typos and mistakes it was taken directly from oracle documentation.
The _type was leading me to beliee I needed to creat a type variable.
The _type was leading me to beliee I needed to creat a type variable.
CREATE OR REPLACE TRIGGER resumable_default
AFTER SUSPEND
ON DATABASE
DECLARE
/* declare transaction in this trigger is autonomous */
/* this is not required because transactions within a trigger
are always autonomous */
PRAGMA AUTONOMOUS_TRANSACTION;
cur_sid NUMBER(10);
cur_inst NUMBER(10);
errno NUMBER(10);
err_type VARCHAR2(50);
object_owner VARCHAR2(50);
object_type VARCHAR2(50);
table_space_name VARCHAR2(50);
object_name VARCHAR2(50);
sub_object_name VARCHAR2(50);
error_txt VARCHAR2(50);
msg_body VARCHAR2(50);
ret_value BOOLEAN;
mail_conn UTL_SMTP.CONNECTION;
BEGIN
-- Get session ID
SELECT DISTINCT(SID) INTO cur_SID FROM V$MYSTAT;
-- Get instance number
cur_inst := userenv('instance');
-- Get space error information
ret_value :=
DBMS_RESUMABLE.SPACE_ERROR_INFO(err_type,object_type,object_owner,
table_space_name,object_name, sub_object_name);
/*
-- If the error is related to undo segments, log error, send email
-- to DBA, and abort the statement. Otherwise, set timeout to 8 hours.
--
-- sys.rbs_error is a table which is to be
-- created by a DBA manually and defined as
-- (sql_text VARCHAR2(1000), error_msg VARCHAR2(4000),
-- suspend_time DATE)
*/
IF OBJECT_TYPE = 'UNDO SEGMENT' THEN
/* LOG ERROR */
INSERT INTO rbs_error (
SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME
FROM dba_resumable /* typo DBMS_RESUMABLE */
WHERE SESSION_ID = cur_sid AND INSTANCE_ID = cur_inst
);
SELECT ERROR_MSG INTO error_txt FROM dba_resumable /* typo DBMS_RESUMABLE */
WHERE SESSION_ID = cur_sid and INSTANCE_ID = cur_inst;
-- Send email to receipient via UTL_SMTP package
msg_body:='Subject: Space Error Occurred
Space limit reached for undo segment ' || object_name ||
' on ' || TO_CHAR(SYSDATE, 'Month dd, YYYY, HH:MIam') ||
'. Error message was ' || error_txt;
mail_conn := UTL_SMTP.OPEN_CONNECTION('exchv2003.ibm.com', 25);
UTL_SMTP.HELO(mail_conn, 'exchv2003.ibm.com');
UTL_SMTP.MAIL(mail_conn, 'alerts@ibm.com');
UTL_SMTP.RCPT(mail_conn, 'tom@ibm.com');
UTL_SMTP.DATA(mail_conn, msg_body);
UTL_SMTP.QUIT(mail_conn);
-- Abort the statement
DBMS_RESUMABLE.ABORT(cur_sid);
ELSE
-- Set timeout to 8 hours
DBMS_RESUMABLE.SET_TIMEOUT(28800);
END IF;
/* commit autonomous transaction */
COMMIT;
END;
/
err_type VARCHAR2(25);
object_owner VARCHAR2(50);
object_type VARCHAR2(50);
table_space_name VARCHAR2(25); ...
msg_body VARCHAR2(2000);
Make sure to choose lengths long enough to fit the actual returned data...