| Strange problem :Synonyms for Views [message #78947] |
Fri, 05 April 2002 06:35  |
Smitha
Messages: 17
Registered: June 2001
|
Junior Member |
|
|
Hi,
We are facing a strange issue with Synonyms for (non updateable) VIEWs.
My problem is, we have a set of forms that are view only(history forms), that can take you to another set of forms(add forms) that provide functionality for adding,editing etc...
So we created views for all the tables to be used in history forms, so that it prevents user from editing records(from typing in). We did not disable anything at the form level. It worked fine for the owner of the schema. Owner could not type in ...which was perfect.
Then we created synonyms for all the views, to be used in other schemas....
Problem: Non owner logins are able to type in to the fields!!!!....(but they can not save it)...
We tried inserting and updating through SQL prompt. It worked perfect(ie did not allow to edit the record since the synonym was based on a VIEW).
One work around is handling UPDATE_ALLOWED and INSERT_ALLOWED at form level. It works, but we want to keep it as the last choice.
One more point:
We created synonyms for the views in the owner schema itself, to see whether its a privilege issue. That also allowed editing(but no saving) thru forms. So I beleive its a synonym issue.
Is there any new patch that solves this problem.
Let me know your thoughts abt this.
Thanks,
Smitha.
|
|
|
|
| Re: Strange problem :Synonyms for Views [message #78949 is a reply to message #78947] |
Fri, 05 April 2002 19:00  |
rama krishna
Messages: 97
Registered: December 2001
|
Member |
|
|
insert_allowed and update_allowed properties are not round about work. its basic use is not to allow user to update or insert rows into the table.
now what u r doing is a round about thing. instead of creating views and creating blocks on those views better create blocks on the base table and set insert_allowed and update_allowed property to no. this i think will directly solve ur problem(if i'm not mistaken...).
no form will know on which object u've created the block (ie table or synonym) it will allow u to do modifications in the text items untill u r commiting them. at the time of commit it will check whether it can update the data. then it'll return u the error.
so if u don't want the user to do any manipulations on the text items of the form better go_ahead with insert_allowed and update_allowed properties
cheers
ram
|
|
|
|
| Re: Strange problem :Synonyms for Views [message #78962 is a reply to message #78947] |
Mon, 08 April 2002 05:09 |
Smitha
Messages: 17
Registered: June 2001
|
Junior Member |
|
|
Thanks for your reply, Ram. But there are few things I missed out in the prev posting. First of all, views are very important to our system, since it has many more purposes than being used in forms. We have tranformed a lot of joins into views, so that our front end runs faster. We can not think of using base tables in place of views. That might require lottsa work.
Secondly, forms do identify the object the block is based on. If you have created a block based on a VIEW, form knows its a VIEW and if you try to type in anything(only in execute query mode), it raises the error "FRM-40602-Cannot insert into or update data in a VIEW". This obviously means that FORM knows that it is based on a VIEW. Our forms open in EXECUTE QUERY mode. So we expect it to raise FRM-40602(because they are based on views). All our forms are based on this fact(assumption??).This was not happening when we use synonyms for the respective views,which is strange b'cos, we expect forms to distinguish between tables,views,synonyms and SPs. And it does for tables and views, but not for synonyms. Forms had this problem of Synonyms before. In an earlier patch of Forms 6, form was not able to identify a synonym. It gave an error "Object not identified". It was a much more critical problem than this, and there was no solution to that. But later oracle released a patch that solved this bug.
SO we feel that the problem we are facing right now, is also some kind of inconsistency or bug in the Forms Builder Software. I am sure somebody might have faced the same problem. I wanted to know if Oracle has released any new patch solving this issue.
Disabling INSERT_ALLOWED and UPDATE_ALLOWED is the best solution with no loop holes. But that requires lottsa changes in our forms, which might affect some other functionalities. So it is a work around for us.
|
|
|
|
| Re: Strange problem :Synonyms for Views [message #79023 is a reply to message #78947] |
Sun, 14 April 2002 22:55 |
rama krishna
Messages: 97
Registered: December 2001
|
Member |
|
|
hi smitha...
try this and tell me if i've understood ur problem.
user_views is a public synonym created in system. so u can use user_views in ur user also. now try creating a base table block on user_views(type user_views and say refresh) ok u can create a block on it. now this sysnonym is created on a view (user_views in sys user)...... so this says u can create a block on synonyms. ok now change the blocks property insert_allowed and update_allowed property to no...
so i didn't find any problem in creating a block on a synonym. i normally use this method to see the view's select statement...
any problem u can write to me..
if i still didn't understood ur problem i'm sorry...
cheers
ram
|
|
|
|
| Re: Strange problem :Synonyms for Views [message #79026 is a reply to message #79023] |
Mon, 15 April 2002 05:16 |
Smitha
Messages: 17
Registered: June 2001
|
Junior Member |
|
|
Hi Ram,
You are right. There's no problem in setting the insert_allowed and update_allowed to NO(at block level) if you don't want the user to type in. But we have come controls(like push button), in the base table block. So if we disable the above mentioned properties it gives error messages.
The perfect solution is setting these properties at item level, so that we can omit those items that should be clickable and double clickable.
This solves our problem and works perfectly fine...
We have around 100 forms. At this time we can not go to each item in each block in each form and set the properties.
We feel that there could be a better one time fix at database level.
I'll state our problem again.
The basic problem is not setting block level properties.
The problem is :when a block is based on a VIEW based SYNONYM we expect it to raise FRM-40602. This is what forms help says abt 40602.........
/************************************
FRM-40602: Cannot insert into or update data in a view.
Cause: You tried to modify the contents of a view in a manner that is not permitted.
Action: No action is necessary; you cannot perform the operation you have attempted.
Level: 20
Type: Error
****************************************************/
But it does not raise this error...
Eg. Create a form in your user based on user_views...run it ....try to type in...it allows you to...(BUt it does not allow to save)
Run the same form in SYS user... Try to type in...it raises the FRM-40602 error....
This obviously means that FORMS does not understand between table based synonyms and view based synonyms. If there's a global fix for this like some environment setting...or db setting ,we would prefer that.
Thanks for your investigations and reply Ram.
|
|
|
|
| Re: Strange problem :Synonyms for Views [message #79851 is a reply to message #79023] |
Thu, 18 July 2002 06:32 |
luchito
Messages: 1
Registered: July 2002
|
Junior Member |
|
|
Hi,
My suggestion is that you put the prefix of the schema before the name of the view in the block's propertys: Data Base objet source and Advanced Data Base Destiny of data DML.
Ex. block's property
Data Base
Name of source Schema.View
Advanced Data Base
Name Destiny of data DML Schema.View
Good luck
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
