Solved

How to extract data from Oracle 10g database to excel

Posted on 2009-04-06
11
289 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 9

Expert Comment

by:MarkusId
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Markus & gatorvip for your inputs, I could retrieve the data from Oracle database.  
Regards,
Dipti
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: You must have administrative privileges in order to configure lead or case queues. Salesforce.com (http://www.Salesforce.com) is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
Note: You must have administrative privileges in order to create/edit Sharing Rules. Salesforce.com (http://www.salesforce.com) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales an…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

771 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

9 Experts available now in Live!

Get 1:1 Help Now