RE: trapping errors from assignment of SQL*Plus Input Variables

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:Bill.Magaliff_at_lendware.com]
> Sent: Tuesday, December 10, 2002 1:09 PM
> To: 'Koivu, Lisa'; 'ORACLE-L_at_fatcity.com'
> Subject: RE: trapping errors from assignment of SQL*Plus Input
> Variables
>
> yes - would it matter?
>
> -----Original Message-----
> From: Koivu, Lisa [mailto:Lisa.Koivu_at_efairfield.com]
> Sent: Tuesday, December 10, 2002 1:05 PM
> To: 'ORACLE-L_at_fatcity.com'; 'Bill.Magaliff_at_lendware.com'
> 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: Bill.Magaliff_at_lendware.com
>
> 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: 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: Koivu, Lisa
  INET: Lisa.Koivu_at_efairfield.com

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: 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).