Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Bulk Data Type Testing Question

Re: Bulk Data Type Testing Question

From: EscVector <Junk_at_webthere.com>
Date: 26 Mar 2007 06:35:17 -0700
Message-ID: <1174916117.135353.206170@b75g2000hsg.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US