An Oracle view is a virtual table that does not physically exist. It is stored in Oracle data dictionary and do not store any data. A view is created by a query joining one or more tables. A view is queried just like any other table and returns up-to-date data, never stale!
To create a view:
- Write and test your query in Query Builder - if the query does not work, nor will the view!
- Once you have a working query, wrap the select statement with the following to create the SQL syntax:
create or replace view VIEW_NAME as (select....);
a very simple example is:
create or replace view TEST_VW as (select eo_id from eo where eo_id<100); - Log into a third-party tool (such as SQL Plus, SQL Developer, Tora, Toad) as the biotics_dlink user.
- Run the SQL syntax to create the view. If successful, the output will indicate View created.
- The view is created under the biotics_user account, not the biotics_dlink account, even though you're logged in as biotics_dlink. In order to query the view or access it through the Biotics' Query Builder, run Grant/Refresh Database Privileges, in Biotics. Querying it prior to doing so will result in the following error:
ORA-01031: insufficient privileges
The view will not be visible in Query Builder until this is run.
- Test the view by querying it like you would query any other table/view, either in the third-party tool or Query Builder:
select * from test_vw