Quering Oracle databases from Excel is easy. In most cases you can simply type select * from mytable;.
If you suspect that the table may be large (larger than a few thousand records) you may wish to restrict the
query. You can use the following syntax to just retrieve 10 rows (an arbitrary 10):
select * from mytable where rownum <= 10;
Table Names and column names:
To find out which tables you can query please use the SQL
editor. A list of tables and views are presented in the database objects list.
Also use the Describe command to find out the column
names in a table. Executing the describe command will display the list of
columns of a table or view: desc mytable;
To quickly dump the contents of a table you can use the
Actions menu in the SQL editor. Select the table of interest and right click. The actions menu is displayed. You can choose to let SQL*XL generate the SQL
to dump the table into Excel or to sample some records only.
More database information:
If you want to explore the database on your own you may find the following views useful. Try a select * from
this_table where rownum <= 50 to find out the data it contains.
View
Description
SYS.CATALOG
This view contains a list of all tables/views in the database the current user has access to. It is an extensive
list of all the tables/views available. I have below selected a few.
USER_TABLES
List all tables owned by the current user. Use ALL_TABLES to see all tables in the database
ALL_TAB_COLUMNS
Shows all the column information for all tables
V$SESSION
Shows all the sessions in the system. A session is created for each user logging in.
To see how many rows a table or view contains use the syntax: select count(*) from mytable
Copyright (C) 1995-2007 Linker IT
Software BV. All Rights Reserved. Oracle is a registered trademark of
Oracle corporation. Excel and Office are registered trademarks of
Microsoft corporation. Other names appearing on the site may be trademarks
of their respective owners. Software,
files, documents, articles and other material are provided
"as is" and without warranties as to performance or mechantability or
any other warranties whether expressed or implied. No
warranty of fitness for a particular purpose is offered.
sitemap
