Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

How to extract data from Oracle 10g database to excel

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
Dipti01
Asked:
Dipti01
2 Solutions
 
MarkusIdCommented:
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
 
Dipti01Author Commented:
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
 
MarkusIdCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Dipti01Author Commented:
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
 
MarkusIdCommented:
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
 
gatorvipCommented:
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
 
Dipti01Author Commented:
Thanks Markus & gatorvip for your inputs, I could retrieve the data from Oracle database.  
Regards,
Dipti
0
 
slightwv (䄆 Netminder) Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now