Re: Bulk Data Type Testing Question

EscVector wrote:
> What is the best way to determine if col1 contains a convertible
> number?
> Bulk collect? Is there a simple SQL function to handle ORA-01722:
> invalid number.
>
> Only the values inserted with 2 or -1 will should count.
>
> CREATE TABLE TEST1(COL1 VARCHAR2(4000);
>
> BEGIN
> FOR x in 1..1000000
> LOOP
> INSERT INTO TEST1
> SELECT
> CASE mod(dbms_crypto.randomnumber,3)
> WHEN 1 THEN 'ABC'
> WHEN 2 THEN '123'
> WHEN -1 THEN '100.42'
> WHEN 0 THEN '444.4a' -- NULL --
> to_char(dbms_crypto.randomnumber)
> ELSE 'BJORK'
> END
> FROM DUAL;
> END LOOP;
> END;
> /

Bulk collect into an array.
Delete unwanted elements from the array
FORALL INSERT using the INDICES OF syntax http://www.psoug.org/reference/array_processing.html

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org