469,323 Members | 1,553 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

exporting data from oracle to excel

NawazAhmed
Hi,
I saw the same titled post but the answer given there is abt how to import data from an input file.
I know this can be done once u can get data from database in ur application using java,.net or any other language but I want to accomplish this inside the query itself.

I've found the code for the same but this query is written for mysql, I am looking for somthing on the same line in oracle.
This is the link for solution to my question, in mysql query using java language.
http://www.java-tips.org/other-api-t...-csv-file.html

the query used here in mysql is:
Expand|Select|Wrap|Line Numbers
  1. "SELECT id,text,price into OUTFILE  '"+filename+ "' FIELDS TERMINATED BY ',' FROM testtable t"
  2.  
  3.  
Thanks,
Nick.
Oct 30 '08 #1
9 20380
Pilgrim333
127 100+
I don't get what you mean.

Do you want to import data into Oracle from Excel, or do you want to export data from Oracle to Excel?

Do you want to do it just by using SQL or are you using PL/SQL as well?
Oct 30 '08 #2
As I mentioned I want to do this with oracle (PL/SQL).
I need the same kind of query in pl/sql which I have written in mysql.
Oct 30 '08 #3
Pilgrim333
127 100+
I really don't read mySQL.

There is a difference between SQL and PL/SQL in Oracle. SQL is the query language and PL/SQL is the programming language.

If you want to get it in a query and the output is csv then the SQL query could be something like:

SELECT column_name1 || ';' || column_name2 || ';' || column_name3 etc.
FROM table.

You can replace the semicolon by any seperator you want, just make sure it is between single quotes.

Make sure the output is send to a file, and you are done.

Pilgrim.
Oct 30 '08 #4
debasisdas
8,127 Expert 4TB
That can be easily done by using some tools like toad,pl/sql developr or query reporter.
Oct 31 '08 #5
I really don't read mySQL.

There is a difference between SQL and PL/SQL in Oracle. SQL is the query language and PL/SQL is the programming language.

If you want to get it in a query and the output is csv then the SQL query could be something like:

SELECT column_name1 || ';' || column_name2 || ';' || column_name3 etc.
FROM table.

Make sure the output is send to a file, and you are done.

Pilgrim.
Thanks for the reply guyz.
Pilgrim333 I was really looking for something like that but can I transfer that result to a file directly from the query something like
SELECT column_name1 || ';' || column_name2 || ';' || column_name3 etc.
FROM table into outputfile "filelocation"
Oct 31 '08 #6
Pilgrim333
127 100+
Hi,

You can spool the output of the query to a file. In your sqlplus window just give the following commands:

Expand|Select|Wrap|Line Numbers
  1. SQL> Spool on
  2. SQL> Spool c:\output.csv --file you want the output generated to
  3. SQL> <query>
  4. SQL> Spool off
  5.  
Pilgrim.
Oct 31 '08 #7
Hi,

You can spool the output of the query to a file. In your sqlplus window just give the following commands:

Expand|Select|Wrap|Line Numbers
  1. SQL> Spool on
  2. SQL> Spool c:\output.csv --file you want the output generated to
  3. SQL> <query>
  4. SQL> Spool off
  5.  
If I am passing the query from the java program to the database, then how that would b using spool.
Will that be something like this??
str = "Spool on
Spool c:\output.csv
SELECT column_name1 || ';' || column_name2 || ';' || column_name3 etc.
FROM table
Spool off"
Nov 3 '08 #8
Pilgrim333
127 100+
Hi,

I read over the part of Java. Spool is an SQLPlus command, so i don't think you can submit it using Java.
But if you are using Java, wouldn't it be a lot easier getting the result back in Java and create your Excel file from Java?

Pilgrim.
Nov 3 '08 #9
Hi,

I read over the part of Java. Spool is an SQLPlus command, so i don't think you can submit it using Java.
But if you are using Java, wouldn't it be a lot easier getting the result back in Java and create your Excel file from Java?
Yes I already implemented that......was just curious to know if it can work this way..............But thanks for all ur help.
Nov 3 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Chris | last post: by
2 posts views Thread by Paige | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.