Solved

How to extract data from Oracle 10g database to excel

Posted on 2009-04-06
11
295 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
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Note: You must have administrative privileges in order to create/edit Roles. 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 and marke…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

729 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