Home » SQL & PL/SQL » SQL & PL/SQL » Viewing sql for a VIEW
Viewing sql for a VIEW [message #198144] Sun, 15 October 2006 18:40 Go to next message
trgpham
Messages: 3
Registered: October 2006
Junior Member
hi all,

I'd like to view the sql statement for a VIEW in our database. How can I do that?

Thanks a lot.

regards,
John.
Re: Viewing sql for a VIEW [message #198151 is a reply to message #198144] Sun, 15 October 2006 19:20 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SET LONG 65536

SELECT text
FROM user_views
WHERE view_name = 'MY_VIEW_NAME'


Ross Leishman
Re: Viewing sql for a VIEW [message #198153 is a reply to message #198151] Sun, 15 October 2006 20:33 Go to previous messageGo to next message
trgpham
Messages: 3
Registered: October 2006
Junior Member
Thanks Ross,

I've tried that out but it keep coming back with 'no rows selected' message. I've tried to do a "Select *" statement from that table and it does show up the records, which to prove that the table name entered is correct.

Is there anything you suggest I should do.

Thanks.

regards,
John.
Re: Viewing sql for a VIEW [message #198159 is a reply to message #198153] Sun, 15 October 2006 21:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you don't own the view, try looking in ALL_VIEWS or DBA_VIEWS.

Ross Leishman
Re: Viewing sql for a VIEW [message #198162 is a reply to message #198159] Sun, 15 October 2006 22:34 Go to previous messageGo to next message
trgpham
Messages: 3
Registered: October 2006
Junior Member
All_Views worked for me. Thanks heaps.

John.
Re: Viewing sql for a VIEW [message #198167 is a reply to message #198144] Sun, 15 October 2006 23:34 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi

you can view all the data by the following syntax

set long 500
set pagesize 500

select text
from user_views
where view_name=UPPER('View Name');

i hope this will solve your problem

regards

Muhammad Shahid Mughal
Re: Viewing sql for a VIEW [message #198190 is a reply to message #198167] Mon, 16 October 2006 02:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hey, cool Muhammad. I bet that really helped.
Re: Viewing sql for a VIEW [message #198192 is a reply to message #198190] Mon, 16 October 2006 02:33 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi
Mr. Ross Leishman

i only read my emails and reply them therefore i couldn't see if the message have been answered already.

sorry if you felt inconvenience


regards

Muhammad Shahid Mughal
Re: Viewing sql for a VIEW [message #198198 is a reply to message #198192] Mon, 16 October 2006 02:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Might be best then to start replying via the web page. Could save yourself some effort.
Re: Viewing sql for a VIEW [message #198210 is a reply to message #198198] Mon, 16 October 2006 03:22 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
True, Ross, these were my thoughts exactly, but you've put them on a paper and I haven't http://www.orafaq.com/forum/fa/1606/0/

This is what was on my mind: there IS a difference between two solutions - Muhammad's contains the UPPER function which could help a newbie to avoid another "but it doesn't work" post (although you wrote a view name using capital letters which implies the same, but UPPER is perhaps more obvious).

Also, I'm a little bit confused (perhaps I should check options this Forum provides) - how did you, Muhammad, get a mail to answer the question? Are you subscribed to ALL messages posted on the OraFAQ Forum? If so, isn't your mailbox flooded with hundreds of e-mails? I'm not sure I'd want to have such a mess in my mailbox.
Re: Viewing sql for a VIEW [message #198220 is a reply to message #198210] Mon, 16 October 2006 03:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I dare to disagree Littlefoot. (although I don't have a matching smiley).

I think it is better to NOT provide the UPPER, to let newbies get used to the fact that object names are in uppercase. If they run into the 'I still cannot find it' once, chances are that it will stick.

(just my not so humble opinion)
Re: Viewing sql for a VIEW [message #198243 is a reply to message #198220] Mon, 16 October 2006 04:50 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem, Frank. I agree with your disagreement http://www.orafaq.com/forum/fa/1587/0/. Both UPPER and uppercase will, eventually, lead to the same solution. There's good and bad in everything, so I guess we could discuss what is better and which way offers more to learn, but ... but http://www.orafaq.com/forum/fa/1598/0/
Re: Viewing sql for a VIEW [message #198375 is a reply to message #198243] Mon, 16 October 2006 21:26 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I wonder if there's any important posts going unanswered at the moment...
Previous Topic: 21/85 PL/SQL: ORA-00933: (merged 2 cross-posts)
Next Topic: Top values in same line
Goto Forum:
  


Current Time: Fri Dec 02 22:55:03 CST 2016

Total time taken to generate the page: 0.19994 seconds