how to unload data from oracle db

[suhaimy]

Hi,
is there any way I can unload data from oracle db using sqlplus. My environment is HPUX ver 11 & Oracle 9i.
The data that I want have separator |with it.

thanks

 

[SantaMufasa]

Suhaimy,

Do you mean that you want to create a flat file of data for each of your tables? And you want the fields to be variable length, separated by "|"s? If so, then yes, I can post a script for you. But first I just wanted to ensure that I understand your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

...and do you want double quotes to surround the data: Example:

"Data 1"|"Data 2"|"Data 3"

Or do you want the output:

Data 1|Data 2|Data 3

Let me know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[suhaimy]

Hi Mufasa,
sorry for late reply busy with works load

i want the output to be like Data 1|Data 2|Data 3|

thanks

 

[SantaMufasa]

Suhaimy,

Here is your code. (Since the code contains an "accept...prompt", you must save the code to a script, then execute the script. In the sample invocation, below, I called the script "GENASCII.sql":

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC, makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
accept x prompt "Enter the table to ASCII: "
accept y prompt "Enter the flat file to write: "
prompt
prompt Following output is generated script that writes text output from table "&x"
prompt 
set verify off
set feedback off
set heading off
set pagesize 0
set linesize 32767
set trimout on
set trimspool on
spool temp.sql
prompt set echo off
prompt set feedback off
prompt set heading off
prompt set pagesize 0
prompt spool &y
prompt Select
select decode(rownum,1,null,'||')||column_name || '||''|''' 
  from user_tab_columns
 where table_name = upper('&x');
prompt from &x
prompt /
prompt spool off
prompt set feedback on
prompt set heading on
prompt set pagesize 20
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt Output file = "&y"
prompt

Sample invocation:

SQL> @GENASCII
Enter the table to ASCII: s_dept
Enter the flat file to write: temp.txt

Following output is generated script that writes text output from table "s_dept"

set echo off
set feedback off
set heading off
set pagesize 0
spool temp.txt
Select
ID||'|'
||NAME||'|'
||REGION_ID||'|'
from s_dept
/
spool off
set feedback on
set heading on
set pagesize 20

Following is text output written to file "temp.txt"

10|Finance|1|
31|Sales|1|
32|Sales|2|
33|Sales|3|
34|Sales|4|
35|Sales|5|
41|Operations|1|
42|Operations|2|
43|Operations|3|
44|Operations|4|
45|Operations|5|
50|Administration|1|

Output file = "temp.txt"

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.