About DB Views:
Views are customized presentations of data in one or more tables or other views. You can think of them as stored queries. Views do not actually contain data, but instead derive their data from the tables upon which they are based. These tables are referred to as the base tables of the view.
Similar to tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view. Views can provide an additional level of security by restricting access to a predetermined set of rows and columns of a table. They can also hide data complexity and store complex queries.
Create Views:
Open SQL Developer and connect the connection (DEV, UAT, Prod), Write the desired query. Now expand the db and find the Views and right click and select the "New View"
A new window will appear.
Paste the query your have prepared before and provide the appropriate new and click on OK button. Your view will be created.
Now you need to provide the privileges to your view (SSE_ROLE/ Select). Find your newly created view by expanding the Views. Right click and high light the "Privileges" and new child will appear, select "Grant"
New window will open, find SSE_ROLE (or the role your are using while creating the users) and select. Option will available, move "SELECT" to right box by using right single arrow and click on "Apply" button. All done.
You can use this view in your queries or create external table in Siebel on this view. For creating external table you need to export the "DDL" file and import in Siebel Tools. Right click on the view and high light the "Quick DDL" child will appear from that click on "Save to file". A new window will appear asking for the file name and the location where you want the save ".sql" file.
Display views:
In the Connections navigator in SQL Developer, navigate to the Views node for the schema that includes the view you want to display.
If the view is in your own schema, navigate to the Views node in your schema.
If the view you want to display is in another user's schema, navigate to the Other Users node, expand it, find the name of the schema the view is in, and navigate to the Views node. Examples of schema names include SYS and HR.
Open the Views node. The list of views in the schema appears.
Click the name of the view that you want to display. A tab with the view name appears in the object pane, with the Columns subtab displayed. You can view the view definition on this tab.
See Also:
How to create SQL DB Views in MS SQL Server
How to create External Table in Siebel Tools on SQL View
Source: Oracle Docs
Comments
Post a Comment