SQL*XL: Excel Oracle Query |
|
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
See also: