Importing small database from Omniform

Does anyone have experience importing data from an ScanSoft Omniform file into Oracle via ODBC? I've run into a few problems that I am not sure how to go about fixing. Omniform started out as scan software with fillable fields (kind of like PDF now). They do have capabilities for a very nasty quasi-filemaker database system, which I'm currently tasked to get rid of.

The problem: First, Omniform will output any multi-line input as a long. That's no big problem; I export each long separately so there is only one long field being generated per table. Then, modify those fields to something like varchar2(500), and merge.

Second, Omniform date fields are restricted in formatting to m/dd/yy[yy] or MMM, dd yyyy. I believe I need to get this into some form of DD-MMM-YYYY (whatever is default for oracle). I thought about exporting into a more ODBC-friendly environment like Access, but any attempt to do so has failed pretty miserably. For Excel, "Fractional truncation (null)" and "ERROR in CREATE TABLE statement" for Access. I've read on ScanSoft's forums and this error occurs in the case of a field longer than 255 characters, however, size-limiting my fields didn't seem to help).

The error from ODBC engine I'm receiving is ORA-01483 "Invalid length for DATE or NUMBER bind variable."

I think I have about given up on this nightmare; exporting directly to a text file should work. From that file, I should be able to write a procedure to import all the data into the 10g database. Unless, that is, someone has had similar experiences and can tell me what I'm overlooking.

Thanks

Brad Liang Received on Thu May 12 2005 - 20:43:43 CEST