Solved

How to extract data from Oracle 10g database to excel

Posted on 2009-04-06
11
290 Views
Last Modified: 2014-01-15
Hi all,
I have a deployed application which was developed externally. This application does some master data management & stores data in Oracle 10g database. So now we need to extract the data from Oracle 10g & store it excel or any other format. This extracted data need to be uploaded to SAP after validation.
Regards,
Dipti
0
Comment
Question by:Dipti01
11 Comments
 
LVL 9

Expert Comment

by:MarkusId
ID: 24084597
Hi,

You must have access with sql to the database. Then you
can spool the data delimited by semicolons to build a csv-file
which can easily be accessed by excel.


set colsep ';' -- you can set this to other delimiters as well

set lines 700 -- set this to your maimum linesize

set pages 50000 -- get a header only at the beginning

set trimspool on -- don't pad all lines to 700 characters

set feed off 

set echo off

set verify off
 

spool filename.csv

SELECT...

spool off

Open in new window

0
 

Author Comment

by:Dipti01
ID: 24084803
I don't know anything about Oracle. In the start menu->Programs-> Oracle database 10g express edition ->Run SQL Command line. When i click here. SQL> prompt comes. I hope i have to use this to execute above mentioned commands.
I junst have .db extension file for Oracle database. How would i select the database i need to extract in the SQL commnand prompt. I don't know the table name also.  

Could you please tell in detail on how to extract data from Oracle database.

Thanks
0
 
LVL 9

Accepted Solution

by:
MarkusId earned 250 total points
ID: 24084929
On the first thing, you are right - you need this SQL Command line.

However, it's hardly possible to extract data from an Oracle database if you don't know anything about the data.

1) You need access to the database. Just because you get the SQL command line prompt does not mean that you are connected to a database (Try: "SELECT * FROM DUAL" within it).

2) If you have the connect info - connect to the database
 ("connect username/password@databasename")

3) If you are connected to the database, get a list of the available tables ("SELECT owner, table_name from all_tables"). Forget the tables where the owner is 'SYS' or 'SYSTEM', just look at all the other tables. You may be lucky and the tables have names that tell you what's in them.
If you don't have an idea what I'm talking about that far, I'm afraid you have to seek professional help for this. At least you might get some description of the tables from the company that developed the application.

If you have all these information you might get back, otherwise I'm afraid we can't help you (would need some crystal ball then).
0
 

Author Comment

by:Dipti01
ID: 24085012
To extract data from Oracle database (.db file), i must know the database name, its table to extract the data. Is this understanding correct?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 9

Expert Comment

by:MarkusId
ID: 24085064
Yes, that's right. You must know a username & password, the
connection information (if it's not already in the tnsnames.ora in your oracle/network/amin-directory) and the table(s) where the data is stored that you want to extract.
0
 
LVL 20

Assisted Solution

by:gatorvip
gatorvip earned 250 total points
ID: 24088333
Another alternative would be to run a data query in Excel, against the database. Open Excel, then go to Data / Import External Data / New Database Query.

However you still need some login info. If you have already set up an ODBC connection to the Oracle database, it will appear in the Choose Data Source window.
0
 

Author Comment

by:Dipti01
ID: 24094050
Thanks Markus & gatorvip for your inputs, I could retrieve the data from Oracle database.  
Regards,
Dipti
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 32354825
Once you get the Oracle connection and tables you want thing figured out.

If SAP can handle XML, I would look at going down the XML path over Excel.  Generating XML from oracle is becoming easier with every new release.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39781822
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now