Home » SQL & PL/SQL » SQL & PL/SQL » WITH clause | How to use it? (Oracle 9i)
WITH clause | How to use it? [message #377569] Tue, 23 December 2008 09:38 Go to next message
Messages: 8
Registered: December 2008
Junior Member

I have performance issue with one of the VIEW so
I am trying to use Oracle WITH clause to call the view once and use the view for the whole session of the main SQL.

I have my main SQL in a function. Is it possible for me to create a procedure and populate the WITH clause? Later this WITH clause will be used in another function while computing values.

something like this

--main function
function x
--call procedure to populate the WITH clause

select my_another_func, x, y from z

procedure populate_with_clause
with abc as (
select x, y from vw_myview

function my_another_func(p_a number) is
select x from abc where abc.x = p_a;

Right now I cannot use the WITH clause in a procedure. Its expecting me to have a SELECT clause on the WITH statement.

Any ideas?
Re: WITH clause | How to use it? [message #377571 is a reply to message #377569] Tue, 23 December 2008 09:58 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
the WITH clause is part of the SELECT syntax.

It doesn't exist outside of a SELECT any more than the FROM clause does.
Re: WITH clause | How to use it? [message #377573 is a reply to message #377569] Tue, 23 December 2008 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64944
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before your next post, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Re: WITH clause | How to use it? [message #377605 is a reply to message #377569] Tue, 23 December 2008 17:02 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
check out this link

The SQL WITH Clause, (Order Your Thoughts, Reuse Your Code)

you might find some of these interesting as well

Kevin Meade's blog

Good luck, Kevin
Previous Topic: ORA-00933 error message
Next Topic: Repeated static SQL sections in PL/SQL packages
Goto Forum:

Current Time: Thu Jun 22 15:47:18 CDT 2017

Total time taken to generate the page: 0.10869 seconds