Re: Porting Access tables into Oracle

On Fri, 15 Aug 2003 13:10:59 +0800, Jane wrote:

> One more question. Is it possible to generate a " xxxx.sql" file from the
> oracle schema for backup purpose ?
>

Because you are on 9i, yes there is, but not at the schema level: try

select dbms_metadata.get_sql('TABLE','EMP') from dual;

(I'm doing this from memory, so you might try a 'describe dbms_metadata' to see the precise parameter requirements of the procedure).

If you precede that with a 'spool emp.sql' and follow it with a 'spool off', you'll have an emp.sql text file which can be used to re-create the table.

Or are you talking about getting just the data out, rather than the table structures? If so, that can be done, too:

select empno || ',' || ename || ',' || sal from emp;

...gives you a comma-separated variable output. Again, spool xxx.csv beforehand and spool off afterwards to have the data written to disk.

There are a couple of things to watch about that last one. You probably want to

set trimspool on
set header off

...before doing it. Also, if you're outputting text fields that might contain their own commas, then you'll need to be a bit more subtle the way you frame the sql statement... the relevant fields will need to be concatenated with double-quotes so that the internal commas are treated literally.

Oh, and if you wanted tab-delimited output of the data, then that would be something like:

select empno || chr(10 || ename chr(10) || sal from emp;

Hope that helps
Regards
HJR Received on Fri Aug 15 2003 - 08:08:24 CDT