Solved

How to extract data from Oracle 10g database to excel

Posted on 2009-04-06
11
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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
 
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 77

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create Index on a Materialized View 5 47
pl/sql - query very slow 26 89
Email query results in HTML 6 38
Toad 12.10 Enterprise visual interface 4 33
Salesforce.com is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

726 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