Re: UNLOAD function

From: M. Armaghan Saqib <armaghan_at_yahoo.com>
Date: 2000/03/05
Message-ID: <89sgrm$6eh$1@nnrp1.deja.com>#1/1

Oracle does not provide a tool utility to UNLOAD data. (It does provide SQL
Loader to LOAD data only).

Use my freeware SQLPlusPlus which does this using simple commands from SQL
Plus. E.g. to unload DEPT table you could generate something like the following script in SQLPlus.

SQL> EXEC S.H('BLDDAT')
| ----------------------------------------------------------------------

---

---

| BLDDAT -- Build ASCII Flat Files from Tables
| ----------------------------------------------------------------------

---

---

| PROCEDURE s2.blddat (tabname IN VARCHAR2 DEFAULT '%',
| own IN VARCHAR2 DEFAULT USER);
|
| ----------------------------------------------------------------------

---

---

SQL> EXEC S2.BLDDAT('DEPT')
| SET HEADING OFF
| SET PAGESIZE 0
| SET TERMOUT OFF
| SET FEEDBACK OFF
| SET LINESIZE 2000
| SET TRIMSPOOL ON
| SPOOL test_dept.dat
| SELECT
| deptno||CHR(44)
| ||chr(34)||dname||chr(34)||CHR(44)
| ||chr(34)||loc||chr(34)||CHR(44)
| ||deptsal
| FROM test.dept;
| SPOOL OFF
|
| SET TERMOUT ON
In addition you could also generate the control file which is required by
SQL Loader to load this data again to oracle database.

SQL> EXEC S.H('BLDCTL')
| ----------------------------------------------------------------------

---

---

| BLDCTL -- Build SQL Loader Control Files
| ----------------------------------------------------------------------

---

---

| PROCEDURE s2.bldctl (tabname IN VARCHAR2 DEFAULT '%',
| own IN VARCHAR2 DEFAULT USER);
|
| ----------------------------------------------------------------------

---

---

SQL> EXEC S2.BLDCTL('DEPT')
| LOAD DATA
| INFILE 'TEST_DEPT.dat'
| REPLACE INTO TABLE DEPT
| FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
| TRAILING NULLCOLS(
| DEPTNO ,
| DNAME ,
| LOC ,
| DEPTSAL )
regards,
M. Armaghan Saqib

+---------------------------------------------------------------


| 1. SQL PlusPlus => Add power to SQL Plus command line

| 2. SQL Link for XL => Integrate Oracle with XL
| 3. Oracle CBT with sample GL Accounting System
| Download free: http://www.geocities.com/armaghan/

 +---------------------------------------------------------------

| SQLPlusPlus now on http://www.ioug.org/
| "PL/SQL package that extends SQL*Plus to another dimension.
| Contains a PL/SQL code generator and set of extremely useful
| utilites with extensive documentation." IOUG Web Site

+---------------------------------------------------------------

d_taggart <d_taggart_at_hotmail.com> wrote in message news:38BFFC68.755DE692_at_hotmail.com...
> I am looking for a function in Oracle to perform what Sybase does  with

> its BCP utility.  Another word, I would like to offload the content of
> an Oracle table in some form of ASCII delimted format.  Is this
> possible?  Thanks

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Mar 05 2000 - 00:00:00 CST