Home » SQL & PL/SQL » SQL & PL/SQL » Need help on view..
Need help on view.. [message #233360] Wed, 25 April 2007 16:51 Go to next message
RocaWear2007
Messages: 11
Registered: April 2007
Junior Member
..................

[Updated on: Thu, 26 April 2007 05:31]

Report message to a moderator

Re: Need help on view.. [message #233372 is a reply to message #233360] Wed, 25 April 2007 21:13 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


What are the primary key ( or unique key ) definitions for the tables in the view ?

Srini
Re: Need help on view.. [message #233399 is a reply to message #233360] Thu, 26 April 2007 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't have primary/unique key on your tables you have to create an instead trigger to update the view.

Regards
Michel
Re: Need help on view.. [message #233436 is a reply to message #233360] Thu, 26 April 2007 02:42 Go to previous messageGo to next message
RocaWear2007
Messages: 11
Registered: April 2007
Junior Member
..............

[Updated on: Thu, 26 April 2007 05:32]

Report message to a moderator

Re: Need help on view.. [message #233443 is a reply to message #233436] Thu, 26 April 2007 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use user/all/dba_UPDATABLE_COLUMNS views to see what columns are updatable.

Oracle allows you can update in a view only if there are keys that ensures that the update is deterministic that is only 1 row is updatable per distinct conditions.

Regards
Michel
Re: Need help on view.. [message #233450 is a reply to message #233360] Thu, 26 April 2007 03:11 Go to previous messageGo to next message
RocaWear2007
Messages: 11
Registered: April 2007
Junior Member
.........sorry wrong post

was suppose to add a new post but edited this one for some reason

[Updated on: Thu, 26 April 2007 03:24]

Report message to a moderator

Re: Need help on view.. [message #233452 is a reply to message #233450] Thu, 26 April 2007 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Those are views.
Just use them in SQL queries.

Regards
Michel
Re: Need help on view.. [message #233454 is a reply to message #233360] Thu, 26 April 2007 03:25 Go to previous messageGo to next message
RocaWear2007
Messages: 11
Registered: April 2007
Junior Member
Yeh, I tried that but it says

SQL> USER_UPDATABLE_COLUMNS
SP2-0734: unknown command beginning "USER_UPDAT..." - rest of line ignored.

Could it be the version of oracle im using? Will I have to go elsewhere and use another version? As im using it at home, and its a different version at college.
Re: Need help on view.. [message #233459 is a reply to message #233454] Thu, 26 April 2007 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not a SQL statement.
How do you query YOUR view.
It is the same for this one.

Regards
Michel
Re: Need help on view.. [message #233461 is a reply to message #233454] Thu, 26 April 2007 03:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's a view. You SELECT from it.
SELECT *
FROM   user_updatable_columns
WHERE  table_name = '<name of view>';
Re: Need help on view.. [message #233466 is a reply to message #233461] Thu, 26 April 2007 04:17 Go to previous messageGo to next message
RocaWear2007
Messages: 11
Registered: April 2007
Junior Member
............

[Updated on: Thu, 26 April 2007 05:32]

Report message to a moderator

Re: Need help on view.. [message #233470 is a reply to message #233466] Thu, 26 April 2007 04:27 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

Quote:
SELECT * FROM USER_UPDATABLE_COLUMNS
2 WHERE CARJOURNEY = 'CARRENTALS'


this is wrong you look at the description of USER_UPDATABLE_COLUMNS

SQL> desc USER_UPDATABLE_COLUMNS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 UPDATABLE                                          VARCHAR2(3)
 INSERTABLE                                         VARCHAR2(3)
 DELETABLE                                          VARCHAR2(3)

JRowbottom wrote on
SELECT *
FROM   user_updatable_columns
WHERE  table_name = '<name of view>';


here table_name is a column name of USER_UPDATABLE_COLUMNS
you have to give the 'column_name=<condition>'
so it should be 'table_name=<name_of_your_view>'
so you change the <'name_of_ your_view'> alone to your view name

regards
shanth

[Updated on: Thu, 26 April 2007 04:31]

Report message to a moderator

Re: Need help on view.. [message #233474 is a reply to message #233360] Thu, 26 April 2007 04:44 Go to previous messageGo to next message
RocaWear2007
Messages: 11
Registered: April 2007
Junior Member
Thanks alot buddy, you and the rest have been great help.

When I run that it said that INS,DEL,UP are all 'No' on every column, which pretty much sums up why I cannot insert, update and delete. I have just given up.

Thanks again everyone for all your help
Re: Need help on view.. [message #233493 is a reply to message #233474] Thu, 26 April 2007 07:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why have you just deleted your posts?
That prevents anyone else from learning anything from this thread, and it's also stopped me from seeing the data structures you're using and seeing if I can create you an updatable view.

Hey ho - your loss.
Re: Need help on view.. [message #233494 is a reply to message #233474] Thu, 26 April 2007 07:01 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please don't edit your posts to remove things you might think of as 'stupid'.
There is no stupidity in trying. You trying to get things done yourself gets you a LOT more respect from everyone than the lazy questions some others ask.
Removing the contents of previous posts makes the thread rather hard to follow.
Previous Topic: how to select rows using datatypes
Next Topic: command to number
Goto Forum:
  


Current Time: Mon Dec 05 21:05:09 CST 2016

Total time taken to generate the page: 0.07749 seconds