Create text file in Oracle statement

From inside pl/sql I would use UTL_FILE.  There are a TON of examples on the Internet and in the online docs.

The drawback is the file will be created on the database server.

Do you have to use pl/sql or could you use sqlplus and the spool command?  This is the simplest way to extract data and create a flat file.

Im using SQL Developer

PL/SQL is Oracle's Procedural programming language.

Go to SQL Developers worksheet and enter:

set pages 0
set feedback off
set timing off
set lines 1000
set trimspool on
spool c:\myfile.txt

select ... -- the rest of your select statement
/

spool off

Select allOpen in new window

Then execute that as a script (I think it is F5).  That should create the file.

From SQL Developer right click onto the query result and select "Export Data".  Now you may save it to various file format including text, Excel etc.

I will try in the morning Slight. Ritesh I need to do it programmatically

>>I need to do it programmatically

Then you can't do it from SQL Developer.  I'm not aware of a command line interface to that.

You'll either need PL/SQL or a sqlplus script to do it programmatically and/or scripted.

I couldn't do it from a package script using the Util_File in SQL  Developer?

Im actually gonna have a table and I need to get that to a text file hopefully from code

Step 1:
SQL> CREATE DIRECTORY out_dir AS '/appl/gl/user'';
SQL> GRANT READ ON DIRECTORY user_dir TO PUBLIC;

Step 2:
DECLARE
  F1 UTL_FILE.FILE_TYPE;
BEGIN
  F1 := UTL_FILE.FOPEN('OUT_DIR','MYFILE.txt','W',32767);
  UTL_FILE.GET_LINE(F1,'This is my output going to text file');
  UTL_FILE.FCLOSE(F1);
END;

ok I will try in the morning Ritesh

Thanks guys