Oracle Views 1

[Kalisto]

Now first off, Im not an oracle developer, Im a .Net developer used to dealing with MS SQL, so please bear with me if I dont use the correct terms, or ask a stupid question.

Ive been given a .net / oracle project to do.
The customer will make the data available in a series of Views (Im used to working with tables).

We are to insert / Update the data using these views.

Taking the .net out of it, in SQL Plus I ran a basic SQL insert statement, and it came back as 1 row inserted.

If I then do a select * from the view, I see nothing.
Where has my data gone, and how can I see it (More from a confidence point of view than a distrust that it hasnt gone anywhere!)

 

[lewisp]

You could check in ALL_VIEWS for the view definition, or ask your system admin to check it for you.

In SQL*Plus

SET LONG 4000
SET PAGES 0

SELECT text
FROM   all_views
WHERE  view_name = '<VIEW NAME>';

From that you can determine which table(s) is/are affected and the WHERE clause that would prevent the data from being displayed for you.

 

[Kalisto]

Thanks, I believe it is slightly more fundamental than that though.

Ive been poking about with TOAD and if I run the SQL there I get an error saying I dont have rights to insert to the underlying table.

Supplemental question. These views are going to be configured somehow to accomodate different products. Is it the usual way that each product would have a view, and I need to maintain a list of views / Products so I know which to insert to, or would I somehow ask the OracleDb to return me a view for product X and just use my code to work on a single view instance, and let teh DB handle the rest?

Thanks
K

 

[Andrzejek]

I may be stating the obvious, but you do realize that views are just that - views, and you can look (at the data) but you can not touch (update, insert, delete) data from the views.

You have to have access to the tables the views are create from to insert, update or delete data in order to change data which you have in views.

Have fun.

---- Andy

 

[jrcol12357]

To pull in your view select Database/ Set Database Location/ Create New Connection Make your connection (server, user, password) choose View Folder select the view required. ex VW_RPT_TestData.

Your report will now have the data from the view. You can still do all the other requirements you may need for the report within CR. Grouping, Formulas, Parameters.

We use views and stored function where I work and associate each view to the report by the same name ex. View VW_RPT_12345 is used with Crystal Report 12345.