?
Solved

How to open Oracle 10g DB tables in MS Excel & Access

Posted on 2010-01-12
13
Medium Priority
?
890 Views
Last Modified: 2012-05-08
Hello Experts,

 Could you please suggest that How we can Open or exports Oracle 10g DB tables & objects in/to MS Excel & Access?


Thanks & regards
  jdbaig
0
Comment
Question by:junaid_baig1
[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
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 7

Assisted Solution

by:fluglash
fluglash earned 400 total points
ID: 26292025
To export oracle table to MS Access you need to:

1. create odbc data source on your local machine for the Oracle database you need.

2. create MS Access DB, and import table.

3. right click on table creation tab and select Import in popup menu.

4. in the Import Dialog select Type ODBC Databases.

5. select datasource name you created in step 1.

6. enter user name and password.

7. select tables you want to import and click OK.



P.S. what are objects here?

"Could you please suggest that How we can Open or exports Oracle 10g DB tables & objects in/to MS Excel & Access?"
0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 800 total points
ID: 26294575
To export object to excel from sqlplus, do the following.
set linesize 1000 (or suitable)
set pagesize 0 ( or suitable)
set markup html on
spool file_to_open_in_excel.xls
select * from your table name
spool off
exit

Now open spool file in excel.
0
 
LVL 7

Expert Comment

by:Fayyaz
ID: 26295196
the method flugflash suggested is to import the data from oracle table to excel. If you just to see the data ther is an option of link table. Use the same method as mentioned above but select link table instead of import table.
This is very helpful when you want to see the oracle data in ms access. Whenever there is a change in data in oracle you can see the change in ms-access tables (whenever you re-open it )
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:junaid_baig1
ID: 26295391

 @fluglash :
                   I don't know how to create odbc data source ! Could you please elaborate a bit How to do it !
  Thanks!!


  @virdi_ds: I tried to follow the steps given by you But it gives this  error! Plz suggest how to sort it out!            
 Thanks.      
                SQL> spool file_to_open_in_excel.xls;
                SP2-0606: Cannot create SPOOL file "file_to_open_in_excel.xls"

 
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26296343
That is because you dont have write permission in your current directory( I think you are working on Unix). Go to your home directory and then retry. Just type cd
0
 

Author Comment

by:junaid_baig1
ID: 26296401

 I 'm working on Windows Vista & checked the permission on C: drive! I can create a new folder/file in C: drive!
0
 

Author Comment

by:junaid_baig1
ID: 26296462
@virdi_ds;   I 'm working on Windows Vista & checked the permission on C: drive! I can create a new folder/file in C: drive!

                       'm getting the following error when i tried to follow the steps given by you ! Kindly suggest what to do now?
 
                 SQL> spool file_to_open_in_excel.xls;
                SP2-0606: Cannot create SPOOL file "file_to_open_in_excel.xls"
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26297095
please remove ";" and provide absolute path for spool file.
0
 

Author Comment

by:junaid_baig1
ID: 26297119
@virdi_ds; I did that also but error persist!
0
 
LVL 7

Expert Comment

by:fluglash
ID: 26300909
you will find info about creating ODBC datasource on Vista:

http://windows.microsoft.com/en-us/windows-vista/ODBC-frequently-asked-questions
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 26304601
Are you able to create directory/file from command window using mkdir/edit/echocomand?
ie
cd c:\users\yourdirectory
mkdir temp
cd temp
echo > temp_file
sqlplus username/passwd@inst
spool temp_file.
0
 
LVL 7

Assisted Solution

by:Fayyaz
Fayyaz earned 800 total points
ID: 26305690
windows vista has a probelm to create file and directories from other users login and you have to enable permissions for that particular user, may be the oracle user.
Or you can try to give the path of spool file to the location where your trace files or alert logs are going and lets see
0
 

Author Closing Comment

by:junaid_baig1
ID: 31676015
Thanks a ton!!
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

801 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