RE: PL-SQL HELP

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Mon, 9 Oct 2000 17:01:29 -0400
Message-Id: <10644.118829@fatcity.com>

How about:

create or replace function to_integer(nameid varchar2) return number is

	revseq	number;
	invalid_number	EXCEPTION;
	pragma EXCEPTION_INIT (invalid_number, -1722);
	numeric_error	EXCEPTION;
	pragma EXCEPTION_INIT (numeric_error, -6502);
begin
	return to_number(nameid);
exception
	when invalid_number or numeric_error then
		raise_application_error(-20998, 'error | incorrect id');

end;

Seems weird to go through all that, though. Why not just do the to_number and throw the invalid number or numeric error exception?

Diana

-----Original Message-----
From: Viktor [mailto:stant_98_at_yahoo.com] Sent: Monday, October 09, 2000 1:25 PM
To: Multiple recipients of list ORACLE-L Subject: PL-SQL HELP

Hi, all!

Can someone help me convert this lines to ORACLE PL-SQL:
select @idseq = substring(@nameid,5,1)
if select patindex("%[a-z,A-Z,*,?,!,,,(,)",@idseq) > 0 begin
select "error|incorrect id
 return -998
end
else
 select @revseq = convert(smallint,@idseq)

What this supposed to accomplish is that it should exit out of the procedure and warn user that the string should not contain non-integer values.

Thanks.

---

Do You Yahoo!?
Yahoo! Photos - 35mm Quality Prints, Now Get 15 Free! http://photos.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Viktor
  INET: stant_98_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Oct 09 2000 - 16:01:29 CDT