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-tips/jdbc/how-to-export-data-from-database-to-csv-file.html

the query used here in mysql is:

"SELECT id,text,price into OUTFILE  '"+filename+ "' FIELDS TERMINATED BY ',' FROM testtable t"

Thanks,
Nick.

[Pilgrim333]

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?

[NawazAhmed]

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.

[Pilgrim333]

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.

[debasisdas]

That can be easily done by using some tools like toad,pl/sql developr or query reporter.

[NawazAhmed]

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 "filelocati on"

[Pilgrim333]

Hi,

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

SQL> Spool on
SQL> Spool c:\output.csv --file you want the output generated to
SQL> <query>
SQL> Spool off

Pilgrim.

[NawazAhmed]

Hi,

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

SQL> Spool on
SQL> Spool c:\output.csv --file you want the output generated to
SQL> <query>
SQL> Spool off

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"

[Pilgrim333]

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.

[NawazAhmed]

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.