Export FileMaker data to Oracle SQL db
Posted on 2011-02-26
I have a large FM DB with text, number, date, and timestamp fields that I need to export and upload to an Oracle SQL db. I normally do a small subset upload by exporting a csv file from FM, replacing ',' separators with '|' (comma to pipe delimiter), then upload a pipe-delimited text file to Oracle. The larger complete FBDB has several fields that are composed of strings of five alphanumeric codes each preceded by a comma that must maintain their original format (similar to a concatenated string of 10 ZIP codes with a comma delimiter and the first term preceded also by a comma). Thus, doing a search and replace of all commas with pipes is not feasible.
On the Oracle side, I can either upload a text file or use SQL Developer to write an INSERT or APPEND query.
What is the most suitable FM output?
What is a sample SQL INSERT query for LastName (text/varchar2), Age (number), DOB (date), and ArrivalTime (timestamp) into a schema Oracle::Demographics?