SQL Export Command for comma delimited file 1

[j1500]

I'm just wanting to run a SQL command through SQL Plus to generated a comma delimited file of every column and record in my database. Do you have any advice on how to go about this?

Thank you so much!

 

[SantaMufasa]

...SQL Plus to generate a comma delimited file of every column and record in my database...

J1500, I presume that you want the data delimited by table, within schema, within the database, correct? Without these breakouts, the resulting data would become virtually meaningless.


Following is a script that I saved to a file named "GenASCII.sql". Section 1 shows the results of running GenASCII.sql; Section 2 shows the code that appears in GenASCII.sql.

Section 1 -- Invocation and proof of concept:

SQL> @genascii
Enter the Oracle username that owns the source table: test
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 test.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"
"99","Extra","1"

Output file = "temp.txt"

Section 2 -- GenASCII.sql script

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 w prompt "Enter the Oracle username that owns the source table: "
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,'''"''||','||'',"''||') || column_name || '|| ''"''' 
  from all_tab_columns
 where owner = upper('&w')
   and table_name = upper('&x');
prompt from &w..&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

Let us know if this provides you with enough information to resolve your needs.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[j1500]

Where do I store this file in order to run it?

Do I save it directly on a directy on the Oracle machine.

Or is there somehow I can run it saved on my desktop and run it through SQL*Plus?

Sorry I'm very new to this!

 

[SantaMufasa]

J1500,

The GenASCII.sql script presumes that:

1) You run the script from a SQL*Plus session.
2) You have SELECT access to the table(s) you ask the script to output
3) The resulting comma-separated file resides on a disk file system to which your client session have write access.

I presume that you are running a SQL*Plus client session from a Windows machine. To determine the default location that SQL*Plus uses for both a) scripts and b) spooled output from a SQL*Plus session, you can

1) <right-click> whatever icon you use to invoke SQL*Plus
2) Choose "Properties" from the resulting menu
3) Look at the value in the "Start in:" entry. That value indicates the default directory that SQL*Plus uses for both reading and writing.

You can, however, fully qualify (with path names) any references in SQL*Plus for both scripts and spooled output files. This way, you do not need to know the name of the default directory.

Did this answer your question(s)?


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]