Re: Import and export in a Db neutral way

Thanks Ron,

I know other databases do not use SQL*Loader, but my first stop is to make sure I can load what I extract from Oracle back into Oracle. Ideally I would like to export the data without the blanks padding each column's data, but cannot work out how to do it. Then I could use the stream option of SQL*Loader.

The case studies seemed to do everything but the most common format around, CSV. I would have thought that CSV files being so common that the Oracle documentation would have an example of how to load one of those? My need is similar to CSV but with a different delimiter, and the fixed column widths padded with blanks.

The fact is that being a programmer it rapidly gets to the point that it is quicker to write some code using JDBC to connect to Oracle to do the export and import. It would not perform as well as SQL*Loader but what the hey.

Lets face it if Oracle was not such a vast product you would not have specialist
DBAs ;)

Regards

David

"Ron Reidy" <rereidy_at_indra.com> wrote in message news:3C05202E.D26A6A48_at_indra.com...
> 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.
Received on Wed Nov 28 2001 - 19:54:25 CET