Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2888
  • Last Modified:

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)
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;
/

Open in new window

0
dplinnane
Asked:
dplinnane
  • 2
1 Solution
 
dmcdconsultCommented:
that error usually refers to changing the VARCHAR2  to VARCHAR2(250);  the '250' is an example and could be as the error states (imagine that!) any number between 1 and 32767.



0
 
dmcdconsultCommented:
For example:
   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...
0
 
dplinnaneAuthor Commented:
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.


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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now