Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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

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
junaid_baig1
Asked:
junaid_baig1
  • 5
  • 4
  • 2
  • +1
3 Solutions
 
fluglashCommented:
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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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
 
FayyazCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
junaid_baig1Author Commented:

 @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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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
 
junaid_baig1Author Commented:

 I 'm working on Windows Vista & checked the permission on C: drive! I can create a new folder/file in C: drive!
0
 
junaid_baig1Author Commented:
@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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
please remove ";" and provide absolute path for spool file.
0
 
junaid_baig1Author Commented:
@virdi_ds; I did that also but error persist!
0
 
fluglashCommented:
you will find info about creating ODBC datasource on Vista:

http://windows.microsoft.com/en-us/windows-vista/ODBC-frequently-asked-questions
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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
 
FayyazCommented:
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
 
junaid_baig1Author Commented:
Thanks a ton!!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now