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

this was the right direction -  

I changed the assignment line to this:  

v_runcr := nvl(to_number('&&i_runcr'), 0);  

and it catches the 6502 (numeric or value error) quite nicely.  

Thanks, Lisa!  

-bill

-----Original Message-----
Sent: Tuesday, December 10, 2002 1:19 PM To: 'Magaliff, Bill'; 'ORACLE-L_at_fatcity.com'

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-----

Sent: Tuesday, December 10, 2002 1:09 PM To: 'Koivu, Lisa'; 'ORACLE-L_at_fatcity.com' Variables

        yes - would it matter?

-----Original Message-----

<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'

        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-----

Sent: Tuesday, December 10, 2002 11:49 AM To: Multiple recipients of list ORACLE-L

        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
<http://www.orafaq.com> >
--
Author: Magaliff, Bill
  INET: Bill.Magaliff_at_lendware.com 

	Fat City Network Services    -- 858-538-5051 <
http://www.fatcity.com <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: 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).