RE: PL-SQL HELP

Thanks, Diana!
You are correct! I went ahead and did the to_number conversion and then threw in exception with a message!

• Diana Duncan <Diana_at_fileFRENZY.com> wrote:
  > 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-----
  > Sent: Monday, October 09, 2000 1:25 PM
  > To: Multiple recipients of list ORACLE-L
  >
  >
  > 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).
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Diana Duncan
  > INET: Diana_at_fileFRENZY.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).
  

  ---