Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Function used in view definition is called multiple times

814021Dec 15 2010 — edited Dec 15 2010
I am trying to write a view that boils down to the following statement:

SELECT val, val FROM (
SELECT SYS_GUID() val
FROM dual
)

Against my expectation, this calls SYS_GUID() twice, resulting in two different values for val.

I've written it here as inline view, but it must be a regular view in reality. Is there any way to construct this view so that SYS_GUID is called only once, even if the resulting value is then selected multiple times? Naively, I'd like to assign the result of SYS_GUID to a variable and then select the value of this variable. However, this does not seem possible in a view.

New to Oracle PL/SQL. This works as expected in SQL Server T-SQL.

Thanks for any help or insight you can provide.

-- Brian
This post has been answered by 635471 on Dec 15 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 12 2011
Added on Dec 15 2010
8 comments
227 views