Re: Exporting Oracle table to Access
Date: 1998/01/27
Message-ID: <6am19c$hrq$1_at_pebble.ml.org>
In article <01bd2b69$e491e910$53a88d8e_at_sud978>,
Alphons Evers <eversa-delete.this-_at_gov.on.ca> wrote:
>Could someone tell me how to export oracle tables to another format.
>(maybe tab delimited) I need to be able to use the tables with access.
To get a tab delimited file, use SQL*Plus to select the fields explicitly, with a tab concatenated between the fields, and a spool statement to create the file.
Doesn't access have odbc to get to oracle directly?
comp.databases.oracle has been replaced by several groups, you might get better answers to questions if you post on those groups rather than here.
The following is a couple of posts relating to this in the past. Please note that they have been cut/pasted numerous times and I haven't tried them. I used to have my own generic one, but I lost it.
Article 35281 of comp.databases.oracle:
Path: rossix!openlink.one-o.com!imci5!pull-feed.internetmci.com!news.internetMCI.com!newsfeed.internetmci.com!newsserver.jvnc.net!newsserver2.jvnc.net!howland.reston.ans.net!EU.net!Austria.EU.net!newsfeed.ACO.net!01-newsfeed.univie.ac.at!02-newsfeed.univie.ac.at!news.via.at!usenet
From: Dieter Oberkofler <dtr_at_leadingbits.via.at>
Newsgroups: comp.databases.oracle
Subject: Re: ASCII datafile export
Date: Mon, 29 Apr 1996 17:49:59 +0200
Organization: LEADING BITS
Lines: 331
Message-ID: <3184E527.2878_at_leadingbits.via.at>
References: <4lipi8$rs9_at_client1.news.psi.net>
NNTP-Posting-Host: lbits.via.at
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="------------525C23886422"
X-Mailer: Mozilla 2.0 (WinNT; I)
To: "Becky L. Glesner" <glesner_at_woowoo>
This is a multi-part message in MIME format.
--------------525C23886422 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit
Becky L. Glesner wrote:
>
> Hello:
>
> I need to export data from oracle tables to ASCII datafiles.
> Is my only option to use PL/SQL? I'm hoping for a
> utility like SQL*Loader that unloads rather than loads data.
>
> Thanks for any help,
> Becky
Have a look to this code... I use it quite often!
- START OF SOURCE ---
|| || Description: || Given a tablename generates || 1. Flat ascii file with delimiters || 2. SQL*Load control file to load the data || || Condition: || Seems ok, needs more testing ||
*/
set echo off
set doc off
set pagesize 0
set feedback off
set verify off
set pause off
accept vtbl_name prompt 'Enter table name: '
define ColumnDelim = "|"
define DateFormat = "yyyymmddhhmiss"
define dataname = &&vtbl_name..dat
spool data.sql
prompt ALTER SESSION SET NLS_DATE_FORMAT="&DateFormat.";;
--
-- Header info
--
prompt /*
prompt || Script Name: data.sql
prompt || Author: Ramesh K Meda
prompt || Date: Feb 1995 (Does day matter?)
prompt || Copyright info: Feel free to copy!
prompt || Fees: As you please!
prompt */
prompt clear columns
--
-- Set up column formats
--
select 'column ' || column_name || ' format ' ||
DECODE (data_type,
'DATE', 'A14'
,'NUMBER', 'S' ||
rpad('0', nvl(data_precision - nvl(data_scale,0), 38), '0')||
'.' ||
rpad('0', nvl(data_scale, 5), '0')
,'A' || data_length
)
from user_tab_columns
where table_name = UPPER('&&vtbl_name')
order by column_id;
--
-- Set Line size to export data
--
select 'set linesize '||
sum(DECODE(data_type
,'DATE', 25
,'NUMBER', nvl(data_precision,45) + 5
, data_length + 5
)
)
from user_tab_columns
where table_name = UPPER('&&vtbl_name');
prompt set echo off
prompt set pagesize 0
prompt set space 0
prompt set feedback off
prompt set verify off
prompt set pause off
prompt set termout off
prompt spool &dataname
prompt select
select decode(column_id, 1, ' ', ',')
, column_name
, ',''&ColumnDelim.'''
from user_tab_columns
where table_name = upper('&&vtbl_name')
order by column_id
/
prompt from &&vtbl_name;;
prompt spool off
prompt set pagesize 15
prompt set feedback on
prompt set verify on
prompt set heading on
prompt set linesize 80
prompt set termout on
spool off
--
-- Create control file
--
prompt Generating control file for SQL*Load
set lines 100
spool &&vtbl_name..ctl
prompt
prompt load data
prompt replace
prompt into table &vTbl_Name
prompt (
select decode (column_id, 1, ' ', ',') ||
column_name ||
chr(9) ||
decode(data_type, 'DATE', ' DATE "&DateFormat"', ' CHAR ') ||
chr(9) ||
' terminated by ''&ColumnDelim.'' ' ||
' nullif ' ||
column_name ||
' = blanks '
from user_tab_columns
where table_name = upper('&&vtbl_name')
order by column_id
/
prompt )
spool off
clear screen
prompt Generating data file
_at_data.sql
prompt Files generated:
prompt SQL script: data.sql
prompt data file: &&vtbl_name..dat
prompt control: &&vtbl_name..ctl
--- END OF SOURCE ---
Dieter Oberkofler
Software Engineer
------------------------------------------------------------------
LEADING BITS GmbH. Tel (+43-1) 586 76 11
Schleifmuehlgasse 5/17 Fax (+43-1) 587 76 15
A-1040 Vienna E-mail dtr_at_leadingbits.via.at
Austria Compuserve 100141.1314
------------------------------------------------------------------
--------------525C23886422
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="FLAT.SQL"
/*
||
|| Description:
|| Given a tablename generates
|| 1. Flat ascii file with delimiters
|| 2. SQL*Load control file to load the data
||
|| Condition:
|| Seems ok, needs more testing
||
*/
set echo off
set doc off
set pagesize 0
set feedback off
set verify off
set pause off
accept vtbl_name prompt 'Enter table name: '
define ColumnDelim = "|"
define DateFormat = "yyyymmddhhmiss"
define dataname = &&vtbl_name..dat
spool data.sql
prompt ALTER SESSION SET NLS_DATE_FORMAT="&DateFormat.";;
--
-- Header info
--
prompt /*
prompt || Script Name: data.sql
prompt || Author: Ramesh K Meda
prompt || Date: Feb 1995 (Does day matter?)
prompt || Copyright info: Feel free to copy!
prompt || Fees: As you please!
prompt */
prompt clear columns
--
-- Set up column formats
--
select 'column ' || column_name || ' format ' ||
DECODE (data_type,
'DATE', 'A14'
,'NUMBER', 'S' ||
rpad('0', nvl(data_precision - nvl(data_scale,0), 38), '0')||
'.' ||
rpad('0', nvl(data_scale, 5), '0')
,'A' || data_length
)
from user_tab_columns
where table_name = UPPER('&&vtbl_name')
order by column_id;
--
-- Set Line size to export data
--
select 'set linesize '||
sum(DECODE(data_type
,'DATE', 25
,'NUMBER', nvl(data_precision,45) + 5
, data_length + 5
)
)
from user_tab_columns
where table_name = UPPER('&&vtbl_name');
prompt set echo off
prompt set pagesize 0
prompt set space 0
prompt set feedback off
prompt set verify off
prompt set pause off
prompt set termout off
prompt spool &dataname
prompt select
select decode(column_id, 1, ' ', ',')
, column_name
, ',''&ColumnDelim.'''
from user_tab_columns
where table_name = upper('&&vtbl_name')
order by column_id
/
prompt from &&vtbl_name;;
prompt spool off
prompt set pagesize 15
prompt set feedback on
prompt set verify on
prompt set heading on
prompt set linesize 80
prompt set termout on
spool off
--
-- Create control file
--
prompt Generating control file for SQL*Load
set lines 100
spool &&vtbl_name..ctl
prompt
prompt load data
prompt replace
prompt into table &vTbl_Name
prompt (
select decode (column_id, 1, ' ', ',') ||
column_name ||
chr(9) ||
decode(data_type, 'DATE', ' DATE "&DateFormat"', ' CHAR ') ||
chr(9) ||
' terminated by ''&ColumnDelim.'' ' ||
' nullif ' ||
column_name ||
' = blanks '
from user_tab_columns
where table_name = upper('&&vtbl_name')
order by column_id
/
prompt )
spool off
clear screen
prompt Generating data file
_at_data.sql
prompt Files generated:
prompt SQL script: data.sql
prompt data file: &&vtbl_name..dat
prompt control: &&vtbl_name..ctl
>
>TIA
>
>
>--
>Alphons Evers
>Ontario MNDM
>Cartographic Unit
>reply to: evers_at_gov.on.ca
--
These opinions are my own and not necessarily those of Information Quest
jgarry_at_eiq.com http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?" I AM the _at_#%*& DBA!
Received on Tue Jan 27 1998 - 00:00:00 CET