Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.cc.ukans.edu!stl-feed.news.verio.net!newsreader.wustl.edu!unlnews.unl.edu!headwall.stanford.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: techguy_chicago@yahoo.com (Bomb Diggy)
Newsgroups: comp.databases.oracle.server
Subject: how to validate incoming pl/sql arguments for type/size?
Date: 7 Jul 2003 11:35:51 -0700
Organization: http://groups.google.com/
Lines: 23
Message-ID: <94599bb3.0307071035.7f7ed94e@posting.google.com>
NNTP-Posting-Host: 192.35.35.34
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1057602951 1010 127.0.0.1 (7 Jul 2003 18:35:51 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 7 Jul 2003 18:35:51 GMT
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:237079

My procedures have been generating 'ORA-01401: inserted value too
large for column' errors when I try to insert runtime parameters into
tables.  I would like to catch this before an insert attempt is made,
but I'm not sure of the best way to proceed.

For example, if I was accepting an incoming parameter of type CHAR(8)
and I wanted to verify that it was not a string of length <= 8, what
would be the best way to do that, syntax-wise?

My ideal, I suppose, would be:

IF (F_PARAMETER_DOES_NOT_FIT_INTO_TYPE( p_my_parameter, table.col%TYPE
) ) THEN
  RAISE a custom exception;
END IF

Can a column '%TYPE' be passed as an argument to a function?

Here is my proc declaration:

PROCEDURE MYPROC(  P_MY_NUMBER  IN  [TABLE_NAME].[COLUMN_NAME]%TYPE );

Thanks.