Re: Bulk Data Type Testing Question

On Mar 23, 5:13 pm, DA Morgan <damor..._at_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..._at_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. Received on Mon Mar 26 2007 - 08:35:17 CDT