CODEWITHSUNDEEP
sqldatabaseoracle-databasetext-files
RXC
RXC

Reputation: 1233

Create text file in oracle using sql

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

Upvotes: 1

Views: 4465

Answers (2)

Potti
Potti

Reputation: 1

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

Upvotes: 0

WoMo
WoMo

Reputation: 7246

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

Upvotes: 2

Related Questions