Re: Import and export in a Db neutral way

David wrote:
>
> Hello,
>
> I have been experimenting with import and export from tables in Oracle
> 8.1.5. I need to export in a form that allows other brands of Db to import
> this data (it is metadata for a Tomcat 4.0 Servlet based application).
>
> I used the following script to export.
>
> <snip>
> SET NEWPAGE 0
> SET SPACE 0
> SET LINESIZE 3000
> SET PAGESIZE 0
> SET ECHO OFF
> SET FEEDBACK OFF
> SET HEADING OFF
> SET COLSEP |
>
> SPOOL ID_TABLE.DAT
> SELECT * FROM ID_TABLE;
> SPOOL OFF
>
> SPOOL RW_DATABASE.DAT
> SELECT * FROM RW_DATABASE;
> SPOOL OFF
> <snip>
>
> Now the problem lies with importing I have not been able to work out how to
> use
> SQL*Loader to import these files (using a control file) and in the process
> trim all trailing blanks. I find the Oracle documentation fairly
> impenetratable on control files for SQL*Loader. The reason I use the pipe
> symbol ('|') to delineate records is that some of the description columns
> have commas.
>
> The control file I thought might work goes like this, but I am not sure how
> to indicate the end of each record. These are fixed length record (3000
> bytes) with <CR><LF> at the end (on W2K)
>
> load data
> infile 'rw_record.dat' "what goes here?"
> into table rw_record
> fields terminated by x'7C'
> trailing nullcols
> (rw_record_id INTEGER,
> view_id INTEGER,
> file_id INTEGER,
> stream_id INTEGER,
> record_name VARCHAR(50),
> record_description VARCHAR(255),
> record_type_length INTEGER,
> record_type_id VARCHAR(30),
> record_content_type VARCHAR(8),
> record_terminator VARCHAR(4),
> record_process_change INTEGER
> )
>
> Any suggestions on a control file or a better way to export losing the
> trailing blanks?
>
> Thanks and Regards
>
> David Wynter
> Lone developer at roamware Ltd.
Other brands of Db do not use SQL*Loader (AFAIK).

For control files, did you read the utils docs (including case studies)? If that does not help, get the O'Reilly book 'Oracle SQL*Loader'.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.