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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.