Re: PL/SQL: cheking for the OVERFLOW of an Variable

From: Tony Andrews <andrewst_at_onetel.com>
Date: 27 Jun 2005 09:53:43 -0700
Message-ID: <1119891223.445328.6150@g43g2000cwa.googlegroups.com>

baka wrote:
> Dear Sir/Madam
> In PL/SQL how can I Find the maximum length of an variable inside the
> PL/SQL block
>
> here is my problem (Image only)
>
> CREATE OR REPLACE PROCEDURE test1(strin varchar2) is
> DECLARE
> tstr2 abctablel.col1%TYPE;
> ......
>
> Begin
>
> LOOP
> tstr2 := substr(strin,instr(strin, '\') + 1);
> IF LENGTH(tstr2)<=LENGTH(abctablel.col1) THEN
> VVVVVVVVVVVVVVVVVV??????????
> do something
> .......
> .......
> printme(' Thanks for the proper input');
>
> exit;
> ELSE
> printme('You have some serious Problem ,chek the size');
> exit;
> END IF;
> END LOOP;
>
> END;
>
> How can i Compare the [VVVVVVVVVVVVVVVVVV?????????? ] Portion
>
>
> Thank you in ADVANCE

Trap the exception:

SQL> CREATE OR REPLACE PROCEDURE test1(strin varchar2) is   2 tstr2 emp.ename%TYPE;
  3 Begin

  4     tstr2 := substr(strin,instr(strin, '\') + 1);
  5     dbms_output.put_line(' Thanks for the proper input');
  6  EXCEPTION
  7     WHEN VALUE_ERROR THEN
  8       dbms_output.put_line('You have some serious Problem ,chek the

size');
  9 END;
 10 /

Procedure created.

SQL> exec test1('\xxxx')
 Thanks for the proper input

PL/SQL procedure successfully completed.

SQL> exec test1('\xxxxxxxxxxxxxxxx')

You have some serious Problem ,chek the size

PL/SQL procedure successfully completed. Received on Mon Jun 27 2005 - 11:53:43 CDT