jknj72
asked on
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.
ASKER
Im using SQL Developer
PL/SQL is Oracle's Procedural programming language.
Go to SQL Developers worksheet and enter:
Then execute that as a script (I think it is F5). That should create the file.
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
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.
ASKER
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.
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.
ASKER
I couldn't do it from a package script using the Util_File in SQL Developer?
ASKER
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',3276 7);
UTL_FILE.GET_LINE(F1,'This is my output going to text file');
UTL_FILE.FCLOSE(F1);
END;
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','
UTL_FILE.GET_LINE(F1,'This
UTL_FILE.FCLOSE(F1);
END;
ASKER
ok I will try in the morning Ritesh
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys
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.