Create text file in Oracle statement

I want to create a table in PL/SQL, lets call it Test, and then create a text file from that table.
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
slightwv (䄆 Netminder) Commented:
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.
0
 
jknj72Author Commented:
Im using SQL Developer
0
 
slightwv (䄆 Netminder) Commented:
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

Open in new window





Then execute that as a script (I think it is F5).  That should create the file.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ritesh_GargCommented:
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.
0
 
jknj72Author Commented:
I will try in the morning Slight. Ritesh I need to do it programmatically
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
jknj72Author Commented:
I couldn't do it from a package script using the Util_File in SQL  Developer?
0
 
jknj72Author Commented:
Im actually gonna have a table and I need to get that to a text file hopefully from code
0
 
Ritesh_GargCommented:
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;
0
 
jknj72Author Commented:
ok I will try in the morning Ritesh
0
 
slightwv (䄆 Netminder) Commented:
>>I couldn't do it from a package script using the Util_File in SQL  Developer?

You can create a stored procedure that uses UTL_FILE and call it from wherever.

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

You need to post your specific requirements.  "from code" isn't a requirement.

Does the file need to be created on the database server or a remote client machine?
Does it need to be created from a stored procedure, anonymous PL/SQL block or can it be created from sqlplus?

>>  UTL_FILE.GET_LINE(F1,'This is my output going to text file');

I believe you mean UTL_FILE.PUT_LINE.

Since you opened the file as WRITE, you need to grant more than READ on the directory.

That is an anonymous pl/sql block.  If that is allowed, the sqlplus should be allowed as well and would be easier.

jknj72,
If you want to use UTL_FILE, again, there are MANY examples on the web.

Please be more specific about your exact requirements.
0
 
Ritesh_GargCommented:
Thanks slightwv.
The corrected steps are:

Step 1:
SQL> CREATE DIRECTORY out_dir AS '/appl/gl/user'';
SQL> GRANT WRITE 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.PUT_LINE(F1,'This is my output going to text file');
  UTL_FILE.FCLOSE(F1);
END;
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
jknj72Author Commented:
Thanks guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.