Re: Trying to convert ASCII into Oracle. Any good data mining software?

I've become the local "expert" on converting ascii text into Oracle data in our shop. I've written various VB routines to read the data and write it to Oracle. The complexity of the reads depends of course on the integrity of the source data. If you always get 6 fields in X order, it's a breeze (line input, split, write to oracle, until eof). If you might get 6 fields this time, 10 the next and back to 5, you have to write a front end that will prompt the user to provide the field count and which field is which. This get's a bit trickier.

In any case, single record writes to the DB are SLLLOOOOOWWWWW because of the overhead of calling Oracle each time from a separate app. I had to write an Oracle SP that will accept a <4000 byte (Oracle can't take a single data piece longer than that) string with fields and records delimited within the string. My app created this in a known order so the proc didn't have to sort things out.

I went from 600 inserts per minute to 6000 inserts per minute with this change!

Quick and dirty (feel free to do better error trapping...) extract function:
Function EXTRACT_RECORD
  ( iRecords IN OUT varchar2,
    iDelim IN varchar2)
  RETURN varchar2 IS

iLen        integer;
oData       varchar2(4000);

BEGIN

• Get end of first record in records select instr(iRecords, iDelim, 1) into iLen from dual; if iLen > 0 then
  • Get first record itself select substr(iRecords, 1, iLen - 1) into oData from dual;
  • Modify input to remove extracted record select substr(iRecords, iLen + length(iDelim)) into iRecords from dual; else if length(iRecords) > 0 then
    • Return input as output, clear input oData := iRecords; iRecords := NULL; else oData := 'ERROR'; end if; end if;

    RETURN oData;
EXCEPTION
   WHEN others THEN

       oData := 'Error' ;
       return oData;

END; -- Function EXTRACT_RECORD

Feed this the input string and delimiter to split on and you'll get back the first record/field with the source modified to remove that record/field and the delimiter. As such, make sure you save your real source if you need it for something else.

Sample usage
Source :=
'Rec1Field1|Rec1Field2|Rec1Field3|$$$Rec2Field1|Rec2Field2|Rec2Field3|$$$' First call Record := Extract_Record(Source,'$$$')

    Function returns 'Rec1Field1|Rec1Field2|Rec1Field3|' (the data up to $$$ delim)

    Source modified to 'Rec2Field1|Rec2Field2|Rec2Field3|$$$' ready for the next record extraction
Next call three times (Field := Extract_Record(Record,'|') to pull out Rec1Field1, 2, 3
Use those fields in your update/insert
Loop for next record

Enjoy!

Bruce

jmkim3_at_my-deja.com wrote:

> I have a printstream of data in ASCII format that I need to get into an
> Oracle database. Currently, I'm using a perl script to do the
> conversion. I'm wondering if anyone knows of a good software package
> that can do this. I've read up on Monarch, but I'd like to know of any
> others that you've had experience with. Thanks.
>
> -James
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Oct 21 1999 - 12:35:58 CDT