Create text file in oracle using sql

Question

I need to grab data from some oracle database tables and format it into a fixed width text file. I want to know if its possible to create a text file using sql. I looked at some stuff and found the bc and xp_cmdshell but they are a bit confusing.

I am pretty new to sql and oracle databases.

Is this possible and how can I begin?

I don't need to open a file or check for existing file, overwriting is fine, what ever makes it easiest. I don't need anything big or complex, a simple script to grab values and create a text file.

Just an update: I don't think bcp works in the toad for oracle editor. I found this tutorial here: http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell but the first bcp command does not compile, it says invalid sql query

@SOaddict - I would like the file to be on my computer, preferably in my documents. — RXC
– RXC, Commented Oct 24, 2013 at 18:41
So you are basically trying to run a simple SQL query and you want to have those results in a text file.Am i correct? — Teja
– Teja, Commented Oct 24, 2013 at 18:44
You can write a shell script which has all oracle connection details — Teja
– Teja, Commented Oct 24, 2013 at 18:52

WoMo · Accepted Answer · 2013-10-24 18:51:06Z

2

If you are using the SQL*Plus client, you can spool to an output file. Here is a sample SQL*Plus file:

set serveroutput on size 1000000
set linesize 150
spool C:\path_to_file\filename.extension

-- Your SQL statement
select columns
from table
where somecondtion;

-- Your next SQL Statement
select ...
from ...;

spool off

answered Oct 24, 2013 at 18:51

WoMo

7,2862 gold badges31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RXC Over a year ago

I am trying to use what you provided but I am getting an error: "Invalid SQL Statement". Not sure what I am missing, but the word off in the last line is underlined red. I am using Toad for Orcale, so not sure if this code works.

WoMo Over a year ago

SQL*Plus is an Oracle command line client that will run the above script. I haven't used Toad in years but I suspect you may be able to execute this by running it as a script not a SQL statement (probably a different button or command). I'm sure Toad has documentation on running scripts and how to spool an output file.

RXC Over a year ago

I did find an Execute as Script button and it worked. The file was created in the path specified. Any idea how to fix the columns not displaying correctly?

Potti · Accepted Answer · 2013-10-24 18:52:42Z

0

I think you can use sqlplus command line tool todo this. See oracle manual for formatting hints.

answered Oct 24, 2013 at 18:52

Potti

1

1 Comment

New Alexandria Over a year ago

When providing an answer, show an actual example, even if a generic example is all that you can do to start

Collectives™ on Stack Overflow

Create text file in oracle using sql

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related