Well, seems to me the script won't execute unless A value for v_runcr is specified, or v_runcr is defined in the sqlplus environment prior to execution. Now, numeric or not, sqlplus doesn't care, of course. In the anon block you could test the variable after assignment by trying TO_NUMBER on it and catching the resulting 1722 error in a begin/exception/end block before execution even starts.
HTH - Lisa
-----Original Message-----
From: Magaliff, Bill [SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, December 10, 2002 1:09 PM
To: 'Koivu, Lisa'; '[EMAIL PROTECTED]'
Subject: RE: trapping errors from assignment of SQL*Plus Input Variables
yes - would it matter?
-----Original Message-----
From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 10, 2002 1:05 PM
To: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: RE: trapping errors from assignment of SQL*Plus Input Variables
Bill, is this an anonymous block you are talking about?
Lisa Koivu
Oracle Diaper Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
-----Original Message-----
From: Magaliff, Bill [SMTP: ]
Sent: Tuesday, December 10, 2002 11:49 AM
To: Multiple recipients of list ORACLE-L
Subject: trapping errors from assignment of SQL*Plus Input Variables
Is there any way to trap errors from the assignment of a SQL*Plus variable
to a PL/SQL variable?
I have this in my executable section:
v_runcr := &&i_runcr;
(v_runcr is declared as a number)
I want to trap the case where either no value for i_runcr is specified at
runtime (and thus the assignment should be NULL) or a non-numeric character
is specified (e.g., a letter).
I had hoped the WHEN OTHERS exception would handle it but it does not.
Any ideas?
Thanks
bill
--
Please see the official ORACLE-L FAQ: <http://www.orafaq.com>
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 <http://www.fatcity.com>
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).