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

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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Mar 23, 5:13 pm, DA Morgan <damor...@psoug.org> wrote:
> 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 syntaxhttp://www.psoug.org/reference/array_processing.html
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Thanks for the reply. This is exactly what I wanted to do.

Using REGEXP the BULK COLLECT, FORALL, INDICES will work nicely.